mysql开启通用查询日志general log
mysql打开general log之后，所有的查询语句都可以在general log文件中以可读的方式得到，但是这样general log文件会非常大，所以默认都是关闭的。有的时候为了查错等原因，还是需要暂时打开general log的（本次测试只修改在内存中的参数值，不设置参数文件）。
general_log支持动态修改：
mysql> select version();
+-----------+ | version() | +-----------+ | 5.6.16 | +-----------+ 1 row in set (0.00 sec)
mysql> set global general_log=1;
query ok, 0 rows affected (0.03 sec)
general_log支持输出到table：
mysql> set global log_output='table';
query ok, 0 rows affected (0.00 sec)
mysql> select * from mysql.general_log\g;
*************************** 1. row *************************** event_time: 2014-08-14 10:53:18 user_host: root[root] @ localhost [] thread_id: 3 server_id: 0 command_type: query argument: select * from mysql.general_log *************************** 2. row *************************** event_time: 2014-08-14 10:54:25 user_host: root[root] @ localhost [] thread_id: 3 server_id: 0 command_type: query argument: select * from mysql.general_log 2 rows in set (0.00 sec) error: no query specified
输出到file：
mysql> set global log_output='file';
query ok, 0 rows affected (0.00 sec)
mysql> set global general_log_file='/tmp/general.log';
query ok, 0 rows affected (0.01 sec)
[root@mysql-db101 tmp]# more /tmp/general.log
/home/mysql/mysql/bin/mysqld, version: 5.6.16 (source distribution). started with: tcp port: 3306 unix socket: /home/mysql/logs/mysql.sock time id command argument 140814 10:56:44 3 query select * from mysql.general_log
查询次数最多的sql语句
analysis-general-log.py general.log | sort | uniq -c | sort -nr
1032 select * from wp_comments where ( comment_approved = 'x' or comment_approved = 'x' ) and comment_post_id = x order by comment_date_gmt desc 653 select post_id, meta_key, meta_value from wp_postmeta where post_id in (x) order by meta_id asc 527 select found_rows() 438 select t.*, tt.* from wp_terms as t inner join wp_term_taxonomy as tt on t.term_id = tt.term_id where tt.taxonomy = 'x' and t.term_id = x limit 341 select option_value from wp_options where option_name = 'x' limit 329 select t.*, tt.*, tr.object_id from wp_terms as t inner join wp_term_taxonomy as tt on tt.term_id = t.term_id inner join wp_term_relationships as tr on tr.term_taxonomy_id = tt.term_taxonomy_id where tt.taxonomy in (x) and tr.object_id in (x) order by t.name asc 311 select wp_posts.* from wp_posts where 1= x and wp_posts.id in (x) and wp_posts.post_type = 'x' and ((wp_posts.post_status = 'x')) order by wp_posts.post_date desc 219 select wp_posts.* from wp_posts where id in (x) 218 select tr.object_id from wp_term_relationships as tr inner join wp_term_taxonomy as tt on tr.term_taxonomy_id = tt.term_taxonomy_id where tt.taxonomy in (x) and tt.term_id in (x) order by tr.object_id asc 217 select wp_posts.* from wp_posts where 1= x and wp_posts.id in (x) and wp_posts.post_type = 'x' and ((wp_posts.post_status = 'x')) order by wp_posts.menu_order asc 202 select sql_calc_found_rows wp_posts.id from wp_posts where 1= x and wp_posts.post_type = 'x' and (wp_posts.post_status = 'x') order by wp_posts.post_date desc limit 118 set names utf8 115 set session sql_mode= 'x' 115 select @@session.sql_mode 112 select option_name, option_value from wp_options where autoload = 'x' 111 select user_id, meta_key, meta_value from wp_usermeta where user_id in (x) order by umeta_id asc 108 select year(min(post_date_gmt)) as firstdate, year(max(post_date_gmt)) as lastdate from wp_posts where post_status = 'x' 108 select t.*, tt.* from wp_terms as t inner join wp_term_taxonomy as tt on t.term_id = tt.term_id where tt.taxonomy in (x) and tt.count > x order by tt.count desc limit 107 select t.*, tt.* from wp_terms as t inner join wp_term_taxonomy as tt on t.term_id = tt.term_id where tt.taxonomy in (x) and t.term_id in (x) order by t.name asc 107 select * from wp_users where id = 'x' 106 select sql_calc_found_rows wp_posts.id from wp_posts where 1= x and wp_posts.post_type = 'x' and (wp_posts.post_status = 'x') and post_date > 'x' order by wp_posts.post_date desc limit 106 select sql_calc_found_rows wp_posts.id from wp_posts where 1= x and wp_posts.post_type = 'x' and (wp_posts.post_status = 'x') and post_date > 'x' order by rand() desc limit 105 select sql_calc_found_rows wp_posts.id from wp_posts where 1= x and wp_posts.post_type = 'x' and (wp_posts.post_status = 'x') and post_date > 'x' order by wp_posts.comment_count desc limit
ps：mysql general log日志清除技巧
mysql general log日志不能直接删除，间接方法
use mysql; create table gn2 like general_log; rename table general_log to oldlogs, gn2 to general_log;
如对本文有疑问， 点击进行留言回复！！
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
【MySQL牛客】10.获取所有非manager的员工emp_no
我要评论
网友评论