1、通用日志概念及作用 通用日志会记录mysql的所有操作,包含查询操作,方便开发人员与数据库人员跟踪数据执行过程。 Mysql相关的参数: log_output=[none|file|table|file,table] #通用查询日志输出格式 general_log=[on|off] #是否启用通用查询日志 general_log_file[=filename] #通用查询日志位置及名字 注:file与table的区别,file会记录在文件中,而table是记录在mysql.general_log表中。 2、如何开启(使用file输出格式) 2.1、通用日志默认是不开启,查看方法1
2
| mysql> select version();+------------+| version() |+------------+| 5.7.16-log |+------------+1 row in set (0.00 sec)
mysql> show variables like "%general%";+------------------+---------------------------+| Variable_name | Value |+------------------+---------------------------+| general_log | OFF || general_log_file | /var/lib/mysql/aboss5.log |+------------------+---------------------------+2 rows in set (0.00 sec)
|
2.2、临时设置(不需要重启)1
2
3
| mysql> set global log_output=file;Query OK, 0 rows affected (0.00 sec)
mysql> set global general_log_file='/var/lib/mysql/mysql_general.log';Query OK, 0 rows affected (0.00 sec)
mysql> set global general_log=on;Query OK, 0 rows affected (0.01 sec)
|
使用完记得关闭,要不然会影响mysql性能 1
| mysql> set global general_log=off;Query OK, 0 rows affected (0.00 sec)
|
2.3、永久设置(需要重启机器)1
2
3
4
| vim /etc/my.cnf
log_output=file
general_log=on
general_log_file=/var/lib/mysql/mysql-general.log
|
3、如何开启(使用table或同时开启table与file两者输出格式) 3.1、临时改成table输出格式注:永久操作,就是在Mysql的my.cnf配置就可以了,这里就不操作。 1
2
3
4
| mysql> set global general_log=on;Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_output';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output | FILE |+---------------+-------+1 row in set (0.00 sec)
mysql> set global log_output='TABLE';Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.slow_log;Empty set (0.00 sec)
|
查看结果 1
| mysql> select thread_id,command_type,argument,event_time from mysql.general_log;+-----------+--------------+--------------------------------------------------------------------------+----------------------------+| thread_id | command_type | argument | event_time |+-----------+--------------+--------------------------------------------------------------------------+----------------------------+| 136 | Query | select * from mysql.slow_log | 2016-11-25 17:17:44.237846 || 136 | Query | desc mysql.general_log | 2016-11-25 17:19:05.909411 || 136 | Query | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:19:50.188954 |+-----------+--------------+--------------------------------------------------------------------------+----------------------------+3 rows in set (0.00 sec)
|
3.2、同时开启FILE,TABLE 两者输出格式1
2
3
4
| mysql> set global log_output='file,table';Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.log_output;+---------------------+| @@global.log_output |+---------------------+| FILE,TABLE |+---------------------+1 row in set (0.00 sec)
mysql> select thread_id,command_type,argument,event_time from mysql.general_log;+-----------+--------------+--------------------------------------------------------------------------+----------------------------+| thread_id | command_type | argument | event_time |+-----------+--------------+--------------------------------------------------------------------------+----------------------------+| 136 | Query | select * from mysql.slow_log | 2016-11-25 17:17:44.237846 || 136 | Query | desc mysql.general_log | 2016-11-25 17:19:05.909411 || 136 | Query | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:19:50.188954 || 136 | Query | show variables like 'log_output' | 2016-11-25 17:23:21.393370 || 136 | Query | set global log_output='file,table' | 2016-11-25 17:23:41.443710 || 136 | Query | select @@global.log_output | 2016-11-25 17:23:54.132140 || 136 | Query | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:24:08.725540 |+-----------+--------------+--------------------------------------------------------------------------+----------------------------+7 rows in set (0.00 sec)
mysql> commit;Query OK, 0 rows affected (0.00 sec)
|
日志文件查看 1
2
| mysql> system tail /var/lib/mysql/mysql_general.log
/usr/sbin/mysqld, Version: 5.7.16-log (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sockTime Id Command Argument2016-11-25T09:17:12.234377Z 136 Query show variables like 'log_output'2016-11-25T09:17:32.614030Z 136 Query set global log_output='TABLE'2016-11-25T09:23:54.132140Z 136 Query select @@global.log_output2016-11-25T09:24:08.725540Z 136 Query select thread_id,command_type,argument,event_time from mysql.general_log2016-11-25T09:24:15.510491Z 136 Query commit
|
查看mysql.general_log 1
| mysql> select thread_id,command_type,argument,event_time from mysql.general_log;+-----------+--------------+--------------------------------------------------------------------------+----------------------------+| thread_id | command_type | argument | event_time |+-----------+--------------+--------------------------------------------------------------------------+----------------------------+| 136 | Query | select * from mysql.slow_log | 2016-11-25 17:17:44.237846 || 136 | Query | desc mysql.general_log | 2016-11-25 17:19:05.909411 || 136 | Query | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:19:50.188954 || 136 | Query | show variables like 'log_output' | 2016-11-25 17:23:21.393370 || 136 | Query | set global log_output='file,table' | 2016-11-25 17:23:41.443710 || 136 | Query | select @@global.log_output | 2016-11-25 17:23:54.132140 || 136 | Query | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:24:08.725540 || 136 | Query | commit | 2016-11-25 17:24:15.510491 || 136 | Query | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:25:11.699651 |+-----------+--------------+--------------------------------------------------------------------------+----------------------------+9 rows in set (0.00 sec)
|
4、关于设置一些小结论 (1)当log_output设置为 TABLE或FILE或两者都设置,而general_log=off时,sql操作不会记录记录在通用日志中。 (2)当log_output设置为NONE,general_log=on时,sql操作也不会被记录在通用日志中。
|