MySQL的日志文件

由于最近碰到一个日志清理的问题，所以就花时间写了这篇文章，严格来说只是学习笔记。 

 

1.通用日志文件

 

默认安装情况下不开启，即不会生成这个文件，需要手工开启，可直接在命令行中设置：set global general_log = ON。即时生效。重启MySQL服务后，会关闭，需重新启动。

 

也可在配置文件my.ini中设置：general_log=on。这样每次启动MySQL服务都会自动启动。

 

这样MySQL就会在data目录下创建一个和你计算机同名的日志文件：计算机名-PC.log。

 

以后你对MySQL做的任何操作，都会被记录到这个日志文件中。

 

2.二进制日志文件

 

默认安装情况下不开启，即不会生成这个文件，需要手工开启，在配置文件my.ini中的[mysqld]后面加入：log_bin=binary_log。这里的binary_log为二进制文件名。

 

这样MySQL就会在data目录下创建如下两个文件：binary_log.000001和binary_log.index。

 

以后对MySQL表做的任何更新，都会被记录里到这个日志文件中。

 

当在data目录下手工删除了某些二进制日志文件时，一定要同时删除binary_log.index里面的对应记录，否则mysql服务将无法启动。

 

可在命令行中通过 show binlog events in ‘binary_log.000001’查看：

也可通过mysqlbinlog命令查看，先在dos下进入data目录然后输入：mysqlbinlog binary_log.000001。

 

mysqlbinlog binary_log.000001 > Jin.txt可输出到txt文件方便查看。

 

重启MySQL后，将会在data目录下重新生成一个新的日志文件，如原来的是binary_log.000001，那么重启后会生成binary_log.000002文件，也即是每次重启后都会按文件后面的数字递增生成一个新文件。

 

flush logs命令可立即生成一个最新的二进制文件，并将后面的更新操作记录其中。但这样貌似会出现一个问题：通用日志（general_log）和慢查询日志（slow_log）将不再更新。不再更新的意思是说，这些操作被存储在内存中了，要等下次重启mysql后，才会重新写入日志中。

     

       所以在使用flush logs命令的时候需要非常小心，因为如果在执行了flush logs命令后，后面产生的通用日志（general_log）和慢查询日志（slow_log）很大的话，那么就会占用很大的内存。这个只是我的猜测，大家可以认证一下。

 

3.慢查询日志文件

 

默认安装情况下不开启，即不会生成这个文件，需要手工开启，在配置文件my.ini中的[mysqld]后面加入：

 

# 慢查询日志文件

log_slow_queries = slow_queries_log

# 记录下查询时间超过10秒的SQL

long_query_time = 10

# 表示记录下没有使用索引的查询SQL                

log-queries-not-using-indexes  

 

slow_queries_log是慢查询日志的文件名，long_query_time为查询时间（单位s），大于此时间的查询都被记录到该日志，log-queries-not-using-indexes表示没有使用索引的查询，该查询也会被记录到该日志文件下。

 

这样MySQL就会在data目录下创建如下文件：slow_queries_log。

 

以后符合上面条件的查询都会被记录到这个日志文件中。

 

4.log_output = table

 

该选项默认为log_output = file，即将日志存入文件中。可设置将日志存入表中，只需在my.ini文件[mysqld]加入log_output = table，即可将通用查询日志（general_log）以及慢查询（log_slow_queries）日志分别存入mysql数据库的general_log和slow_log表中。

 

但好像在这种模式下无法存储二进制文件。

 

5．通过日志文件进行增量备份

 

   在进行增量备份之前，首先我们需要进行一个全备份，可以使用mysqldump命令：

mysqldump -u 用户名 -p --single-transaction [数据库名|--all-databases] > 文件名.sql

 

--single-transaction的意思相当于设置会话的隔离级别为REPEATABLE-READ，即允许重复读，保证数据库的一致性（关于MySQL的隔离级别，可参考我之前写的文章：http://www.iteye.com/topic/1130108）。

举个例子，假如现在有下面几个数据库：

我们要对Jin_Liang这个数据库做一个全备份，那可以在DOS写如下命令：

 

由图可见，备份完成，我们将备份文件存储在了D盘的Jin_Liang_Backup.sql文件下：

 

打开文件可以看到，里面其实都是表的创建命令以及数据的insert语句。

 

假如我们现在清空数据库Jin_Liang里的所有表（模拟崩溃状态）：

 

这时候Jin_Liang里面的数据已经全部被删除了，我们将通过Jin_Liang_Backup.sql来恢复它们，命令如图所示：

 

 

这时候通过mysql命令行客户端查看：

 

 

由图可见，表已全部恢复。

 

上面即是mysql数据库的全备份了，如果想对所有的数据库都进行备份，那么只要在上面的mysqldump命令中选择—all-databases即可。但这有一个缺点，那就是我们必须先手工创建数据库，当然我们在用mysqldump命令的时候可加入--add-drop-database选项，这样就不需要手工创建数据库了。

 

PS: 这里还有一些很有用的命令：

 

只导出一个表：

       mysqldump -u 用户名 -p 数据库名 表名> 文件名.sql

只导出数据库的表结构：

       mysqldump -u 用户名 -p -d --add-drop-table 数据库名 >文件名.sql

      -d 没有数据 --add-drop-table 在每个create语句之前增加一个

 

那下面就开始介绍如何通过二进制日志文件做增量备份。

 

首先先看下data目录下的日志文件有哪些：

 

如图所示，有四个二进制日志文件：binary_log.000001、binary_log.000002、binary_log.000003和binary_log.000004；一个错误日志文件：LilyZhang-PC.err；一个通用日志文件：LilyZhang-PC.log；一个慢查询日志文件：slow_queries.log；还有两个mysql默认的日志文件：ib_logfile0和ib_logfile1。

 

当然我们只需要二进制日志文件。

首先我们需要做flush logs操作，重新生成一个新的二进制日志文件，以便把后面的操作都记录其中：

 

然后查看data目录，可见新生成了binary_log.000005二进制日志文件。

 

这时候我们更新一条记录：

 

查看binary_log.000005文件：

 

由图可见，该更新操作已被记录到binary_log.000005文件中。

 

我们把binary_log.000005导出到txt文件方便查看：

 

打开binary_log.000005.txt文件，如图所示：

 

里面记录了前面的更新操作。所以我们只要根据该操作反向操作即可恢复到之前的状态。

 

从binary_log.000005.txt文件的内容上看，里面记录的其实是重做日志，也即是说当数据库崩溃时，我们可以根据其来重新提交【之前所做的更新】，当然前提是我们做了全备份，并且通过这个全备份文件恢复了备份前的数据，以保证查询条件存在。

 

而增量备份的意思是，我们在做了一次全备份以后，后面我们只要备份那些新更新的数据即可。而二进制日志文件中，保存的即是后面的更新操作，我们只要将其取出重执行一遍，即可恢复到之前的状态。所以，我们在做了一次全备份后，后面的备份我们只要备份二进制日志文件即可。

 

现在我们来模仿通过二进制日志文件如何恢复数据库。首先我们将数据库Jin_Liang删除，以模仿崩溃状态：

 

然后重建数据库：

 

通过全备份恢复：

 

这时候可以看到表已恢复：

 

但查看classes表，此时的数据不是最新的，因为我们之前有做更新：

update classes set class_name = 'Jin' where class_name = 'Jin_Liang'

此时我们就可以通过二进制日志文件来重做这个操作，假设我们前面在做增量备份时，将binary_log.000005备份到了old_logs目录下，那么只要做如下操作即可恢复：

 

此时查看classes表：

 

class_name中Jin_Liang已更新为Jin。

 

评论

1 楼 Youkes 2013-05-23  

感谢分享，支持原创