在MySQL中,Binlog的职能类似于Oracle的归档日志。
它会存放对数据发生或者潜在发生变更的SQL语句,并以二进制形式保存。

通过BINLOG,我们可以:
1. 查看数据库的变更历史。
2. 对数据库执行:增量备份与恢复。
3. MySQL的复制:主主复制、主从复制

下面的篇章里,详细展开:

查看当前数据库的binlog状态:

可以看到,我的库,当前是没有开启binlog的。

开启binlog。

创建binlog的存放路径:

编辑配置文件:

如上,配置文件(/etc/my.cnf)中,配置“log-bin”,即可开启binlog,如果要禁用binlog,则注释该参数的设置即可达到目的。

重启MySQL,应用配置变更:

查看这时候的binlog状态:

可以看到,log_bin,已经启用。

如上的方式,需要重启才可以将binlog从禁用切换为启用。
单如果MySQL的版本是5.6或者以上,则不需要这么麻烦:
启用与禁用:
set @global.log_bin= 1 / 0
1,on
2,off
set @global.binlog_size=37268
单位:Bytes。

暂停binlog:
当前状态:

暂停:

恢复:

二进制日志文件的大小(单位: bytes):

以上的参数修改,可以写到/etc/my.cnf中,这样下次启动MySQL的时候,就可以应用了。

二进制日志的切换:
如果二进制日志写满了或者MySQL重启了,二进制日志会自动进行切换。

这个操作也可以手动执行:
当前。
库:

存放binlog的文件系统:

在MySQL中执行手动二进制日志切换:
库:

手动切换后,再次查看状态。
库:

文件系统:

可以看到,文件系统中,新增了二进制日志文件:mysql-bin.000005。

关于binlog的其他参数的意义:

binlog_cache_size,二进制日志缓存大小
sync_binlog,默认为:0,每隔N秒,将缓存中的二进制日志记录写回磁盘。

查看binlog的内容。

为了试验,先创建一些测试数据:

测试样例创建好了以后,手动刷一下log:

文件系统的变化:
刷之前:

刷之后:

新增的二进制日志文件为:mysql-bin.000006。
但我变更操作是在:Wed Aug 24 02:10:45 EDT 2016(该时间是在操作前查询的。上面,刷新前的命令shell中可以看到)。
在这个时间之后,变动的二进制日志包括:
1. mysql-bin.000005
2. mysql-bin.000006
刚刚的新的建库与建表的操作,应该位于这两个二进制日志文件中。

从OS层面看看这两个文件:

如上,可以看到:
mysql-bin.000005,存放了我们刚刚建库与建表的操作。

二进制的日志文件,不能够通过文本文件的方式打开,也就是说:vi,这样的工具是无法打开的。
MySQL提供了对二进制日志的查看工具:mysqlbinlog。

使用mysqlbinlog的时候,需要注意:
1. 不要查看正在写入的binlog文件。
2. 不要加参数“–force”强制访问。
3. 如果binlog格式是行模式,可以使用参数:-w。

具体如下:

二进制日志文件的清理。
二进制日志文件会不断的增长,并产生多个文件。
因此,需要定期清理无用的二进制日志文件。

删除方式有三种:
1. OS直接删除
2. reset master
3. purge binary logs before ‘【yyyy-mm-dd hh24:mm:ss,例如:2014-07-09 12:40:26】’

删除方式:reset master

查看文件系统中的变化:
之前:

之后:

PS:binlog在MySQL运行过程中是会不断的动态增长的:
如下,我有一张表:

我会通过:insert into people.user select * from people.user;
模拟大量数据写入MySQL。

执行插入前,查看下当前的binlog的状态:
库:

文件系统:

执行插入:

再次查看binlog的状态:

可以看到binlog:mysql-bin.000002 的大小,从5069,变成了5268。

查看一下其中的内容:

除了通过mysqlbinlog,还可以通过MySQL去查看:
文件系统:

库:

查看正在写入的binlog:

获取binlog文件列表:

MySQL:mysqlbinlog。

根据时间查看binlog:

改变时间间隔,看看:

基于POS值查看也是一样的,语法如下:
mysqlbinlog –start-postion=107 –stop-position=1000 -d 库名 二进制文件

远程查看:
mysqlbinlog -u username -p password -h【主机名 / IP地址】 -P3306 \
–read-from-remote-server –start-datetime=’2013-09-10 23:00:00′ –stop-datetime=’2013-09-10 23:30:00′ mysql-bin.000001 > t.binlog

——————————————————————
MySQL:binlog恢复。

查询当前时间:

查看当前正在写入的binlog文件:

创建测试样例:

插入后,查询一下:

可以看到,查得到数据。

做一次日志切换:

可以看到,正在写入的日志已经切为了:mysql-bin.000005。
然后就可以对mysql-bin.000004,操作了。(最好不要对正在写入的binlog做mysqlbinlog操作。)

恢复数据库:
操作之前,我们查过时间:2016-08-24 07:05:19
恢复到:2016-08-24 06:05:19.

查看:

可以看到:
2016-08-24 07:41:06为止,只有建库 / 建表 / 插入数据(不包含user*格式的数据)
2016-08-24 07:41:06之后,插入数据(包含user*格式的数据)

删除数据库,以便于做恢复:

执行基于时间的binlog恢复:
1. 恢复到:2016-08-24 07:41:06,这时候应该是没有user*格式的数据的。

库的状态:

可以看到,被删掉的库“me”恢复了,数据也恢复了,并且按照期望的,并没有user*的数据。


2. 恢复到:2016-08-24 07:45:06,这时候已经有user*格式的数据了。
[root@localhost binlog]# mysqlbinlog –stop-datetime=’2016-08-24 07:45:6′ mysql-bin.000004 | mysql -uroot
ERROR 1007 (HY000) at line 22: Can’t create database ‘me’; database exists
[root@localhost binlog]#
可以看到出了错,binlog只是很傻的抽sql语句。
导出到文本文件:
[root@localhost binlog]# mysqlbinlog –stop-datetime=’2016-08-24 07:45:6′ mysql-bin.000004 > /script/userlike.sql
[root@localhost binlog]#
做出替换:
[root@localhost binlog]# cat /script/userlike.sql | grep –color create
#160824 5:47:02 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.37-log created 160824 5:47:02
create database me
create table user select * from mysql.user
[root@localhost binlog]#
[root@localhost binlog]# sed -i “s/create/#create/g” /script/userlike.sql
[root@localhost binlog]# cat /script/userlike.sql | grep –color create
#160824 5:47:02 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.37-log #created 160824 5:47:02
#create database me
#create table user select * from mysql.user
[root@localhost binlog]#

这里删除线标注的部分,并不正确。(正确的方式,在下文中描述)

2. 恢复从“2016-08-24 07:41:6”开始变更的增量数据:
先查看下增量数据:

执行恢复:
恢复前:
库:

执行恢复:

恢复后查看:
库:

应用增量之前,全表的数据量:1572864,现在查到的数据量:1572880,差额就是新增的16条数据记录。

至此,binlog的恢复数据,完成。
——————————————————
Done。

1 thought on “MySQL:Binlog备份与恢复”

  1. 至于,增量恢复的时候,众多binlog中到底查看哪个binlog,有这么几点可以参考的确认点:1. 全备的时候的时间信息,需要知道2. MySQL当前正在写的binlog3. MySQL当前所有的binlog日志的时间信息,哪些的最后修改时间是在全备时间之后的?ls -ltr –time-style “+|%Y-%m-%d|%H:%M:%S|” 【路径】

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.