mysql基于binlog日志恢复数据

恢复思路
首先mysql数据库必须开启binlog日志功能，且有做全备和增量备份，如每周一次全备，每天一次增量备份。
先根据全量备份，将数据库中的数据恢复到临近的某一时间点，再找出该时间点到发生事故时段的所有增量备份的binlog文件。

1、开启binlog日志功能

参考一下mysql配置

port=3306
default-time_zone='+8:00'
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir="D:/software_install/mysql-5.6.10-winx64/"
datadir="D:/software_install/mysql-5.6.10-winx64/data/"
log-error="D:/software_install/mysql-5.6.10-winx64/log/error.log"

#binlog日志格式，mysql默认采用statement，建议使用mixed
binlog_format=mixed
#binlog日志文件
log-bin="D:/software_install/mysql-5.6.10-winx64/binlog/mysql-bin.log"

#binlog过期清理时间   
expire_logs_days=7
#binlog每个日志文件大小
max_binlog_size=100m
#binlog缓存大小
binlog_cache_size=4m
#最大binlog缓存大小
max_binlog_cache_size=512m
server-id=1

2、全备过程

#文件备份路径最好写绝对路径,如果只写文件名称,生成的文件在打开cmd窗口的位置
mysqldump -u 用户 -p 数据库 [表1 表2 …… 表N] > 备份文件路径

#实际上还要加一些别的参数控制
mysqldump -u root -p --single-transaction --flush-logs --master-data=2 liberty > backup_test.sql
--single-transaction,开启单一事务,整个备份过程只有一个事务,不适用该参数时,会频繁的开启事务,实现点对点的备份恢复.因为-single-transaction选项备份时涉及到的是select语句，所以dml语句是被允许的，ddl不被允许直到回滚保存点撤销select 语句
--flush-logs 为结束当前日志，生成新日志文件
--master-data=2 选项将会在输出SQL中记录下完全备份后新日志文件的名称,用于日后恢复时参考
liberty  数据库名称

若要恢复数据，执行sql脚本即可恢复到全备数据。然后要关闭数据库的binlog日志功能，再重启。接着进行增量恢复

3、增量恢复

执行完全量备份文件后，再依次执行binlog日志

# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p
# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p 
# mysqlbinlog --no-defaults mysql-bin.000003 | mysql -u root -p
# mysqlbinlog --no-defaults mysql-bin.000004 | mysql -u root -p

最后一个发生故障的binlog文件可以有两种方式来恢复。

3.1 基于位置的恢复:跳过误操作的位置

找到误操作所在的binlog文件

 show binlog events in 'mysql-bin.000002';

得到的结果：

+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                                                                                                                                                                                                                            |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000002 |    4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4                                                                                                                                                                                                                                                                                                                                           |
| mysql-bin.000002 |  120 | Query       |         1 |         239 | use `shop`; DROP TABLE `studet` /* generated by server */                                                                                                                                                                                                                                                                                                                       |
| mysql-bin.000002 |  239 | Query       |         1 |         369 | use `shop`; DROP TABLE IF EXISTS `student` /* generated by server */                                                                                                                                                                                                                                                                                                            |
| mysql-bin.000002 |  369 | Query       |         1 |         644 | use `shop`; CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1                                                                                                                                                           |
| mysql-bin.000002 |  644 | Query       |         1 |         764 | use `shop`; /*!40000 ALTER TABLE `student` DISABLE KEYS */                                                                                                                                                                                                                                                                                                                      |
| mysql-bin.000002 |  764 | Query       |         1 |         843 | BEGIN                                                                                                                                                                                                                                                                                                                                                                           |
| mysql-bin.000002 |  843 | Query       |         1 |        1272 | use `shop`; INSERT INTO `student` VALUES (1,'寮犱笁'),(2,'鏉庡洓'),(3,'鐜嬩簲'),(4,'璧垫煶'),(5,'璧垫煶'),(6,'璧垫煶'),(7,'璧垫煶'),(8,'璧垫煶'),(9,'璧垫煶'),(10,'璧垫煶'),(11,'璧垫煶'),(12,'璧垫煶'),(13,'璧垫煶'),(14,'璧垫煶'),(15,'璧垫煶'),(16,'璧垫煶'),(17,'璧垫煶'),(18,'璧垫煶'),(19,'璧 垫煶'),(20,'璧垫煶'),(21,'璧垫煶'),(22,'璧垫煶'),(23,'璧垫煶'),(24,'璧垫煶') |
| mysql-bin.000002 | 1272 | Xid         |         1 |        1303 | COMMIT /* xid=571 */                                                                                                                                                                                                                                                                                                                                                            |
| mysql-bin.000002 | 1303 | Query       |         1 |        1422 | use `shop`; /*!40000 ALTER TABLE `student` ENABLE KEYS */                                                                                                                                                                                                                                                                                                                       |
| mysql-bin.000002 | 1422 | Query       |         1 |        1501 | BEGIN                                                                                                                                                                                                                                                                                                                                                                           |
| mysql-bin.000002 | 1501 | Intvar      |         1 |        1533 | INSERT_ID=25                                                                                                                                                                                                                                                                                                                                                                    |
| mysql-bin.000002 | 1533 | Query       |         1 |        1649 | use `test`; INSERT into student(name) values('鐢颁竷')                                                                                                                                                                                                                                                                                                                          |
| mysql-bin.000002 | 1649 | Xid         |         1 |        1680 | COMMIT /* xid=612 */                                                                                                                                                                                                                                                                                                                                                            |
| mysql-bin.000002 | 1680 | Query       |         1 |        1759 | BEGIN                                                                                                                                                                                                                                                                                                                                                                           |
| mysql-bin.000002 | 1759 | Intvar      |         1 |        1791 | INSERT_ID=26                                                                                                                                                                                                                                                                                                                                                                    |
| mysql-bin.000002 | 1791 | Query       |         1 |        1907 | use `test`; INSERT into student(name) values('鐜嬪叓')                                                                                                                                                                                                                                                                                                                          |
| mysql-bin.000002 | 1907 | Xid         |         1 |        1938 | COMMIT /* xid=619 */                                                                                                                                                                                                                                                                                                                                                            |
| mysql-bin.000002 | 1938 | Query       |         1 |        2017 | BEGIN                                                                                                                                                                                                                                                                                                                                                                           |
| mysql-bin.000002 | 2017 | Query       |         1 |        2130 | use `test`; delete from student where name='璧垫煶'                                                                                                                                                                                                                                                                                                                             |
| mysql-bin.000002 | 2130 | Xid         |         1 |        2161 | COMMIT /* xid=626 */                                                                                                                                                                                                                                                                                                                                                            |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
20 rows in set (0.00 sec)

故障的sql语句为最后一条delete语句，所以最终的位置是1938。
执行最后一条恢复语句即可恢复到故障之前的所有数据。

mysqlbinlog --start-position=4 --stop-position=1938 D:\software_install\mysql-5.6.10-winx64\binlog\mysql-bin.000002 | mysql -u root -p

3.2 基于时间的恢复:跳过误操作的时间

# mysqlbinlog --no-defaults --stop-datetime='2018-07-03 18:19:49' /usr/local/mysql/data/mysql-bin.00000x | mysql -u root -p
# mysqlbinlog --no-defaults --start-datetime='2018-07-03 18:22:21' /usr/local/mysql/data/mysql-bin.00000x | mysql -u root -p

测试了没有成功！

注意点
mysql 5.6版本以上新加了gtid 功能，gtid开启之后，默认不执行已经执行过的事务。直接还原是还原不了的，所以将二进制文件倒成sql文件时或二进制直接还原时：要加–skip-gtids参数或是要重新建个实例。
其实就是 将二进制文件倒成sql文件例： mysqlbinlog --skip-gtids mysql-bin-0000001 > test.sql （gtid开启之后，默认不执行已经执行过的事务所以要加–skip-gtids参数）
更详细的可以参考：

https://www.cnblogs.com/houchaoying/p/9092814.html