mysql恢复误删除的表(drop table)

一 试验介绍

备份策略：使用xtrabackup做全量备份

生产服务器：192.168.1.202

恢复服务器：192.168.1.203

想要恢复生产最近被drop的表dba.t4

二 试验步骤

2.1 找到drop table所在binlog

根据用户反馈的故障时间点及相关人员误操作的时间点，确定有可能包含drop 该table的binlog。

这里定位到了三个binlog：

-rw-r-----. 1 mysql mysql   697 Mar 21 11:22 mysql-bin.000044

-rw-r-----. 1 mysql mysql   697 Mar 21 11:23 mysql-bin.000045

-rw-r-----. 1 mysql mysql   831 Mar 21 11:25 mysql-bin.000046

 

#将binlog转换为可读模式

[root@pc1 binlog]# mysqlbinlog -v mysql-bin.000044 > 44.log

[root@pc1 binlog]# mysqlbinlog -v mysql-bin.000045 > 45.log

[root@pc1 binlog]# mysqlbinlog -v mysql-bin.000046 > 46.log

 

#查找包含drop table dba.t4的binlog是哪一个

[root@pc1 binlog]# cat 44.log | grep 'DROP TABLE' | grep 't4'

[root@pc1 binlog]# cat 45.log | grep 'DROP TABLE' | grep 't4'

[root@pc1 binlog]# cat 46.log | grep 'DROP TABLE' | grep 't4'

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

说明在mysql-bin.000046中。

 

#查找其对应position

less 46.log

# at 394

#200321 11:24:05 server id 1  end_log_pos 459 CRC32 0xdbd9f265  GTID    last_committed=0        sequence_number=1       rbr_only=no

SET @@SESSION.GTID_NEXT= '6b3c9713-6916-11ea-87dd-080027040516:6'/*!*/;

# at 459

#200321 11:24:05 server id 1  end_log_pos 572 CRC32 0x53ebd65a  Query   thread_id=2     exec_time=0     error_code=0

use `dba`/*!*/;

SET TIMESTAMP=1584761045/*!*/;

SET @@session.pseudo_thread_id=2/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549152/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

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

SET @@session.collation_database=DEFAULT/*!*/;

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

2.2 在恢复机器上进行恢复

2.2.1 使用备份进行恢复

需要将备份文件拷贝到恢复机器上进行恢复，以免影响线上环境。

#将备份服务器上的备份数据打包并拷贝到恢复服务器上：

tar czf backup.tar.gz 2020-03-21_11-10-52

scp backup.tar.gz 192.168.1.203:/download/bak/xtrbak/

 

#在恢复机器上执行prepare：

tar xf backup.tar.gz

innobackupex --apply-log /download/bak/xtrbak/2020-03-21_11-10-52

如果最后输出completed ok，则表明数据达到一致状态。

 

#在恢复机器上进行恢复

service mysqld stop

将数据目录设置为空：

cd /data/server/mysql_3307/

mv data data_bak

在恢复机器上恢复数据：

innobackupex --copy-back /download/bak/xtrbak/2020-03-21_11-10-52

chown -R mysql:mysql data

此时，数据恢复到了备份时刻的状态。

service mysqld start

2.2.2 利用备份之后的binlog继续恢复

#将备份后的binlog拷贝到恢复机器上

[root@pc1 xtrbak]# cat 2020-03-21_11-10-52/xtrabackup_binlog_info

mysql-bin.000044 394  2daff865-67ce-11ea-874e-080027040516:1-14,

6b3c9713-6916-11ea-87dd-080027040516:1-3,

a4ac8cd2-e17c-11e9-a602-080027040516:1-89,

d4388817-6914-11ea-bc56-080027040516:1-8,

d4d2f1d2-66f2-11ea-972a-080027040516:1-17,

fa0fb1c5-6912-11ea-a627-080027040516:1-5

因此这里需要拷贝mysql-bin.000044，mysql-bin.000045，mysql-bin.000046

scp mysql-bin.000044 mysql-bin.000045 mysql-bin.000046 192.168.1.203:/download/

 

/usr/local/mysql/bin/mysqlbinlog mysql-bin.000044 --start-position=394 --skip-gtids=true  | mysql -u root -p

/usr/local/mysql/bin/mysqlbinlog mysql-bin.000045 --skip-gtids=true  | mysql -u root -p

/usr/local/mysql/bin/mysqlbinlog mysql-bin.000046 --stop-position=459 --skip-gtids=true  | mysql -u root -p

2.2.3 将恢复后的表导出

mysqldump -u root -p --single-transaction dba t4 -S /tmp/mysql.sock > t4.bak

scp t4.bak 192.168.1.202:/download/

2.3 将表恢复到生产环境

mysql -u root -p dba < t4.bak