如何使用mysqldump+my2sql+mysqlbinlog恢复误删除的表

背景： 

​ 客户误操作，执行了drop table，导致数据丢失，需要紧急恢复。
 

恢复方法： 

​ 利用mysqldump中的备份 + binlog 恢复数据，到drop 操作前。

 
 主要恢复步骤，如下： 

1. 恢复mysqldump中指定的表及数据。
2. 利用my2sql工具，读取binlog数据，恢复我们想要的表。
复制

一、模拟测试环境

1. 创建基础数据

-- 创建测试数据库
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)

mysql> use testdb;
Database changed

-- 建学生信息表student
create table student
(
sno varchar(20) not null primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
);

-- 建立教师表
create table teacher
(
tno varchar(20) not null primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null
);

-- 添加学生信息
insert into student values('108','曾华','男','1997-09-01','95033');
insert into student values('105','匡明','男','1995-10-02','95031');
insert into student values('107','王丽','女','1996-01-23','95033');
insert into student values('101','李军','男','1996-02-20','95033');
insert into student values('109','王芳','女','1995-02-10','95031');
insert into student values('103','陆君','男','1994-06-03','95031');

-- 添加教师表
insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');
复制

2. 使用mysqldump做一下全备份

[root@mysql57 backup]# mysqldump -uroot -plhr -h127.0.0.1 --single-transaction --master-data=2 testdb > /backup/testdb_bak.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
复制

3. 模拟业务正常DML操作

insert into student values('104','小明','男','1994-06-03','95031');
insert into teacher values('832','王红','女','1967-08-14','教授','电子工程系');
delete from teacher where tno=856;
update student set class=95033 where sno=105;

-- 操作其他数据库
mysql> delete from sbtest.sbtest1 limit 10;
Query OK, 10 rows affected (0.01 sec)

-- 切换log
flush logs;

-- 建立课程表course
create table course
(
cno varchar(20) not null primary key,
cname varchar(20) not null,
tno varchar(20) not null
);
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('9-888','高等数学','831');

-- 更新teacher表
update teacher set prof='副教授' where tno=825;
insert into teacher values('833','晴川','女','1988-08-14','助教','计算机系');
复制

4. 误操作，drop table

mysql> select * from teacher;
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname  | tsex | tbirthday           | prof      | depart          |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
| 825 | 王萍   | 女   | 1972-05-05 00:00:00 | 副教授    | 计算机系        |
| 831 | 刘冰   | 女   | 1977-08-14 00:00:00 | 助教      | 电子工程系      |
| 832 | 王红   | 女   | 1967-08-14 00:00:00 | 教授      | 电子工程系      |
| 833 | 晴川   | 女   | 1988-08-14 00:00:00 | 助教      | 计算机系        |
+-----+--------+------+---------------------+-----------+-----------------+
5 rows in set (0.00 sec)

mysql> drop table teacher;
Query OK, 0 rows affected (0.01 sec)

-- drop table之后，继续做其他操作
mysql> insert into student values('106','小红','女','1994-09-03','95031');
Query OK, 1 row affected (0.00 sec)
复制

二、恢复误删除表

1. 切换binlog

– 当用户反馈问题后，先手动切一个日志，方便后续解析binlog日志文件。

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
复制

2. 从备份中抽取建表语句

[root@mysql57 backup]# cat testdb_bak.sql |grep teacher -A 15|more
-- Table structure for table `teacher`
--
DROP TABLE IF EXISTS `teacher`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teacher` (
  `tno` varchar(20) NOT NULL,
  `tname` varchar(20) NOT NULL,
  `tsex` varchar(20) NOT NULL,
  `tbirthday` datetime DEFAULT NULL,
  `prof` varchar(20) DEFAULT NULL,
  `depart` varchar(20) NOT NULL,
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
复制

3. 从备份中抽取需要恢复的数据

[root@mysql57 backup]# grep 'INSERT INTO `teacher`' testdb_bak.sql >>teacher_data.sql

[root@mysql57 backup]# ll
total 8
-rw-r--r-- 1 root root  315 Aug 17 18:45 teacher_data.sql
-rw-r--r-- 1 root root 3816 Aug 17 18:28 testdb_bak.sql
[root@mysql57 backup]# cat teacher_data.sql 
INSERT INTO `teacher` VALUES ('804','李诚','男','1958-12-02 00:00:00','副教授','计算机系'),('825','王萍','女','1972-05-05 00:00:00','助教','计算机系'),('831','刘冰','女','1977-08-14 00:00:00','助教','电子工程系'),('856','张旭','男','1969-03-12 00:00:00','讲师','电子工程系');
复制

4. 创建表

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> CREATE TABLE `teacher` (
    ->   `tno` varchar(20) NOT NULL,
    ->   `tname` varchar(20) NOT NULL,
    ->   `tsex` varchar(20) NOT NULL,
    ->   `tbirthday` datetime DEFAULT NULL,
    ->   `prof` varchar(20) DEFAULT NULL,
    ->   `depart` varchar(20) NOT NULL,
    ->   PRIMARY KEY (`tno`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.02 sec)
复制

5. 锁表（可选）

– 根据需要，是否需要锁表

mysql> lock tables teacher  write;
Query OK, 0 rows affected (0.00 sec)
复制

6.恢复备份中的数据

mysql> source /backup/teacher_data.sql
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- 也可以根据需求，在恢复前，先执行set sql_log_bin=0，不记录binlog
复制

7.检查数据

mysql> select * from teacher;
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname  | tsex | tbirthday           | prof      | depart          |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
| 825 | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 计算机系        |
| 831 | 刘冰   | 女   | 1977-08-14 00:00:00 | 助教      | 电子工程系      |
| 856 | 张旭   | 男   | 1969-03-12 00:00:00 | 讲师      | 电子工程系      |
+-----+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)
复制

– 此时，已经将备份中的数据恢复出来。

8. 从binlog中恢复我们需要的数据

8.1 检查备份文件，找到需要恢复的binlog日志

[root@mysql57 backup]# grep "CHANGE"  testdb_bak.sql  |more
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql57-bin.000040', MASTER_LOG_POS=4296;
复制

 可以看到，我们所需要应用的binlog ，开始点为mysql57-bin.000040，pos:4296。

8.2 查看当前binlog

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql57-bin.000042
         Position: 1067
     Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set: 08ad837f-cd2e-11ec-b75b-0242ac110002:1-2,
4297e09f-2b30-11ec-a651-0242ac110002:1-3,
8a788db6-cd32-11ec-ba2f-0242ac110002:1-81858
1 row in set (0.00 sec)

ERROR: 
No query specified
复制

可以看到，mysql57-bin.000042是我们手动切换刚产生的，所以我们drop table的操作应该在mysql57-bin.000041中。

8.3 找到drop table的时间点

[root@mysql57 data]# mysqlbinlog -d testdb -v --skip-gtids mysql57-bin.000041 |grep 'DROP TABLE `teacher' -C 10
###   @1='9-888'
###   @2='高等数学'
###   @3='831'
# at 3318
#220817 18:41:45 server id 56483306  end_log_pos 3349 CRC32 0x94453e68  Xid = 331
COMMIT/*!*/;
# at 3349
# at 3414
#220817 18:42:00 server id 56483306  end_log_pos 3538 CRC32 0x00311963  Query   thread_id=25    exec_time=0     error_code=0
SET TIMESTAMP=1660732920/*!*/;
DROP TABLE `teacher` /* generated by server */
/*!*/;
# at 3538
# at 3603
#220817 18:43:11 server id 56483306  end_log_pos 3677 CRC32 0x7b4b5cb3  Query   thread_id=25    exec_time=0     error_code=0
SET TIMESTAMP=1660732991/*!*/;
BEGIN
/*!*/;
# at 3677
#220817 18:43:11 server id 56483306  end_log_pos 3742 CRC32 0xbfcb9e24  Table_map: `testdb`.`student` mapped to number 139
# at 3742
复制

可以看到，drop table的时间点对应的binlog位置为mysql57-bin.000041 ，pos 3349。

因此，我们所需要恢复的binlog数据为：

开始点mysql57-bin.000040，pos:4296

结束点mysql57-bin.000041 ，pos 3349

9. 利用my2sql恢复我们需要的数据

9.1 安装my2sql

-- 1.首先需要go语言的环境，tar包方式安装go
mv go1.10.3.linux-amd64.tar.gz /usr/local/
cd /usr/local/
tar -zxvf  go1.10.3.linux-amd64.tar.gz
vim /etc/profile
# 在最后一排添加
export GOROOT=/usr/local/go
export PATH=$PATH:$GOROOT/bin

-- 重新生效
source /etc/profile

-- 2.安装my2sql
unzip my2sql-master.zip
mv my2sql-master my2sql
cd my2sql/releases/centOS_release_7.x/
mv my2sql /usr/local/bin/my2sql
chmod +x /usr/local/bin/my2sql
复制

9.2 根据操作pos解析binlog

[root@mysql57 backup]# mkdir -p /tmp/forward

-- 根据操作pos解析binlog，-work-type 2sql生成原始sql (标准SQL)
[root@mysql57 backup]# my2sql -user root -password lhr  -port 3306 \
> -host 127.0.0.1 -databases testdb  -tables teacher \
> -work-type 2sql   -start-file mysql57-bin.000040 \
> -start-pos  4296 -stop-file  mysql57-bin.000041 -stop-pos 3349 \
> -output-dir /tmp/forward
[2022/08/17 19:04:19] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3306 root   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2022/08/17 19:04:19] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql57-bin.000040, 4296)
[2022/08/17 19:04:19] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2022/08/17 19:04:19] [info] events.go:210 start thread to write redo/rollback sql into file
[2022/08/17 19:04:19] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2022/08/17 19:04:19] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2022/08/17 19:04:19] [info] repl.go:16 start to get binlog from mysql
[2022/08/17 19:04:19] [info] binlogsyncer.go:777 rotate to (mysql57-bin.000040, 4296)
[2022/08/17 19:04:19] [info] binlogsyncer.go:777 rotate to (mysql57-bin.000041, 4)
[2022/08/17 19:04:19] [info] binlogsyncer.go:777 rotate to (mysql57-bin.000041, 4)
[2022/08/17 19:04:19] [info] binlogsyncer.go:777 rotate to (mysql57-bin.000042, 4)
[2022/08/17 19:04:19] [info] binlogsyncer.go:777 rotate to (mysql57-bin.000042, 4)
[2022/08/17 19:04:19] [info] events.go:241 finish processing mysql57-bin.000041 1690
[2022/08/17 19:04:19] [info] com.go:58 stop to get event. StopFilePos set. currentBinlog (mysql57-bin.000041, 3349) StopFilePos (mysql57-bin.000041, 3349)
[2022/08/17 19:04:19] [info] repl.go:18 finish getting binlog from mysql
[2022/08/17 19:04:19] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2022/08/17 19:04:19] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2022/08/17 19:04:19] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2022/08/17 19:04:19] [info] events.go:274 finish writing redo/forward sql into file
[2022/08/17 19:04:19] [info] events.go:277 exit thread to write redo/rollback sql into file


-- 检查生成的原始SQL
[root@mysql57 forward]# cat forward.40.sql 
INSERT INTO `testdb`.`teacher` (`tno`,`tname`,`tsex`,`tbirthday`,`prof`,`depart`) VALUES ('832','王红','女','1967-08-14 00:00:00','教授','电子工程系');
DELETE FROM `testdb`.`teacher` WHERE `tno`='856';

[root@mysql57 forward]# cat forward.41.sql 
UPDATE `testdb`.`teacher` SET `prof`='副教授' WHERE `tno`='825';
INSERT INTO `testdb`.`teacher` (`tno`,`tname`,`tsex`,`tbirthday`,`prof`,`depart`) VALUES ('833','晴川','女','1988-08-14 00:00:00','助教','计算机系');
复制

9.3 应用binlog中解析的语句

-- 继续追数据至drop table前
mysql> source /tmp/forward/forward.40.sql 
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)

mysql> source /tmp/forward/forward.41.sql
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Query OK, 1 row affected (0.00 sec)
复制

9.4 再次验证数据

mysql> select * from teacher;
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname  | tsex | tbirthday           | prof      | depart          |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
| 825 | 王萍   | 女   | 1972-05-05 00:00:00 | 副教授    | 计算机系        |
| 831 | 刘冰   | 女   | 1977-08-14 00:00:00 | 助教      | 电子工程系      |
| 832 | 王红   | 女   | 1967-08-14 00:00:00 | 教授      | 电子工程系      |
| 833 | 晴川   | 女   | 1988-08-14 00:00:00 | 助教      | 计算机系        |
+-----+--------+------+---------------------+-----------+-----------------+
5 rows in set (0.00 sec)
复制

数据已经恢复到误操作drop table 之前。请开发人员验证数据准确性。 

10. 解锁表

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
复制