Binlog数据恢复

精选原创

wx5e6c776d2a88f 2024-01-02 10:23:20 博主文章分类：MySQL ©著作权

文章标签 mysql 数据库恢复数据 文章分类 MySQL 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者wx5e6c776d2a88f的原创作品，请联系作者获取转载授权，否则将追究法律责任

公司生产环境库被我不小心删了，废了九牛二虎之力，终于恢复

记录一下方法

第一种方法

注意：在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。

一、原理

binlog日志中记录了数据库的增删改（update,insert,delete）操作

通过重放这些操作可以达到恢复数据库的目的

注意：前提是得有一次完整备份。

[mysqld]区块添加

　　　　log-bin=mysql-bin(也可指定二进制日志生成的路径，如：log-bin=/opt/Data/mysql-bin) 　　　　server-id=1

　　　　binlog_format=MIXED(加入此参数才能记录到insert语句)

二、操作

1、首先把数据库恢复到上一次完整备份状态。

2、查看数据库是否开启binlog

show variables like 'log_%';

Binlog数据恢复_mysql

log_bin为on即为开启，off即为关闭

3、进入mysql的data目录查找binlog二进制文件

或者使用show binary logs;查看binlog日志：

Binlog数据恢复_恢复数据_02

4、使用flush logs;刷新日志记录，即mysql会重新打印一个binlog二进制文件，这样有利于恢复数据。

比如删除全库的语句记录在binlog.00001中，刷新后，会立马结束binlog.00001的记录，并开启一个binlog.00002。这样重放操作只用执行到binlog.00001。

5、查看binlog日志中记录的操作记录

mysqlbinlog --no-defaults binlog.000003 | grep -i DROP -A3 -B4 这样是查看全部库的操作

mysqlbinlog --no-defaults -d database binlog.000003 | grep -i DROP -A3 -B4 加上-d可以指定数据库

6、查看binlog日志后可以看到删除全库的操作记录，找到时间戳

Binlog数据恢复_恢复数据_03

7、开始重放操作，找到上次全库备份后的所有binlog文件

使用mysqlbinlog命令开始重放。

注意：重放之前对目标库以外的库进行备份，防止重放操作影响到别的库

mysqlbinlog --no-defaults -d database binlog.000003 | mysql -uroot -p

-d会指定数据库

一直重放binlog日志直到删除全库的操作的那次binlog

mysqlbinlog --no-defaults -d database binlog.000003 --start-datetime='2019-07-12 19:50:36' --stop-datetime='2019-07-13 19:23:40' | mysql -uroot -p

这里的时间即时间戳查出的时间。

注意注意：

①在重放操作时会有很多报错，需要在my.cnf中指定跳过。这里只指定了三个，根据真实情况指定

my.cnf中的写法：

slave_skip_errors=1062,1053，1032

②在重放操作中如果有插入动作，可能会有外键约束

删除全表的外键，即可。后续需补充回来

③mysqlbinlog --no-defaults 要这样使用mysqlbinlog命令，否则无法使用

扩展：

常用参数选项解释：

　　　　　　--start-positinotallow=875 起始pos点　　　　　　--stop-position=954 结束pos点　　　　　　--start-datetime="2016-9-25 22:01:08" 起始时间点　　　　　　--stop-datetime="2019-9-25 22:09:46" 结束时间点　　　　　　--database=ops指定只恢复ops数据库(一台主机上往往有多个数据库，只限本地log日志)

指定pos结束点恢复（部分恢复）：

　　　　　　/application/mysql3306/bin/mysqlbinlog --stop-position=3064 --database=ops /application/mysql3306/mysql_data/mysql-bin.000002 | /application/mysql3307/bin/mysql -uroot -S /application/mysql3307/logs/mysql.sock -p123456 -v(因为加了--database=ops因此不会恢复二进制日志中关于ops1库的相应操作，若也需要恢复ops1库的相应操作，则再加上--database=ops1即可)

指定pos点区间恢复（部分恢复）

　　　　　　在f环节我们已经恢复到了删库之前的时刻，在删库后我们还做了创建ops2库并创建了member表和增加了数据的操作，此时我们要跳过删库并且恢复到创建ops2库和创建member表的时刻可以采用区间pos点恢复：/application/mysql3306/bin/mysqlbinlog --start-position=3153 --stop-position=3880 /application/mysql3306/mysql_data/mysql-bin.000002 | /application/mysql3307/bin/mysql -uroot -S /application/mysql3307/logs/mysql.sock -p123456 -v