Mysql slow query log not working

Add this in my.cnf and then restart MySQL server:

general_log = 1
log=/var/log/mysql_slowquery.log

You can check whether the general_log is 'ON' or 'OFF' first using query:

mysql>show variables like '%log%';

You can enable it by below commoand without restart

SET GLOBAL slow_query_log = 'ON';

Mysql change table engine MyISAM to InnoDB

 The easiest way is

ALTER TABLE table_name ENGINE = InnoDB;

InnoDB uses row level locks

Command to optimize all MySQL tables one by one automatically

 use mysqlcheck with --optimize and --databases combination. mysqlcheck is a command-line interface for administrators


 mysqlcheck -uroot -p --optimize --databases myDatabase

Optimizing a Table

It's simple and straightforward to optimize a table in MySQL

Optimize table TABLE_NAME

Speedly MySQL InnoDB Shutdown

Depending on the size of the databases you have in mysql innodb the time it takes mysql to restart can be very slow.Some tricks that can speed this up


mysql> set global innodb_max_dirty_pages_pct = 0;

You can check the number of dirty pages with the command

mysqladmin ext -i10 | grep dirty

SELECT without Table

You can also issue SELECT without a table. For example, you can SELECT an expression or evaluate a built-in function.

mysql> select 1+1;

+-----+

| 1+1 |

+-----+

|   2 |

+-----+

1 row in set (0.00 sec)

mysql> select now();

+---------------------+

| now()               |

+---------------------+

| 2016-05-03 12:40:41 |

+---------------------+

1 row in set (0.00 sec)

mysql> select now(), 1+2;

+---------------------+-----+

| now()               | 1+2 |

+---------------------+-----+

| 2016-05-03 12:40:55 |   3 |

+---------------------+-----+

1 row in set (0.00 sec)

Recover a MySQL root password

Simple and smart reset mysql root password

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Save the Select Output to a File

Using SELECT INTO, we can save the output of a select command into a file.Instead of displaying the output on the screen

mysql> SELECT * INTO OUTFILE '/tmp/employee.txt' FROM employee;

We can also store the output into a comma delimited file by specifying the “FIELDS TERMINATED BY”

mysql> SELECT * INTO OUTFILE '/tmp/employee1.txt'   FIELDS TERMINATED BY ',' FROM employee;