MySQL中的通用查询日志(General Query Log)
1. 以什么形式来记录?
log_output系统变量来决定的,可选值 TABLE, FILE, 或者 NONE,默认值是FILE,可以同时选择TABLE和FILE,中间用逗号隔开。
2. 如何开启?
设置general_log系统变量,设置为ON,OFF
3. 具体写到哪里?
如果是写到文件里,由general_log_file指定,如果要看默认值,可以使用show variables like 'general_log_file'。如果写到表里,则写到mysql.general_log
4. 何时开启?
可以在启动MySQL server的时候开启,指定--log_output,--general_log和--general_log_file选项;也可以在运行时开启,设置环境变量log_output,general_log和general_log_file
5. 记录什么内容?
The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.
测试:
下面的测试中分别从db2b和db2a上连接了MySQL数据库,并进行了一些增、删、改、查的动作,看一下通用查询日志记录了哪些内容:
db2a为MySQL server:
mysql> set global general_log=1;
mysql> show variables like 'general_log_file';
+------------------+-------------------------+
| Variable_name | Value |
+------------------+-------------------------+
| general_log_file | /var/lib/mysql/db2a.log |
+------------------+-------------------------+
1 row in set (0.04 sec)
mysql> alter user 'user1'@'db2b' identified by 'user1';
db2b上:
root@db2b:~# mysql -h db2a -u user1 -pwrong sample
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'db2b' (using password: YES)
root@db2b:~# mysql -h db2a -u user1 -puser1 sample
mysql> drop table test1;
mysql> create table test1(year);
mysql> create table test1(year int);
mysql> insert into test1 values(2007),(2010);
mysql> select * from test1 where year > 2008;
mysql> delete from test1 where year < 2008;
mysql> show variables like 'default_password_lifetime';
mysql> quit
db2a上:
root@db2a:~# mysql -u root -pqingsong sample;
mysql> select * from test2;
mysql> set global general_log=0;
可以看到,执行的drop/create table, select/insert/update/delete,show variables,set,以及连接动作(成功与否)/退出都被记录了下来。
参考链接:
https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html
https://dev.mysql.com/doc/refman/5.7/en/query-log.html
1. 以什么形式来记录?
log_output系统变量来决定的,可选值 TABLE, FILE, 或者 NONE,默认值是FILE,可以同时选择TABLE和FILE,中间用逗号隔开。
2. 如何开启?
设置general_log系统变量,设置为ON,OFF
3. 具体写到哪里?
如果是写到文件里,由general_log_file指定,如果要看默认值,可以使用show variables like 'general_log_file'。如果写到表里,则写到mysql.general_log
4. 何时开启?
可以在启动MySQL server的时候开启,指定--log_output,--general_log和--general_log_file选项;也可以在运行时开启,设置环境变量log_output,general_log和general_log_file
5. 记录什么内容?
The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.
测试:
下面的测试中分别从db2b和db2a上连接了MySQL数据库,并进行了一些增、删、改、查的动作,看一下通用查询日志记录了哪些内容:
db2a为MySQL server:
mysql> set global general_log=1;
mysql> show variables like 'general_log_file';
+------------------+-------------------------+
| Variable_name | Value |
+------------------+-------------------------+
| general_log_file | /var/lib/mysql/db2a.log |
+------------------+-------------------------+
1 row in set (0.04 sec)
mysql> alter user 'user1'@'db2b' identified by 'user1';
db2b上:
root@db2b:~# mysql -h db2a -u user1 -pwrong sample
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'db2b' (using password: YES)
root@db2b:~# mysql -h db2a -u user1 -puser1 sample
mysql> drop table test1;
mysql> create table test1(year);
mysql> create table test1(year int);
mysql> insert into test1 values(2007),(2010);
mysql> select * from test1 where year > 2008;
mysql> delete from test1 where year < 2008;
mysql> show variables like 'default_password_lifetime';
mysql> quit
db2a上:
root@db2a:~# mysql -u root -pqingsong sample;
mysql> select * from test2;
mysql> set global general_log=0;
看一下 cat /var/lib/mysql/db2a.log
root@db2a:~# cat /var/lib/mysql/db2a.log
mysqld, Version: 5.7.18-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
2017-09-18T12:26:25.428531Z 16 Query show variables like 'general_log_file'
2017-09-18T12:26:32.415259Z 16 Query ALTER USER 'user1'@'db2b' IDENTIFIED WITH 'mysql_native_password' AS '<secret>'
2017-09-18T12:26:41.703283Z 23 Connect user1@db2b on sample using SSL/TLS
2017-09-18T12:26:41.703388Z 23 Connect Access denied for user 'user1'@'db2b' (using password: YES)
2017-09-18T12:26:50.012271Z 24 Connect user1@db2b on sample using SSL/TLS
2017-09-18T12:26:50.049494Z 24 Query show databases
2017-09-18T12:26:50.055891Z 24 Query show tables
2017-09-18T12:26:50.068155Z 24 Field List A
2017-09-18T12:26:50.084237Z 24 Field List B
2017-09-18T12:26:50.084982Z 24 Field List child
2017-09-18T12:26:50.085600Z 24 Field List employee
2017-09-18T12:26:50.086364Z 24 Field List s2
2017-09-18T12:26:50.087536Z 24 Field List t1
2017-09-18T12:26:50.088150Z 24 Field List t2
2017-09-18T12:26:50.088679Z 24 Field List test1
2017-09-18T12:26:50.089221Z 24 Field List test2
2017-09-18T12:26:50.091282Z 24 Query select @@version_comment limit 1
2017-09-18T12:26:56.784946Z 24 Query drop table test1
2017-09-18T12:27:08.058195Z 24 Query create table test1(year int)
2017-09-18T12:27:12.764736Z 24 Query insert into test1 values(2007),(2010)
2017-09-18T12:27:16.740802Z 24 Query select * from test1 where year > 2008
2017-09-18T12:27:22.588631Z 24 Query delete from test1 where year < 2008
2017-09-18T12:27:26.476919Z 24 Query show variables like 'default_password_lifetime'
2017-09-18T12:27:29.008515Z 24 Quit
2017-09-18T12:28:09.577592Z 25 Connect root@localhost on sample using Socket
2017-09-18T12:28:09.638443Z 25 Query show databases
2017-09-18T12:28:09.660399Z 25 Query show tables
2017-09-18T12:28:09.661135Z 25 Field List A
2017-09-18T12:28:09.661638Z 25 Field List B
2017-09-18T12:28:09.662730Z 25 Field List child
2017-09-18T12:28:09.663875Z 25 Field List employee
2017-09-18T12:28:09.665264Z 25 Field List s2
2017-09-18T12:28:09.666117Z 25 Field List t1
2017-09-18T12:28:09.666671Z 25 Field List t2
2017-09-18T12:28:09.667845Z 25 Field List test1
2017-09-18T12:28:09.668325Z 25 Field List test2
2017-09-18T12:28:09.668993Z 25 Query select @@version_comment limit 1
2017-09-18T12:28:23.349771Z 25 Query select * from test2
2017-09-18T12:28:27.891398Z 25 Query set global general_log=0
可以看到,执行的drop/create table, select/insert/update/delete,show variables,set,以及连接动作(成功与否)/退出都被记录了下来。
参考链接:
https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html
https://dev.mysql.com/doc/refman/5.7/en/query-log.html