查询日志的作用是记录所有客户端发来的sql语句，也就是记录客户端的所有操作
Log参数将要过时，现在用general_log来代替
打开查询日志功能
mysql> showvariables like "%log";
+----------------+-------+
| Variable_name| Value |
+----------------+-------+
| back_log| 50|
| general_log| OFF|
| log| OFF|
| relay_log||
| slow_query_log | OFF|
| sync_binlog| 0|
+----------------+-------+
6 rows in set (0.01 sec)
mysql> set global general_log=1;
Query OK, 0 rows affected (0.18 sec)
mysql> set globallog=1;
Query OK, 0 rows affected, 1 warning (0.00sec)
mysql> showvariables like "%log";
+----------------+-------+
| Variable_name| Value |
+----------------+-------+
| back_log| 50|
| general_log| ON|
| log| ON|
| relay_log||
| slow_query_log | OFF|
| sync_binlog| 0|
+----------------+-------+
6 rows in set (0.00 sec)
也可以在my.cnf中添加参数
general_log=1
general_log_file=/tmp/chenzhongyang.log这样来设定
mysql> show variables like"%file";
+---------------------+-----------------------------------------+
| Variable_name| Value|
+---------------------+-----------------------------------------+
| ft_stopword_file| (built-in)|
| general_log_file| /tmp/chenzhongyang.log|
| init_file||
| local_infile| ON|
| pid_file|/usr/local/mysql/var/test4.wolf.org.pid |
| relay_log_info_file | relay-log.info|
| slow_query_log_file |/usr/local/mysql/var/test4-slow.log|
+---------------------+-----------------------------------------+
查看日志文件的内容
[root@test4 ~]# tail -f/tmp/chenzhongyang.log
/usr/local/mysql/libexec/mysqld, Version:5.1.70-log (Source distribution). started with:
Tcp port: 3306Unix socket: /tmp/mysql.sock
TimeId CommandArgument
130903 16:09:431 Connectroot@localhost on
1 Queryselect@@version_comment limit 1
130903 16:10:141 Queryshow variables like "%file"
130903 16:12:091 Queryselect * fromtt
130903 16:12:161 QuerySELECT DATABASE()
1 Init DBtest
130903 16:12:181 Queryselect * fromtt
一般情况下我们不打开查询日志功能，因为他对系统效率的影响很大
管理查询日志文件
一般的日志文件会很大，所以要处理日志
[root@test4 ~]# mysqladminflush-logs;刷新日志所有日志文件
from: http://wolfword.blog.51cto.com/4892126/1287978
慢查询日志（不包括获取锁的时间）
1，开启慢查询日志功能
以前的版本中开启慢查询日志功能的参数是--log_slow_queries在my.cnf文件中指定，但是现在新的版本中用参数--slow_query_log和--slow_query_log_file来指定
slow_query_log=1
slow_query_log_file=/tmp/mysqlslow.log
重新启动mysql
mysql> show variables like "%slow%";
+---------------------+--------------------+
| Variable_name | Value |
+---------------------+--------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /tmp/mysqlslow.log |
+---------------------+--------------------+
4 rows in set (0.00 sec)
2，慢查询相关的 参数long_query_time
当查询超过long_query_time指定的时间，那么就会记录在慢查询日志文件中，默认是10秒
mysql> show variables like "%long%";
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| long_query_time | 10.000000 |
我们来吧时间调短试试，看看什么变化
mysql> set session long_query_time=1; 调到1秒
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%long%";
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| long_query_time | 1.000000 |
| max_long_data_size | 1048576 |
+--------------------+----------+
2 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> create table t as select * from information_schema.tables;
Query OK, 53 rows affected (0.12 sec)
Records: 53 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 3392 rows affected (0.11 sec)
Records: 3392 Duplicates: 0 Warnings: 0
这个时候达到了1秒钟了所以就要记录sql语句
mysql> insert into t select * from t;
^[[AQuery OK, 6784 rows affected (1.15 sec)
Records: 6784 Duplicates: 0 Warnings: 0
mysql> system more /tmp/mysqlslow.log
/usr/local/mysql/libexec/mysqld, Version: 5.1.70-log (Source distribution). star
ted with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 130903 18:46:28
# User@Host: root[root] @ localhost []
# Query_time: 1.150157 Lock_time: 0.000205 Rows_sent: 0 Rows_examined: 13568
use test;
SET timestamp=1378205188;
insert into t select * from t;
3，慢查询相关的 参数log_queries_not_using_indexes
如果log_queries_not_using_indexes为ON的话，当执行一个sql语句的时候，如果一个表没有索引就会把这个信息记录在慢查询文件中
mysql> show variables like "%log_queries_not%"
-> ;
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
我们来吧log_queries_not_using_indexes设置为ON的时候来试试吧
mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%log_queries_not%";
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.01 sec)
mysql> show index from t; 表t没有索引
Empty set (0.00 sec)
mysql> select * from t where TABLE_NAME="xxxxxxxxxxx";
Empty set (0.11 sec)
这个时候慢日志文件记录下来了这个sql语句
mysql> system tail /tmp/mysqlslow.log;
# User@Host: root[root] @ localhost []
# Query_time: 1.150157 Lock_time: 0.000205 Rows_sent: 0 Rows_examined: 13568
use test;
SET timestamp=1378205188;
insert into t select * from t;
# Time: 130903 18:58:17
# User@Host: root[root] @ localhost []
# Query_time: 0.100749 Lock_time: 0.057377 Rows_sent: 0 Rows_examined: 27136
SET timestamp=1378205897;
select * from t where TABLE_NAME="xxxxxxxxxxx";
mysql>
4，工具mysqldumpslow用来分析sql语句慢查询
[root@test4 Desktop]# mysqldumpslow -s t -t 2 /tmp/mysqlslow.log
Reading mysql slow query log from /tmp/mysqlslow.log
Count: 1 Time=1.15s (1s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t select * from t
Count: 1 Time=0.04s (0s) Lock=0.06s (0s) Rows=0.0 (0), root[root]@localhost
select * from t where TABLE_NAME="S"
from: http://wolfword.blog.51cto.com/4892126/1287992