MySQL误删除数据-延时复制故障恢复数据案例

  • 当前环境为GTID模式主从,延迟设置为300秒

主库创建一个测试延时复制的库与表
# 模拟业务在正常运行时的数据,此时从库也是有相同的数据
mysql> create database olda charset utf8mb4;
mysql> use olda
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> drop database olda;
从库停止SQL线程:
# 如果不停止SQL线程,等延迟时间过后从库olda数据库也会被删除
mysql> stop slave sql_thread;
查看从库未执行relay日志pos:
起点:
[root@mysql-slave ~]# mysql -uroot -predhat -e "show slave status\G" 2>/dev/null |egrep 'Relay_Log_File|Relay_Log_Pos'
               Relay_Log_File: mysql-slave-relay-bin.000005   <-----relay文件名
                Relay_Log_Pos: 454     <------------454为position起点

或者

[root@mysql-slave ~]# cat /usr/local/mysql-5.7.20/data/relay-log.info
7
./mysql-slave-relay-bin.000005   <---------文件
454     <------------454为position起点
mysql-bin.000001
3174
300
0
1
终点:
# 选择drop语句前的pos点id号
[root@mysql-slave ~]# mysql -uroot -predhat -e "show relaylog events in 'mysql-slave-relay-bin.000005';" 2>/dev/null
+------------------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name                     | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-slave-relay-bin.000005 |    4 | Format_desc    |         2 |         123 | Server ver: 5.7.20-log, Binlog ver: 4                              |
| mysql-slave-relay-bin.000005 |  123 | Previous_gtids |         2 |         194 | 53139cb7-c043-11e9-951e-000c291f7490:6-16                          |
| mysql-slave-relay-bin.000005 |  194 | Rotate         |         1 |           0 | mysql-bin.000001;pos=4                                             |
| mysql-slave-relay-bin.000005 |  241 | Format_desc    |         1 |         123 | Server ver: 5.7.20-log, Binlog ver: 4                              |
| mysql-slave-relay-bin.000005 |  360 | Rotate         |         0 |         407 | mysql-bin.000001;pos=154                                           |
| mysql-slave-relay-bin.000005 |  407 | Rotate         |         0 |         454 | mysql-bin.000001;pos=3174                                          |
| mysql-slave-relay-bin.000005 |  454 | Gtid           |         1 |        3239 | SET @@SESSION.GTID_NEXT= '53139cb7-c043-11e9-951e-000c291f7490:17' |
| mysql-slave-relay-bin.000005 |  519 | Query          |         1 |        3349 | create database olda charset utf8mb4                               |
| mysql-slave-relay-bin.000005 |  629 | Gtid           |         1 |        3414 | SET @@SESSION.GTID_NEXT= '53139cb7-c043-11e9-951e-000c291f7490:18' |
| mysql-slave-relay-bin.000005 |  694 | Query          |         1 |        3511 | use `olda`; create table t1(id int)                                |
| mysql-slave-relay-bin.000005 |  791 | Gtid           |         1 |        3576 | SET @@SESSION.GTID_NEXT= '53139cb7-c043-11e9-951e-000c291f7490:19' |
| mysql-slave-relay-bin.000005 |  856 | Query          |         1 |        3648 | BEGIN                                                              |
| mysql-slave-relay-bin.000005 |  928 | Table_map      |         1 |        3693 | table_id: 222 (olda.t1)                                            |
| mysql-slave-relay-bin.000005 |  973 | Write_rows     |         1 |        3743 | table_id: 222 flags: STMT_END_F                                    |
| mysql-slave-relay-bin.000005 | 1023 | Xid            |         1 |        3774 | COMMIT /* xid=138 */                                               |
| mysql-slave-relay-bin.000005 | 1054 | Gtid           |         1 |        3839 | SET @@SESSION.GTID_NEXT= '53139cb7-c043-11e9-951e-000c291f7490:20' |
| mysql-slave-relay-bin.000005 | 1119 | Query          |         1 |        3931 | drop database olda                                                 |   <------------drop语句前的1119为结束点
+------------------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
截取从库relay日志:
[root@mysql-slave ~]# mysqlbinlog --start-position=454 --stop-position=1119 /usr/local/mysql-5.7.20/data/mysql-slave-relay-bin.000005 > /root/relay.sql
恢复数据到从库:
# 可以看到olda库因为延时同步没有执行的drop已经被踢掉
mysql> source /root/relay.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| olda               |
| performance_schema |
| sys                |
+--------------------+
解除从库身份:
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)
「点点赞赏,手留余香」

    还没有人赞赏,快来当第一个赞赏的人吧!
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论