MySQL学习笔记十：日志管理

reference：http://www.cnblogs.com/zmxmumu/p/4434791.html

1.错误日志文件

MySQL的错误日志文件记录错误信息，还会记录mysqld进程的关闭和启动的信息，但也不是什么错误信息都会记录，只记录mysqld进程运行过程中发生的关键性错误。

错误日志的启动，可以在启动mysqld进程时，通过log-error选项来指定错误日志文件名和存放位置，或者my.ini配置文件中配置log-error参数，如果没有指定文件名的话，会自动生成一个[hostname].err文件保存在{datadir}文件夹下。

在删除错误日志后，会把错误日志重建到[hostname].err.old的文件夹下。删除日志可以使用使用flush logs。

2.慢查询日志，有利于SQL语句调优

慢查询日志会记录所有查询语句执行时间大于系统变量long_query_time的值。默认情况下，慢查询日志功能是被禁止的。

mysql> show variables like 'slow%'; +---------------------+--------------------+ | Variable_name | Value | +---------------------+--------------------+ | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | LENOVO-PC-slow.log | +---------------------+--------------------+

要开启慢查询日志功能，可以在my.ini配置文件中配置以下语句，或直接在客户端设置

slow-query-log=1 --0 关闭 slow_query_log_file="LENOVO-PC-slow.log" long_query_time=10 --慢查询语句执行时间阀值 set global slow_query_log = on --开启 set global slow_query_log = off --禁止

要查看一个较大的慢查询日志，可以使用mysqldumpslow命令，或者第三方工具mysqlsla。

3.普通查询日志

普通查询日志(Qeneral Query Log)，会记录mysqld进程几乎所有的操作，包括客户端的SQL语句查询，数据库和对象的管理操作等等。

普通查询日志的开启或关闭，可以在客户端进行，亦可以在配置文件中设置。

mysql> set global general_log=1; Query OK, 0 rows affected (0.03 sec) mysql> show variables like 'general%'; +------------------+---------------+ | Variable_name | Value | +------------------+---------------+ | general_log | ON | | general_log_file | LENOVO-PC.log | +------------------+---------------+ 2 rows in set (0.00 sec)

如果要在某个会话开启/禁用普通查询日志，可以使用sql_log_off变量来设置。

set sql_log_off = N --1(on) 开启 0(off) 关闭

在mysql5.6版本之前，普通日志会记录用户更新密码的信息，会有安全隐患，不过在5.6版本在记录用户相关信息时会自动重写，将涉及密码的部分加密后再写入。

4.查询日志配置

设置查询日志的输出位置，可以在mysql服务启动时通过--log-output参数指定，也可以在服务运行时设置，如下所示

D:\MySQL\MySQL Server 5.5\bin> mysqld --log-output=Table,File --general_log --log-output参数值有三个选项，Table,File,None --Table，即将日志记录到日志表中，相对应的有general_log和slow_log表 --File,将日志记录到文件中 --None,不输出日志

启用普通查询日志和慢查询日志，并输出到表

--log-output=TABLE --general_log --slow_log

在服务运行中设置，主要有log_output,general_log,slow_query_log,sql_log_off等系统变量

mysql> show variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | NONE | +---------------+-------+ mysql> set global log_output='TABLE'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+ 1 row in set (0.00 sec)

5.二进制日志文件

二进制日志记录数据库的修改事件，通过二进制日志，能够实现两个非常重要的功能，复制和恢复。

二进制日志的开启,方法1：在配置文件中开启  2：在服务启动时，使用--log-bin参数开启

[mysqld] log-bin=mysql-bin D:\MySQL\MySQL Server 5.5\bin> mysqld --log-bin=mysql-bin

每次开启mysql服务时，都会新建一个二进制日志文件，其命名格式为[=base_name]-bin.000001,单个日志文件大小不是无限的，有max_binlog_size进行控制

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

开启二进制功能后，也可以通过sql_bin_log变量或者my.ini的binlog-do-db，binlog-ignore-db选项来限制某些语句或者数据库的修改生成二进制日志。

#需要备份数据，多个写多行 binlog-do-db=test #不需要备份的数据库，多个写多行 binlog-ignore-db=mysql mysql> show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> set sql_log_bin=0

查看二进制日志文件，使用mysqlbinlog命令

PS D:\ProgramData\MySQL\MySQL Server 5.5\data> mysqlbinlog .\mysql-bin.000001 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #150126 17:25:25 server id 3306 end_log_pos 107 Start: binlog v 4, ser t startup ROLLBACK/*!*/; BINLOG '

查看二进制日志文件并用于数据库恢复

D:\ProgramData\MySQL\MySQL Server 5.5\data> mysqlbinlog .\mysql-bin.000001 | mysql -u root -p

默认情况下，二进制日志不是同步刷新到磁盘的，这就会有丢失数据的隐患，为此mysql提供了一个sync_binlog的系统变量，用来设置二进制日志同步到磁盘的频率。

mysql> set global sync_binlog = x --当x=0时，二进制日志不与磁盘同步，即mysql不做同步刷新指令将binlog_cache中信息到磁盘 --当X=N时，当进行N次事务提交，并写入二进制日志，mysql将binlog_cache中的信息写入到磁盘；N为1时，系统最安全但性能损耗最大，因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。