Enable MySQL General log dynamically

This problem is often encountered: the database has a large access volume and needs to be optimized from the SQL aspect. Developers often ask: Can you see which SQL statements are executed frequently? Return: No. You can only view the currently running SQL statements and the SQL statements recorded in the slow log.

Generally, General log is not enabled for performance purposes. Slow log can locate SQL statements with performance problems, while general log records all SQL statements.

For MySQL, If You Want To enable slow log and general log, you need to restart. From MySQL 5.1.6, general query log and slow query log support writing to files or database tables, in addition, logs can be dynamically modified at the global level when they are enabled and output.

Root @ (none) 09:40:33> select version ();
+ ---- +
| Version () |
+ ---- +
| 5.1.37-log |
+ ---- +
1 row in SET (0.02 Sec)

Set the log output mode to a file (if log_output = table is set, the log results will be recorded in the table named gengera_log, and the default engine of this table is CSV ):

Root @ (none) 09:41:11> set global log_output = file;
Query OK, 0 rows affected (0.00 Sec)

Set the log file path for general log:

Root @ (none) 09:45:06> set global general_log_file = '/tmp/General. log ';
Query OK, 0 rows affected (0.00 Sec)

Enable General log:

Root @ (none) 09:45:22> set global general_log = on;
Query OK, 0 rows affected (0.02 Sec)

After a while, close the General log:

Root @ (none) 09:45:31> set global general_log = off;
Query OK, 0 rows affected (0.02 Sec)

Check the tmp/General. log information. You can see which SQL queries, updates, deletes, and inserts frequently. For example, if some tables do not change frequently and the query volume is large, the data can be fully cached. If tables with low requirements on Master/Slave latency, the data can be read to the slave database.