第16章　mysql系统库之日志记录表 - xiaoboluo768/qianjinliangfang GitHub Wiki

16.1　日志信息概述

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log ='OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
SET GLOBAL general_log = @old_log_state;

use mysql;
DROP TABLE IF EXISTS general_log2;
CREATE TABLE general_log2 LIKE general_log;
RENAME TABLE general_log TO general_log_backup，general_log2 TO general_log;

16.2.1　general_log

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

mysql> set global general_log=1;       
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.general_log\G
......
1 row in set (0.00 sec)

mysql> select connection_id();
......
1 row in set (0.00 sec)

shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> mv host_name-old.log backup-directory
# 在Windows上请直接重命名，而不是使用mv命令

mysql> SET GLOBAL general_log ='OFF';
# 在禁用查询日志记录功能的情况下，从外部重命名日志文件。例如，从命令行重命名日志文件，然后再次启用查询日志记录功能

Mysql> SET GLOBAL general_log ='ON';
# 此方法适用于任何平台，不需要重新启动服务器

16.2.2　slow_log

mysql> set global long_query_time=0;
Query OK, 0 rows affected (0.01 sec)

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)

# 断开会话重新连接
mysql> use test
Database changed
mysql> show tables;
......
5 rows in set (0.01 sec)

mysql> select * from test;
......
5 rows in set (0.01 sec)

mysql> select * from mysql.slow_log where sql_text='select * from test' limit 1\G
......
1 row in set (0.00 sec)

mysql> alter table sbtest1 modify pad char(100);
Query OK, 1000000 rows affected (12.32 sec)
Records: 1000000 Duplicates: 0 Warnings: 0

---

上一篇：第15章　mysql系统库之复制信息表 | 下一篇：第17章　mysql系统库应用示例荟萃