MySQL误操作数据恢复之误删表

一、前言

之前介绍了误删表数据后闪回的操作，本次介绍误删表后要如何进行操作。

二、数据恢复演示

set1:操作前先做个数据库的全备

mysqldump -uroot -p -S /root/mysql-sandboxes/5003/sandboxdata/mysqld.sock --master-data=2 --single-transaction  -A  >3306_all.sql

set2:模拟误删表操作

root@mysqld.sock 09:43:  [db1]> checksum table sbtest10;+--------------+------------+| Table        | Checksum   |+--------------+------------+| db1.sbtest10 | 4221337750 |+--------------+------------+1 row in set (0.29 sec)
root@mysqld.sock 09:44:  [db1]> drop table sbtest10;Query OK, 0 rows affected (0.32 sec)
root@mysqld.sock 09:44:  [db1]> show tables;+---------------+| Tables_in_db1 |+---------------+| sbtest1       || sbtest2       || sbtest3       || sbtest4       || sbtest5       || sbtest6       || sbtest7       || sbtest8       || sbtest9       |+---------------+9 rows in set (0.00 sec)

set3:新环境恢复备份

source /root/3306_all.sql;

set4:获取误操作的binlog位置

set5:新环境与就环境构建主从关系，并且从库同步到误操作前的一个事务

root@mysqld.sock 10:01:  [(none)]> change master to master_host='127.0.0.1',master_port=3306,master_user='test',master_password='test',master_auto_position=1;Query OK, 0 rows affected, 8 warnings (0.02 sec)
root@mysqld.sock 10:04:  [(none)]> start slave io_thread;Query OK, 0 rows affected, 1 warning (0.00 sec)
root@mysqld.sock 10:05:  [(none)]> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 127.0.0.1                  Master_User: test                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: testdb02-bin.000015          Read_Master_Log_Pos: 404               Relay_Log_File: testdb02-relay-bin.000001                Relay_Log_Pos: 4        Relay_Master_Log_File:              Slave_IO_Running: Yes            Slave_SQL_Running: No            ..... --设置SQL_threads停止的位置  root@mysqld.sock 10:05:  [(none)]> start slave sql_thread  until sql_before_gtids='2c736321-0be5-11ed-b3f1-525496022f28:1314817';Query OK, 0 rows affected, 1 warning (0.00 sec)         

set6:将新环境的sbtest10导出后导入源库

mysqldump -uroot -p -S /root/mysql-sandboxes/3309/sandboxdata/mysqld.sock --databases db1 --tables sbtest10 >sbtest10.sq
root@mysqld.sock 10:12:  [db1]> source sbtest10.sql;
root@mysqld.sock 10:11:  [db1]> show tables;+---------------+| Tables_in_db1 |+---------------+| sbtest1       || sbtest10      || sbtest2       || sbtest3       || sbtest4       || sbtest5       || sbtest6       || sbtest7       || sbtest8       || sbtest9       |+---------------+10 rows in set (0.00 sec)
root@mysqld.sock 10:12:  [db1]> checksum table sbtest10;+--------------+------------+| Table        | Checksum   |+--------------+------------+| db1.sbtest10 | 4221337750 |+--------------+------------+1 row in set (0.17 sec)

至此误删表的数据已经恢复完毕。