背景说明：3306端口数据库，做一次xtrabackup全备之后，删除表，模拟恢复到 3308 端口数据库。 采用模拟slave线程恢复的方式，速度更快。 当恢复的场景是从全备恢复某一张表时，也可以使用复制过滤功能，只应用对应表的binlog，不用全部binlog都恢复。 先做一次完整备份： innobackupex --defaults-file=/etc/my.cnf --user root --password Jimstars /data/mysqlbak 执行完命令后，/data/mysqlbak 目录下会生成目录：2020-08-23_12-07-44 多切换几次 binlog日志 mysql -uroot -pJimstars -e "flush logs;" 插入测试数据 mysql -uroot -pJimstars scott -e "create table test_aaa(id int,name varchar(200));" mysql -uroot -pJimstars scott -e "insert into test_aaa values (20001,'full_bak');" 多切换几次 binlog日志 mysql -uroot -pJimstars -e "flush logs;" 再插入测试数据 mysql -uroot -pJimstars scott -e "insert into test_aaa values (20002,'full_bak2');" 多切换几次 binlog日志 mysql -uroot -pJimstars -e "flush logs;" 再插入测试数据 mysql -uroot -pJimstars scott -e "insert into test_aaa values (20003,'full_bak3');" 多切换几次 binlog日志 mysql -uroot -pJimstars -e "flush logs;" 假设在这里误删除了表 root@localhost:mysql.sock/scott> drop table test_aaa; Query OK, 0 rows affected (0.02 sec) root@localhost:mysql.sock/scott> show master status; +---------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------------------+----------+--------------+------------------+-------------------+ | VM_0_15_centos-bin.000011 | 342 | | | | +---------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 多切换几次 binlog日志 mysql -uroot -pJimstars -e "flush logs;" 创建另外的测试表，作为对比参考，如果是按照指定点恢复，下面创建的表不应该被恢复出来 mysql -uroot -pJimstars scott -e "create table test_aa2 like t1;" mysql -uroot -pJimstars scott -e "insert into test_aa2 select * from t1;" 解析binlog，找到误删除操作对应的 GTID ： mysqlbinlog --base64-output=DECODE-ROWS -vv -d scott /data/mysql/VM_0_15_centos-bin.000011 > /tmp/scott.log [root@VM_0_15_centos mysqlbak]# grep -C 30 test_aaa /tmp/scott.log /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200823 13:14:43 server id 2003306 end_log_pos 123 CRC32 0xcf238446 Start: binlog v 4, server v 5.7.22-log created 200823 13:14:43 # at 123 #200823 13:14:43 server id 2003306 end_log_pos 154 CRC32 0x0cf1aaac Previous-GTIDs # [empty] # at 154 #200823 13:14:48 server id 2003306 end_log_pos 219 CRC32 0xb194c306 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #200823 13:14:48 server id 2003306 end_log_pos 342 CRC32 0x341d61ab Query thread_id=20 exec_time=0 error_code=0 use `scott`/*!*/; SET TIMESTAMP=1598159688/*!*/; SET @@session.pseudo_thread_id=20/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=268435456/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!C utf8mb4 *//*!*/; SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; DROP TABLE `test_aaa` /* generated by server */ /*!*/; # at 342 #200823 13:15:24 server id 2003306 end_log_pos 398 CRC32 0x34db40c1 Rotate to VM_0_15_centos-bin.000012 pos: 4 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 从上述日志，可以得出我们要找出的点为： 154 备份文件xtrabackup_binlog_info中找到备份对应的binlog位置 [root@VM_0_15_centos 2020-08-23_12-07-44]# cat xtrabackup_binlog_info VM_0_15_centos-bin.000004 154 恢复全备，这里采用恢复本地端口 3306的数据库 恢复到 3308 innobackupex --apply-log /data/mysqlbak/2020-08-23_12-07-44 innobackupex --defaults-file=/etc/my_3308.cnf --copy-back /data/mysqlbak/2020-08-23_12-07-44 chown -R mysql.mysql /data/mysql_3308 拷贝binlog 到 /data/mysql_3308 cp /data/mysql/VM_0_15_centos-bin.0* /data/mysql_3308 chown -R mysql.mysql /data/mysql_3308 将binlog改名为relay log rename "bin" "relay-bin" VM_0_15_centos-bin.0* ls ./VM_0_15_centos-relay-bin.0* > mysql-relay.index chown -R mysql.mysql /data/mysql_3308 [mysqld] 中设置参数 relay_log = /data/mysql_3308/mysql-relay.index # server_id 必须设置和原来实例的 server_id 不一样，不然不会应用 binlog server_id=2003308 # relay_log_recovery=1时，relay log 会在MySQL重启、复制线程启动时被清除 relay_log_recovery=0 # 开启并行复制 slave-parallel-type=LOGICAL_CLOCK # 设置work线程数量 slave_parallel_workers=8 # 设置redo log异步刷盘，每秒1次 innodb_flush_log_at_trx_commit=0 # 设置binlog刷盘策略 sync_binlog = 0 启动MySQL。 mysqld --defaults-file=/etc/my_3308.cnf & 建立复制通道： mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1',RELAY_LOG_FILE='VM_0_15_centos-relay-bin.000004',RELAY_LOG_POS=154; 确认slave sql thread的起始位置和设置的一样 mysql> select * from mysql.slave_relay_log_info; 启动复制前，如果只需要恢复指定表，可以使用复制过滤功能，加快binlog回放速度： mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('scott.test_aaa'); 启动复制线程，到误删除那个事务停止： mysql> START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE = 'VM_0_15_centos-relay-bin.000011', RELAY_LOG_POS = 123;
说明：RELAY_LOG_POS = 123 ，这个点一定要找对，如果是154或者219，则恢复失败。
如果是恢复全部binlog，则 mysql> start slave sql_thread;
如果是恢复全部binlog，则 mysql> start slave sql_thread;
接下来把误操作的表导出，再导入到生产环境。