第 14 章 MySQL的日志管理

日志是MySQL数据库的重要组成部分，日志文件中记录着MySQL数据库运行期间发生的变化，如：MySQL数据库的客户端连接情况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复；也可以通过日志文件分析数据、优化查询等。MySQL日志管理机制比较完善，它包含了以下几种常用的日志文件，分别为：错误日志（-log-err）、查询日志（-log）、二进制日志（-log-bin）、更新日志（-log-update）及慢查询日志（-log-slow-queries）。对于MySQL管理员而言，日志文件是不可或缺的组成部分。本章介绍MySQL日志文件的操作及日志文件的管理。

14.1 操作错误日志

在MySQL数据库中，错误日志记录着MySQL服务器的启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况的相关信息、事件调度器运行一个事件时产生的信息、从服务器上启动服务器进程时产生的信息等。错误日志记录的并非全是错误信息，如MySQL如何启动InnoDB的表空间文件、如何初始化自己的存储引擎等信息也记录在错误日志文件中。

14.1.1 启动错误日志

错误日志功能默认状态下是开启的，并且不能被禁止。错误日志也可以自行配置，通过修改my.ini文件即可。错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中，log-err定义是否启用错误日志的功能和错误日志的村粗位置，log-warnings定义是否将警告信息也定义至错误日志中。–log-err=[file-name]用来指定错误日志存放的位置。如果没有指定[file-name]，默认hostname.err作为文件名，默认存放在DATADIR目录中。

14.1.2 查看错误日志

错误日志是以文本文件的形式存储的，直接使用普通文本工具就可以打开查看。Windows操作系统可以使用文本编辑器查看。linux操作系统下，可以使用vi工具或者使用gedit工具来查看。
通过show命令可以查看错误日志文件所在目录及文件名信息。
show variables like ‘log_error’；

14.1.3 删除错误日志

管理员可以删除很久之前的错误日志，这样可以保证MySQL服务器上的硬盘空间。通过show命令查看错误文件所在位置，确认可以删除错误日志后可以直接删除文件。在MySQL数据库中，可以使用mysqladmin命令来开启新的错误日志，语法如下：
flush logs；
执行该命令后，系统会自动创建一个新的错误日志文件。

14.2 操作查询日志

查询日志记录了用户的所有操作，包括对数据库的增、删、查、改等信息，在并发操作多的环境下会产生大量的信息，从而导致不必要的磁盘IO，会影响MySQL的性能。如不是为了调试数据库的目的建议不要开启查询日志。

14.2.1 启动查询日志

默认情况下查询日志是关闭的。如果需要打开查询日志，可以通过修改my.ini文件来启动查询日志。在[mysqld]组下加入log选项，格式如下:
[mysqld]
log[=path/[filename]]
其中，path用来指定查询日志存放的位置；[filename]用来指定查询日志文件名，默认主机名（hostname）作为文件名，存放在DATADIR目录中。
也可以通过命令行来启动查询日志：
set global general_log=on；
或者使用如下命令：
set global general_log=1；
也可通过命令行来设置查询日志存放的位置。
set global general_log_file=‘e:/mysql-5.6.22-winx64/log/general_log.log’；
然后通过show 命令查看通用查询日志，语法如下：
show variables like ‘general%’；

14.2.2 查看查询日志

用户的所有操作都会记录到查询日志中。该日志是以文本文件的形式存储的。Windows操作系统可以使用文本编辑器查看。linux操作系统下，可以通过vi工具或者gedit工具来查看。首相通过show命令找到查询日志文件所在位置及文件名，打开日志文件，可以看到。

14.2.3 删除查询日志

通过show命令查看查询日志文件所在位置，确认可以删除通用查询日志后可以直接删除文件。在MySQL数据库中，可以使用mysqladmin命令来开启新的查询日志。语法如下：
flush logs；
执行该命令后，系统会自动创建一个新的查询日志文件。
注意：更新记录中没有记录错误的语句，这对于恢复被破坏的数据库内容非常有意义。

14.3 操作二进制日志

MySQL数据的二进制日志是用来记录所有用户对数据库的操作。当数据库发生意外时，可以通过此文件查看在一定时间段内用户所做的操作，结合数据库备份技术，即可再现用户操作，使数据库恢复。
提示：进制日志开启后，所有对数据库操作的记录均会被记录到此文件，所以当长时间开启之后，日志文件会变得很大，占用磁盘空间。

14.3.1 启动二进制日志

二进制日志记录了所有对数据库数据的修改操作，所以MySQL数据库默认情况下是不开启二进制日志文件的，可通过查看命令查看：
show variables like ‘log_bin’；
根据实际需要，可以通过以下步骤手动启用二进制日志。
（1）打开MySQL安装目录\my.ini（Windows系统是my.ini文件，linux是my.cnf文件）。
（2）找到[mysqld]这个标签，在此标签下面一行，添加如下语句：
log_bin=[filename]
再该语句中，log_bin说明要开启二进制日志文件；filename是二进制日志文件的名字。如果没有指定[filename]，默认为主机名后面跟_bin作为文件名，默认存放在DATADRI目录中。
如果只对制定数据库生成二进制日志，则需要添加如下语句：
Binlog_do_db=db_name（数据库名称）
如果不对制定数据库生成二进制日志，则需要添加如下语句：
Binlog_ignore_db=db_name（数据库名称）
（3）重启MySQL服务。可以在MySQL安装目录\data文件夹下看到“binary_log.数字编号”文件，如：binary_log.000001。以后每重启一次MySQL服务，都会重新生成二进制日志文件，文件名中的数字编号依次递增。
如果日志长度超过了max_binlog_size的上限（默认是 1G）也会创建一个新的日志文件，通过SHOW 命令查看二进制日志的上限。
show variables like ‘max_binlog_size’；
使用flush logs（MySQL命令符）或者执行mysqladmin -u -p flush-logs（Windows命令提示符）也会创建一个新的日志文件。
完成以上操作后再次查看二进制日志相关信息如下：
show variables like ‘log_bin%’\G
技巧：重启MySQL服务操作：首先退出mysql，输入“net stop mysql”；提示服务停止后，再次输入“net start mysql”即可重启服务。

14.3.2 查看二进制日志

可以通过以下命令查询二进制日志：
show binary logs；
由于binary是以binary方式存取，不能直接在Windows下查看，可以通过MySQL提供的mysqlbinlog工具查看。
也可以通过show命令查看对数据库的操作事件。
show binlog events in ‘binary_log.000004’\G
通过二进制日志文件的内容可以看出对数据库操作记录，对于管理员对数据库进行管理或数据恢复提供了依据。
通过mysqlbinlog工具查看二进制日志的所有内容：
mysqlbinlog –no-defaults e:\mysql-5.6.22-winx64\data\binary_log.0000013
在二进制日志文件中，对数据库的DML操作和DDL都记录到了binlog中，而SELECT并没有记录。如果用户想记录SELECT和SHOW操作，就只能使用查询日志，而不是二进制日志。此外，二进制日志还包括了执行数据更改操作的时间等其他额外信息。
总之，开启二进制日志可以实现以下几个功能：
（1）恢复：某些数据的恢复需要二进制日志。例如：在一个数据库全备文件恢复后，用户可以通过二进制日志进行point_in_time的恢复。
（2）复制：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库与一台MySQL数据库进行实时同步。
（3）审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

14.3.3 删除二进制日志

开启二进制日志会对数据库整体性能有所影响，但是性能的损失十分有限。根据MySQL官方手册中的测试表明，开启二进制日志会使性能下降1%。但考虑到可以使用复制和point_in_time的恢复，这些性能的损失是可以被接受的。对于早期的二进制日志，在确认无用的情况下，为了节约磁盘空间，可以进行删除。下面介绍几种删除二进制日志的方法：
用RESET MASTER命令删除所有日志，新日志重新从000001开始编号；
用purge master logs to ‘filename.**’命令可以删除指定编号前的所有日志；
用purge master logs to before ‘YYYY-MM-DD HH21:MI:SS’命令可以删除‘YYYY-MM-DD HH24:MI:SS’之前的产生的所有日志。

14.3.4 使用二进制日志还原数据库

在数据库出现异常情况或者数据丢失时，可以使用二进制日志恢复数据。通过show命令查看二进制日志文件的路径和文件名，然后通过mysqlbinlog工具从指定的时间点开始直到现在，或者一个指定的时间点的日志中恢复数据。
mysqlbinlog恢复数据的语法如下：
mysqlbinlog [option] filename | mysql -uuser -ppass
其中，Option是可选项，其参数为：–start-date（起始时间点）、–stop-date（结束时间点）、–start-position（开始位置）和–stop-position（结束位置）；filename是日志文件名。

1. 指定恢复时间
   对于MySQL，可以在mysqlbinlog语句中通过 –start-date 和–stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00（今天是2005年4月20日），执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入如下语句：
   mysqlbinlog –stop-date=“2005-04-20 9:59:59”/var/log/mysql/bin.123456 | mysql -u root -pmypwd
   其中，stop-date稍早于重大删除时间。该命令将恢复截止到在–stop-date选项中以DATETIME格式给出的日期和时间的所有数据。这样，系统会自动执行从二进制日志有记录开始截止到2015-04-20 9:59:59，用户所做过的所有操作。与–stop-date对应的，还有–start-date属性，可以设定执行记录的开始时间。也可以两个属性都设置。
2. 指定恢复位置
   也可以不指定日期和时间，而使用mysqlbinlog的选项–start-position和–stop-position来指定日志位置。它们的作用与起止日期选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法如下：
   mysqlbinlog –start-date=“2005-04-20 9:55：00”–stop-date=“2005-04-20 10:05:00”\
   /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
   该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。可以用文本编辑器打开该文件，寻找不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置信号恢复了以前的备份文件后，应在命令行输入下面的内容。
   mysqlbinlog –stop-position=“368312” /var/log/mysql/bin.123456 | mysql -u root -pmypwd
   mysqlbinlog –start-position=“368315” /var/log/mysql/bin.123456 | mysql -u root -pmypwd
   上面第一行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反映事务执行的原时间。

14.4 操作慢查询日志

优化MySQL最重要的一部分工作就是先确定“有问题”的查询语句。只有先找出这些查询较慢的SQL查询，才可以进一步分析原因并且优化它。慢查询日志就记录了执行时间超过了特定时长的查询，即记录所有执行时间超过最大SQL执行时间（long_query_time）或未使用索引的语句。

14.4.1 起动慢查询日志

默认状态下，慢查询日志是关闭的。可以通过配置文件my.ini或者my.cnf来启用。配制方法如下：
[mysqld]
slow_query_log=[filename]
slow_launch_time=n
其中，[filename]用来指定慢查询日志存放的位置，如没有指定，默认为hostname_slow.log作为文件名，默认存放在DATADIR目录中；slow_launch_time=n是设置时间的，如果某条查询语句的查询时间超过了这个值，则该查询过程将被记录到慢查询日志文件中。默认时间为10s。同样慢查询日志也可以通过命令行来设置，语句如下：
set global slow_query_log=on
set global slow_launch_time=1
通过show命令查看设置情况：
show variables like ‘slow_%’;

14.4.2 查看慢查询日志

根据上面对满日志的设置，可以看到在默认文件夹下生成了一个salonshi_PC_slow.log的慢日志文件，该文件可以使用记事本打开。
该日志记录了慢查询日志发生的时间、连接用户、IP、执行时间、锁定时间、最终发送行数、总计扫描数、SQL语句等相关信息。
对于查询时间大于所设置的Log_query_time时，可通过mysqldumpslow工具进行汇总、排序，以便找出耗时最高、请求次数最多的慢查询日志。
除了MySQL自带的mysqldumpslow工具外，也有很多第三方优秀的慢日志分析工具，如：mysqlsla、myprofi等。

14.4.3删除慢查询日志

如果遇到慢查询日志文件过大，需要回收空间加以利用，或者其他原因需要删除慢查询日志文件，可以直接进行删除。也可以通过以下命令将慢日志文件重置。
set global slow_query_log=0
删除后需要重新生成一个新的慢日志文件，可以通过以下命令生成。
set global slow_query_log=1
或者使用如下的windows命令。
C:>mysqladmin -uroot -p flush_logs
Enter password：*

14.5 高手点拨

logrotate程序是一个日志文件管理工具。用来把旧的日志文件的内容转存到新的文件中，并清空或者是删除旧的日志文件。MySQL日志可以利用logrotate工具进行管理。