Mysql通用日志总结
1、通用日志概念及作用通用日志会记录mysql的所有操作，包含查询操作，方便开发人员与数据库人员跟踪数据执行过程。Mysql相关的参数：log_output= #通用查询日志输出格式general_log= #是否启用通用查询日志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: 3306Unix socket: /var/lib/mysql/mysql.sockTime Id Command Argument2016-11-25T09:17:12.234377Z 136 Queryshow variables like 'log_output'2016-11-25T09:17:32.614030Z 136 Queryset global log_output='TABLE'2016-11-25T09:23:54.132140Z 136 Queryselect @@global.log_output2016-11-25T09:24:08.725540Z 136 Queryselect thread_id,command_type,argument,event_time from mysql.general_log2016-11-25T09:24:15.510491Z 136 Querycommit
查看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操作也不会被记录在通用日志中。
页:
[1]