恢复MySQL数据到误删的表之前的数据
一、演示环境说明:
系统CentOS Linux release 7.2.1511 (Core) X_86 64位最小化安装
mysql版本是官方二进制版本5.7.22-22,mysql采用的是二进制安装,单机上开启2个mysql实例,mysql实例要开启定时器event_scheduler=ON. 而且2个mysql实例都要开启Gtid
xtrabackup 采用的是rpm包安装,版本是version 2.4.13
MySQL备份方式采用每天一次全量备份和binlog增量备份
二、模拟删除库,进行数据恢复演示:
提示:当然此处只是演示,严禁生成环境删库,删表模拟,后果你懂得
故障模拟:
线上误删除一个testdb库下的test1_event表,利用当天的mysql的全量备份+当天生成的mysql的binlog文件来恢复数据到误删的表test1_event之前的数据
恢复方式介绍:
官方推荐采用利用mysql binlog方式恢复,生产实践验证官方的这个方式已经是不严谨的做法了(下面的方法是官方推荐的)
故障恢复过程如下:
2.1接收到误删除之前,第一时间确认大概误操作时间
2.2 登录主库查看当前的binlog位置点(要记住此时的binlog文件,后面恢复时会用到)
2.3最好是flush logs下,让接下来的sql写入到新的binlog文件
2.4定位drop 表语句所在binglog文件的位置点:
定位到15018 这个位置点就是利用binlog的文件恢复时的结束点
2.5恢复xtrabackup备份到 mysql3308 实例上:
提示:mysql 3308实例要开启Gtid的
恢复备份的命令:
给数据目录data mysql 权限:
启动mysql3308 实例:
查看到恢复到3308实例的数据,但是在备份3306库到删除表test1_event 这时间段还存在很多缺失的数据未找回,要从增量的binlog文件中找回:
xtrabackup 备份是开启Gtid的,所以下面使用mysqlbinlog命令 进行增量binglog文件恢复数据时,要添加参数–skip-gtids 忽略掉binlog文件的中的Gtid信息,要是不加参数–skip-gtids 进行恢复的话,导致数据恢复不到3308库
正确的恢复命令如下:
参数说明:
–skip-gtids 忽略binlog文件中的Gtid的信息
–binary-mode 主要是为了解决中文乱码或者是特殊字符串乱码的问题
-f 强制恢复,忽略报错
执行完以上命令,数据就恢复到删除表test1_event之前的了
下面的恢复命令会导致数据恢复不到mysql 3308 实例上:(原因是my3308实例开启了Gtid参数导致的)
2.6如果新的实例mysql3308 启动前从my3308.cnf中关闭掉Gtid参数:
3.
这样的话采用mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.00000* |mysql -f --binary-mode -S /tmp/mysql3308.sock 是可以将数据恢复到mysql 3308实例上的,但是恢复过程中报错。这样恢复到3308的数据,
生成的binlog文件是不记录Gtid信息的
当然也可以采用下面的方式来恢复,同样3308的实例的binglog文件是不记录Gtid信息的,这种方法在恢复的过程中可能会出现报错,不能完全保证数据的完整性和正确性,所以生产上最好不要使用这种方法来恢复找回数据
友情提示:生产上还是最好开启Gtid. 这样在恢复数据和数据库同步,以及解决同步错误是非常方便的
总结:
对于DDL语句像 drop database ,drop tables , drop tables , truncate table 这样的语句就可以采用mysqlbinlog 来恢复。同时也可以采用binlog2sql工具来闪回
对于这样的语句,不管binglog 格式为row 格式,还是statement 格式,还是Mixed 格式,记录的binlog格式都为 statement 格式为row
利用mysqlbinlog 只能支持到database级别的提取
全备+利用mysqlbinlog恢复到某个时间点
利用mysqlbinlog --skip-gtids 存在很大风险点