mysql binlog数据恢复.md

MySQL binlog 数据恢复

@author Zjmainstay
@website http://www.zjmainstay.cn

一、启动bin-log日志

在my.cnf [mysqld]节点添加：

log-bin=mysql-bin
binlog_format=mixed

二、查看bin-log是否启动成功

show variables like '%log_bin%';

三、查看mysql-bin.000001文件所在位置

show variables like '%datadir%';

四、基于sql查询查看bin-log文件中的内容

show binlog events in 'mysql_bin.000001'\G

五、基于mysqlbinlog命令查看bin-log文件的内容

mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001|more
可以指定参数：--start-position=0   --stop-position=xxx

六、日志显示参考

请留意日志中额外添加的注释，如（作者注：开始的位置）

#161118 21:58:58 server id 1  end_log_pos 1974 CRC32 0x733660ed         Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1479477538/*!*/;
insert into `tb_abc` (`id`, `name`) values ('1', 'abc')
/*!*/;
# at 1974（作者注：开始的位置）
# （作者注：需要恢复的内容Begin）
#161118 21:58:06（开始的时间） server id 1  end_log_pos 2161 CRC32 0xcd3cd3d2         Query   thread_id=7     exec_time=784   error_code=0
SET TIMESTAMP=1479477486/*!*/;
CREATE TABLE `binlog-find` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT) DEFAULT CHARACTER SET `utf8`
/*!*/;
# at 2161
#161118 21:58:18 server id 1  end_log_pos 2307 CRC32 0x1e28b93d         Query   thread_id=7     exec_time=772   error_code=0
SET TIMESTAMP=1479477498/*!*/;
ALTER TABLE `binlog-find` ADD `name` INT
 NULL
 DEFAULT NULL
 AFTER `id`
/*!*/;
# at 2307
#161118 21:58:23 server id 1  end_log_pos 2386 CRC32 0x34669ad7         Query   thread_id=7     exec_time=767   error_code=0
SET TIMESTAMP=1479477503/*!*/;
BEGIN
/*!*/;
# at 2386
#161118 21:58:23 server id 1  end_log_pos 2518 CRC32 0xf4f4adb4         Query   thread_id=7     exec_time=767   error_code=0
SET TIMESTAMP=1479477503/*!*/;
INSERT INTO `binlog-find` (`id`, `name`) VALUES ('1', '2')
/*!*/;
# at 2518
#161118 21:58:23 server id 1  end_log_pos 2549 CRC32 0x54679b22         Xid = 2426
COMMIT/*!*/;
# at 2549（作者注：结束的位置）
# （作者注：需要恢复的内容End）
#161118 21:58:27（作者注：结束的时间） server id 1  end_log_pos 2673 CRC32 0x35b852fa         Query   thread_id=7     exec_time=860   error_code=0
SET TIMESTAMP=1479477507/*!*/;
DROP TABLE `binlog-find` /* generated by server */
/*!*/;
# at 2673

七、数据恢复

##导入sql结果到sql文件
sudo mysqlbinlog --no-defaults --start-position="1974" --stop-position="2549" /var/lib/mysql/mysql-bin.000001 >> res.sql
##通过管道方式直接运行（基于位置）
sudo mysqlbinlog --no-defaults --start-position="1974" --stop-position="2549" /var/lib/mysql/mysql-bin.000001 |mysql -uzjmainstay --binary-mode
--start-position 为需要恢复开始语句上一条语句运行结束位置
--stop-position 为需要恢复结束语句运行结束位置（删表上一条语句）
或
##通过管道方式直接运行（基于时间）
sudo mysqlbinlog --no-defaults --start-datetime="2016-11-18 21:58:06" --stop-datetime="2016-11-18 21:58:27" /var/lib/mysql/mysql-bin.000001 |mysql -uzjmainstay --binary-mode
--start-datetime 为需要恢复开始语句运行时间
--stop-datetime 为需要恢复结束语句下一条语句运行时间（删表语句）

八、数据恢复原理

1. 恢复上一次备份；
2. 找到上一次数据备份对应的binlog文件及其position，从该位置恢复到删表操作前的位置，也就是说，恢复文件并不一定是mysql-bin.000001；
3. 如果从上次备份到现在已产生多个binlog，则要按照binlog产生的顺序从小到大进行恢复；

如果没有上一次备份，使用类似mysqlbinlog /var/lib/mysql/mysql-bin.000001 --start-position=0 --stop-position=9999999999999 | mysql -uroot -pxxxx 从第一个bin-log日志文件，逐步恢复到删表操作前的位置

--stop-position=9999999999999设置成接近无限大是为了无视文件的结束位置，方便操作