mysql query log_Mysql query log

----默认情况下查看是否启用查询日志：[root@node4 mysql5.5]# service mysql start

Starting MySQL....[OK]

[root@node4 mysql5.5]# mysql

Welcometo the MySQL monitor. Commands end with ; or\g.

Your MySQL connection idis 1Server version:5.5.22-logSource distribution

Copyright (c)2000, 2011, Oracle and/or its affiliates. Allrights reserved.

Oracleis a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarksoftheir respective

owners.

Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

mysql> show variables like '%log';+--------------------------------+-------+

| Variable_name | Value |

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

| back_log | 50 |

| general_log | OFF |

| innodb_locks_unsafe_for_binlog | OFF |

| log | OFF |

| relay_log | |

| slow_query_log | OFF |

| sync_binlog | 0 |

| sync_relay_log | 0 |

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

8 rows in set (0.00sec)----备注：log和general_log这两个参数是兼容的。而默认的情况下查询日志是不开启的

----使用下面的命令是开启查询日志

mysql> set global log=1;

Query OK,0 rows affected, 1 warning (0.03sec)

mysql> show variables like '%log';+--------------------------------+-------+

| Variable_name | Value |

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

| back_log | 50 |

| general_log | ON |

| innodb_locks_unsafe_for_binlog | OFF |

| log | ON |

| relay_log | |

| slow_query_log | OFF |

| sync_binlog | 0 |

| sync_relay_log | 0 |

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

8 rows in set (0.00sec)----其中log参数是过时的，在启动选项中使用log参数的话，会在err日志中显示出来。

----修改my.cnf文件，添加log的参数设置

[root@node4 mysql5.5]# vi my.cnf[root@node4 mysql5.5]# cat ./my.cnf |grep '^log='

log=/tmp/mysqlgen.log

----清空err日志

[root@node4 mysql5.5]# cat /dev/null > /tmp/mysql3306.err[root@node4 mysql5.5]# ll /tmp/mysql3306.err-rw-rw---- 1 mysql root 0 Jul 31 07:50 /tmp/mysql3306.err

[root@node4 mysql5.5]# service mysql start

Starting MySQL...[ OK ]

----启动数据库后查看err日志的内容

[root@node4 mysql5.5]# cat /tmp/mysql3306.err130731 07:51:32 mysqld_safe Starting mysqld daemon with databases from /opt/mysql5.5/data130731 7:51:32 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file'instead.130731 7:51:33 InnoDB: The InnoDB memory heap isdisabled130731 7:51:33 InnoDB: Mutexes and rw_locks use InnoDB's own implementation

130731 7:51:33 InnoDB: Compressed tables use zlib 1.2.3

130731 7:51:33 InnoDB: Initializing buffer pool, size = 128.0M

130731 7:51:33 InnoDB: Completed initialization of buffer pool

130731 7:51:33 InnoDB: highest supported file format is Barracuda.

130731 7:51:33 InnoDB: Waiting for the background threads to start

130731 7:51:34 InnoDB: 1.1.8 started; log sequence number 1625855

130731 7:51:34 [Note] Event Scheduler: Loaded 0 events

130731 7:51:34 [Note] /opt/mysql5.5/bin/mysqld: ready for connections.

Version:'5.5.22-log'socket:'/tmp/mysql.sock'port: 3306 Source distribution

----使用最新的参数

----general_log和general_log_file。

[root@node4 mysql5.5]# service mysql stop

Shutting down MySQL. [ OK ]

[root@node4 mysql5.5]# vi my.cnf

[root@node4 mysql5.5]# cat ./my.cnf |grep'^general'general_log = 1

general_log_file = /tmp/mysqlgen.log

[root@node4 mysql5.5]# service mysql start

Starting MySQL... [ OK ]

[root@node4 mysql5.5]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.22-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type'help;'or'\h'for help. Type'\c'to clear the current input statement.

mysql> show variables like'%log';

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

| Variable_name | Value |

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

| back_log | 50 |

| general_log | ON |

| innodb_locks_unsafe_for_binlog | OFF |

| log | ON |

| relay_log | |

| slow_query_log | OFF |

| sync_binlog | 0 |

| sync_relay_log | 0 |

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

8 rows in set (0.04 sec)

mysql> show variables like'%file';

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

| Variable_name | Value |

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

| ft_stopword_file | (built-in) |

| general_log_file | /tmp/mysqlgen.log |

| init_file | |

| local_infile | ON |

| pid_file | /tmp/mysql3306.pid |

| relay_log_info_file | relay-log.info |

| slow_query_log_file | /opt/mysql5.5/data/node4-slow.log |

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

7 rows in set (0.00 sec)

----在上面的操作中可以看到已经启用查询日志，并且文件目录是/tmp/mysqlgen.log。

----查询日志记录了哪些东西？

进行下面的查询

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test2 |

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

5 rows in set (0.08 sec)

mysql> use test;

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> use test2;

Database changed

mysql> show tables;

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

| Tables_in_test2 |

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

| course |

| jack |

| sc |

| student |

| t |

| teacher |

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

6 rows in set (0.07 sec)

mysql> drop table t;

Query OK, 0 rows affected (0.13 sec)

mysql> select * from sc;

Empty set (0.04 sec)

----可以看到上面的操作都记录在了mysqlgen.log里面。

[root@node4 ~]# tail -f /tmp/mysqlgen.log

/opt/mysql5.5/bin/mysqld, Version: 5.5.22-log (Source distribution). started with:

Tcp port: 3306 Unix socket: /tmp/mysql.sock

Time Id Command Argument

130731 7:55:41 1 Query show databases

130731 7:55:56 1 Query SELECT DATABASE()

1 Init DB test

130731 7:55:59 1 Query show tables

130731 7:56:19 1 Query SELECT DATABASE()

1 Init DB test2

130731 7:56:23 1 Query show tables

130731 7:56:27 1 Query drop table t

130731 7:56:39 1 Query select * from sc