mysql idb恢复删除之前的数据_恢复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文件，后面恢复时会用到)

(root@'mgr01':mysql3306.sock)[testdb]>show master status\G

*************************** 1. row ***************************

File: mysql-bin.000005

Position: 15211

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: bde7b592-b966-11e9-8c64-000c294f3e61:1-10445

1 row in set (0.00 sec)

2.3最好是flush logs下，让接下来的sql写入到新的binlog文件

2.4定位drop 表语句所在binglog文件的位置点：

[root@mgr01 binlog]# mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/binlog/mysql-bin.000005|grep -i -C 15 drop

### @1=10422

### @2='tomcat'

### @3='xiaohuahua'

### @4='2019-08-08 14:22:18'

# at 14987

#190808 14:22:18 server id 63306 end_log_pos 15018 CRC32 0x873943dd Xid = 20695

COMMIT/*!*/;

#at15018###################################

#190808 14:22:19 server id 63306 end_log_pos 15083 CRC32 0xcc8773ce GTID last_committed=34 sequence_number=35 rbr_only=no

SET @@SESSION.GTID_NEXT= 'bde7b592-b966-11e9-8c64-000c294f3e61:10445'/*!*/;

#at 15083

#190808 14:22:19 server id 63306 end_log_pos 15211 CRC32 0x8d445019 Query thread_id=7213 exec_time=0 error_code=0

use `testdb`/*!*/;

SET TIMESTAMP=1565245339/*!*/;

SET @@session.sql_auto_is_null=0/*!*/;

DROP TABLE `test1_event` /* generated by server */

/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

#End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

定位到15018 这个位置点就是利用binlog的文件恢复时的结束点

2.5恢复xtrabackup备份到 mysql3308 实例上：

提示：mysql 3308实例要开启Gtid的

恢复备份的命令：

innobackupex --apply-log /data/backup/db_3306_20190808/

innobackupex --defaults-file=/data/mysql/mysql3308/my3308.cnf --copy-back /data/backup/db_3306_20190808/

给数据目录data mysql 权限：

chown -R mysql.mysql /data/mysql/mysql3308/data/

启动mysql3308 实例：

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &

查看到恢复到3308实例的数据，但是在备份3306库到删除表test1_event 这时间段还存在很多缺失的数据未找回，要从增量的binlog文件中找回：

| 10273 | tomcat | xiaohuahua | 2019-08-08 14:17:16 |

| 10274 | tomcat | xiaohuahua | 2019-08-08 14:17:18 |

+-------+----------+------------+---------------------+

(root@'mgr01':mysql3308.sock)[testdb]>select count(*) from test1_event;

+----------+

| count(*) |

+----------+

| 10273 |

+----------+

1 row in set (0.00 sec)

xtrabackup 备份是开启Gtid的，所以下面使用mysqlbinlog命令 进行增量binglog文件恢复数据时，要添加参数--skip-gtids 忽略掉binlog文件的中的Gtid信息，要是不加参数--skip-gtids 进行恢复的话，导致数据恢复不到3308库

正确的恢复命令如下：

mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000001 --skip-gtids |mysql -f --binary-mode -S /tmp/mysql3308.sock

mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000002 --skip-gtids |mysql -f --binary-mode -S /tmp/mysql3308.sock

mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000003 --skip-gtids |mysql -f --binary-mode -S /tmp/mysql3308.sock

mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000004 --skip-gtids |mysql -f --binary-mode -S /tmp/mysql3308.sock

mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000005 --stop-position="15018" --skip-gtids |mysql -f --binary-mode -S /tmp/mysql3308.sock

参数说明：

--skip-gtids 忽略binlog文件中的Gtid的信息

--binary-mode 主要是为了解决中文乱码或者是特殊字符串乱码的问题

-f 强制恢复，忽略报错

执行完以上命令，数据就恢复到删除表test1_event之前的了

下面的恢复命令会导致数据恢复不到mysql 3308 实例上:(原因是my3308实例开启了Gtid参数导致的)

mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000001 |mysql -f --binary-mode -S /tmp/mysql3308.sock

mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000002 |mysql -f --binary-mode -S /tmp/mysql3308.sock

mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000003 |mysql -f --binary-mode -S /tmp/mysql3308.sock

mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000004 |mysql -f --binary-mode -S /tmp/mysql3308.sock

mysqlbinlog --stop-position="15018" /data/mysql/mysql3306/binlog/mysql-bin.000005|mysql -f --binary-mode -S /tmp/mysql3308.sock

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信息的

[root@mgr01 backup]# mysqlbinlog --stop-position="15018" /data/mysql/mysql3306/binlog/mysql-bin.000005|mysql -f --binary-mode -S /tmp/mysql3308.sock

ERROR 1781 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.

ERROR 1781 (HY000) at line 50: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.

ERROR 1781 (HY000) at line 74: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.

ERROR 1781 (HY000) at line 98: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.

当然也可以采用下面的方式来恢复，同样3308的实例的binglog文件是不记录Gtid信息的，这种方法在恢复的过程中可能会出现报错，不能完全保证数据的完整性和正确性，所以生产上最好不要使用这种方法来恢复找回数据

[root@mgr01 backup]# mysqlbinlog --skip-gtids /data/mysql/mysql3306/binlog/mysql-bin.000001|mysql -f --binary-mode -S /tmp/mysql3308.sock

ERROR 1050 (42S01) at line 27: Table 'test1_event' already exists

ERROR 1062 (23000) at line 92: Duplicate entry '1' for key 'PRIMARY'

ERROR 1537 (HY000) at line 132: Event 'e_test' already exists

mysqlbinlog --skip-gtids /data/mysql/mysql3306/binlog/mysql-bin.000002|mysql -f --binary-mode -S /tmp/mysql3308.sock

mysqlbinlog --skip-gtids /data/mysql/mysql3306/binlog/mysql-bin.000003|mysql -f --binary-mode -S /tmp/mysql3308.sock

mysqlbinlog --skip-gtids /data/mysql/mysql3306/binlog/mysql-bin.000004|mysql -f --binary-mode -S /tmp/mysql3308.sock

mysqlbinlog --skip-gtids --stop-position="15018" /data/mysql/mysql3306/binlog/mysql-bin.000005|mysql -f --binary-mode -S /tmp/mysql3308.sock

友情提示：生产上还是最好开启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 存在很大风险点