MySQL log_output参数

jiawang

在MySQL里log_output参数控制general log和slow log输出的格式和一个或多个目标。该值是从TABLE、FILE和NONE中选择的一个或多个逗号分隔单词的列表。
TABLE选择对mysql系统数据库中的general_log和slow_log表进行日志记录。
FILE选择记录以记录文件。NONE禁用日志记录。



查看参数设置如下：

mysql> SHOW VARIABLES LIKE  'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

#指定多个输出方式
mysql> SET GLOBAL log_output='FILE,TABLE';

#配置文件参数
[mysqld]
log_output=TABLE,FILE

TABLE方式
对于FILE输出都比较熟悉，看下TABLE方式输出。TABLE方式默认采用的是CSV引擎。CSV引擎表实际上是具有逗号分隔值的文本文件。CSV表允许以CSV格式导入或转储数据，以便与读取和写入相同格式的脚本和应用程序交换数据。因为CSV表没有索引，所以通常在正常操作期间将数据保存在InnoDB表中，并且仅在导入或导出阶段使用CSV表。
下面看下slow_log的表结构和数据：


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




SQL语句保存在sql_text字段mediumblob类型保存。还需要使用程序解开才可以。比较麻烦。
mysql> select * from slow_log limit 1\G
*************************** 1. row ***************************
    start_time: 2023-12-08 10:07:30.462000
     user_host: root[root] @ localhost []
    query_time: 00:00:00.000957
     lock_time: 00:00:00.000005
     rows_sent: 0
rows_examined: 0
            db: mysql
last_insert_id: 0
     insert_id: 0
     server_id: 129
      sql_text: 0x73657420676C6F62616C2020736C6F775F71756572795F6C6F673D4F4E
     thread_id: 15
1 row in set (0.00 sec)


log_output表操作
对于output的表，在slow_query_log=OFF下可以DROP表 。


#ON状态下
mysql> SET GLOBAL slow_query_log=ON;
mysql> DROP TABLE slow_log;
ERROR 1580 (HY000): You cannot DROP a log table if logging is enabled
'
#OFF状态下
mysql> SET GLOBAL slow_query_log=OFF;
mysql> DROP TABLE slow_log;
Query OK, 0 rows affected (0.00 sec)


#再次开启慢日志，因为表不存在，所以报错，需要手动重新创建。
mysql> SET GLOBAL slow_query_log=ON;
ERROR 1146 (42S02): Table 'mysql.slow_log' doesn't exist
目前表支持MyISAM和CSV引擎的转换，不能改成Innodb引擎。


#关闭慢日志
mysql> SET GLOBAL slow_query_log=OFF;
Query OK, 0 rows affected (0.00 sec)


#更改引擎MyISAM
mysql>  ALTER TABLE slow_log engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


#更改引擎CSV
mysql> ALTER TABLE slow_log engine=CSV;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


#在MyISAM引擎下添加索引
mysql>  ALTER TABLE slow_log engine=MyISAM;
mysql> ALTER TABLE slow_log add index idx_id(insert_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


#更改引擎InnoDB，失败
mysql> ALTER TABLE slow_log engine=InnoDB;
ERROR 1579 (HY000): This storage engine cannot be used for log tables
官方说明中，可以RENAME重命名方式替换表同时改成InnoDB引擎.如下新的记录表采用InnoDB引擎，为了查询方便在start_time创建索引。当然也可以关闭日志DROP之后手动创建。


#slow_log_tmp新表
USE mysql;
CREATE TABLE `slow_log_tmp` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int NOT NULL,
  `rows_examined` int NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int NOT NULL,
  `insert_id` int NOT NULL,
  `server_id` int unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint unsigned NOT NULL,
  KEY `idx_dt` (`start_time`)
) ENGINE=InnoDB；


#进行rename操作
mysql> RENAME TABLE slow_log TO slow_log_backup1, slow_log_tmp TO slow_log;
Query OK, 0 rows affected (0.02 sec)
对于output表，可以进行TRUNCATE方式清理，但不支持DELETE语句。


mysql> truncate table slow_log;
Query OK, 0 rows affected (0.03 sec)


mysql> delete from  slow_log;
ERROR 1556 (HY000): You can't use locks with log tables.
除此之外，output表


支持：CHECK TABLE。
不支持：分区。
不支持：LOCK TABLES。
不支持：INSERT、DELETE和UPDATE语句人为操作该表。这些操作只能在MySQL服务本身内部进行。
FLUSH TABLES WITH READ LOCK和READ_only参数对output表没有影响。
binlog不会记入，不影响复制。


总结
建议还是采取FILE方式，TABLE方式消耗MySQL服务性能。要是数据量积累过多也会影响整体性能。