log_output参数是指定日志的存储方式

log_output='FILE'表示将日志存入文件,默认值是'FILE'　log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中.

mysql数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'.日志记录到系统专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查日志,又需要比够获得更高的系统性能,那么建议优先记录到文件.

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

 但设置:log_output='TABLE'时,在线查询的时候,报错!!!

后来通过下面的设置问题解决:

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global log_output='FILE';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.log_output;
+---------------------+
| @@global.log_output |
+---------------------+
| FILE                |
+---------------------+
1 row in set (0.00 sec)

mysql> set @@global.slow_query_log = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set @@global.log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> show create table mysql.slow_log;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| slow_log | CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

mysql> select * from  mysql.slow_log limit 4\G;
*************************** 1. row ***************************
    start_time: 2017-05-06 14:13:48
     user_host: zabbix[zabbix] @ localhost []
    query_time: 00:00:00
     lock_time: 00:00:00
     rows_sent: 1
 rows_examined: 1
            db: zabbix
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: SELECT dv.mandatory,dv.optional FROM dbversion dv
*************************** 2. row ***************************
    start_time: 2017-05-06 14:13:48
     user_host: zabbix[zabbix] @ localhost []
    query_time: 00:00:00
     lock_time: 00:00:00
     rows_sent: 1
 rows_examined: 1
            db: zabbix
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: SELECT dv.mandatory,dv.optional FROM dbversion dv
*************************** 3. row ***************************
    start_time: 2017-05-06 14:13:48
     user_host: zabbix[zabbix] @ localhost []
    query_time: 00:00:00
     lock_time: 00:00:00
     rows_sent: 1
 rows_examined: 1
            db: zabbix
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: SELECT dv.mandatory,dv.optional FROM dbversion dv
*************************** 4. row ***************************
    start_time: 2017-05-06 14:13:48
     user_host: zabbix[zabbix] @ localhost []
    query_time: 00:00:00
     lock_time: 00:00:00
     rows_sent: 1
 rows_examined: 1
            db: zabbix
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: SELECT dv.mandatory,dv.optional FROM dbversion dv
4 rows in set (0.00 sec)

ERROR: 
No query specified

 

mysql> select @@global.slow_query_log;
+-------------------------+
| @@global.slow_query_log |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)