mysql优化之全局查询日志

全局查询日志

1，配置启用全局查询日志

注意：测试环境检测sql操作情况，切忌在生产环境中开启此功能

1,命令设置：(当前session有效)
    //开启
    set global general_log = 1
    //定位table
    set global log_output = 'TABLE';

2,配置文件设置：(全局有效)
    mysql的配置文件my.cnf中
    #开启
    general_log = 1
    #记录日志文件路径
    general_log_file = /path/logfile
    #输出格式
    log_output = FILE

定位以及配置成功,当前session下操作的sql语句，将会记录在mysql库中general_log表，
可以用如下命令查看
select * from mysql.general_log;

mysql> select * from mysql.general_log;
+---------------------+---------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_time          | user_host                       | thread_id | server_id | command_type | argument                                                                                                                                                                                                                                                                                                                                                                                                  |
+---------------------+---------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2018-01-16 21:27:49 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | SELECT bloginfo_id,bloginfo_title,bloginfo_describe,bloginfo_img,from_unixtime(bloginfo_createtime,'%Y') AS year,from_unixtime(bloginfo_createtime,'%m-%d') AS date,from_unixtime(bloginfo_createtime,'%Y-%m-%d') AS bloginfo_createtime,bloginfo_like,bloginfo_hate,bloginfo_click FROM ns_bloginfo WHERE bloginfo_status = 1 AND class_id=10 ORDER BY bloginfo_oid,bloginfo_id DESC                     |
| 2018-01-16 21:27:55 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | SELECT bloginfo_id,bloginfo_title,bloginfo_describe,bloginfo_img,from_unixtime(bloginfo_createtime,'%Y') AS year,from_unixtime(bloginfo_createtime,'%m-%d') AS date,from_unixtime(bloginfo_createtime,'%Y-%m-%d') AS bloginfo_createtime,bloginfo_like,bloginfo_hate,bloginfo_click FROM ns_bloginfo WHERE bloginfo_status = 1 and class_id in (10,11,12,13,14,15) ORDER BY bloginfo_oid,bloginfo_id DESC |
| 2018-01-16 21:27:58 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | SHOW TABLES                                                                                                                                                                                                                                                                                                                                                                                               |
| 2018-01-16 21:28:03 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | select * from ns_link                                                                                                                                                                                                                                                                                                                                                                                     |
| 2018-01-16 21:28:05 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | SHOW TABLES                                                                                                                                                                                                                                                                                                                                                                                               |
| 2018-01-16 21:28:16 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | select * from ns_homepage                                                                                                                                                                                                                                                                                                                                                                                 |
| 2018-01-16 21:28:23 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | select * from ns_class                                                                                                                                                                                                                                                                                                                                                                                    |
| 2018-01-16 21:28:27 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | select * from ns_class                                                                                                                                                                                                                                                                                                                                                                                    |
| 2018-01-16 21:28:34 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | select * from ns_class                                                                                                                                                                                                                                                                                                                                                                                    |
| 2018-01-16 21:28:45 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | select * from mysql.general_log                                                                                                                                                                                                                                                                                                                                                                           |
| 2018-01-16 21:28:48 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | select * from mysql.general_log                                                                                                                                                                                                                                                                                                                                                                           |
| 2018-01-16 21:29:01 | niushao[niushao] @ localhost [] |       358 |         1 | Query        | select * from mysql.general_log                                                                                                                                                                                                                                                                                                                                                                           |
+---------------------+---------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)