通过延迟复制与binlog恢复意外删除的数据

一、环境概述

以下是我们操作的数据库环境的详细信息：

二、主库设置

在主库上，我们首先需要创建一个复制用户并授权：

shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5725.sock -p
greatsql> CREATE USER 'repl'@'%' IDENTIFIED BY '123';
greatsql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

三、配置延迟从库

接下来，我们配置从库以实现延迟复制：

greatsql> CHANGE MASTER TO
    MASTER_HOST='192.168.134.199',
    MASTER_PORT=5725,
    MASTER_USER='repl',
    MASTER_PASSWORD='123',
    MASTER_AUTO_POSITION=1,
    MASTER_DELAY=7200;
greatsql> START SLAVE;
greatsql> SHOW SLAVE STATUSG

四、模拟主库数据表误删除

我们模拟一个误删除操作，如下：

shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5725.sock -p sysbench
greatsql> DROP TABLE sbtest2;

五、从延迟从库恢复数据至主库故障前状态

1. 备份从库：为防止恢复过程中出现意外，我们首先使用Xtrabackup备份从库：

$ xtrabackup --defaults-file=/data1/greatsql/greatsql5726/my5726.cnf -S /tmp/greatsql5726.sock --backup --slave-info 
    --stream=xbstream --target-dir=/backup/full.xb

1. 定位误操作的binlog：我们需要找到主库误操作所在的binlog文件，并确认其位置信息：

$ /usr/local/greatsql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -vvv ./* | grep -rli 'drop'
$ /usr/local/greatsql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -vvv mysql-bin.000002 | less

1. 停止sql_thread线程：设置复制不延时，并在误操作binlog位置点停止复制：

shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5726.sock -p
greatsql> STOP SLAVE;
greatsql> CHANGE MASTER TO MASTER_DELAY = 0;
greatsql> START SLAVE IO_THREAD;
greatsql> START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS='2fc5a82c-2ac3-11ee-9f7f-00163e402951:187';
greatsql> SHOW SLAVE STATUSG

1. 等待复制停止：等待复制到达指定的停止位置点，此时sql_thread线程已停止。

1. 备份误操作的表：查看从库中误操作的表，并备份以便恢复到主库：

greatsql> SHOW TABLES FROM sysbench;
greatsql> SELECT COUNT(*) FROM sysbench.sbtest2;
shell> /usr/local/greatsql/bin/mysqldump -S /tmp/mysql5726.sock --set-gtid-purged=OFF --single-transaction --master-data=2 --max-allowed-packet=32M -q sysbench sbtest2 > sbtest2.sql

1. 恢复备份数据至主库：

```sql
shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5725.sock -p -A sysbench
greatsql> SET sql_log_bin = off;
greatsql> SOURCE sbtest2.sql