mysql删除记录延迟_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_Log_Pos: 454

或者

[root@mysql-slave ~]# cat /usr/local/mysql-5.7.20/data/relay-log.info

7

./mysql-slave-relay-bin.000005

454

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 |

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

截取从库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)