1. 实验介绍
环境
| IP | 作用 | 环境 |
|---|---|---|
| 192.168.150.253 | 源实例（误操作的实例） | CentOS 7.4、已安装 MySQL 8.0.25、已安装 XtraBackup 8.0.25 |
| 192.168.150.123 | 目标实例（用于恢复数据） | CentOS 7.4、已安装 MySQL 8.0.25、已安装 XtraBackup 8.0.25 |
大致过程：
在源实例写入基础数据，然后进行全量备份，再写入增量数据，之后模拟在源实例误删除一个数据库，之后通过全量备份在目标实例上进行恢复，把源实例的 Binlog 传输到恢复数据的实例，然后修改成 relay log，再通过 start slave sql_thread until sql_before_gtids=”xxx” 同步数据到误操作前面的一个位点。
2. 数据写入
在源实例创建测试库和测试表：
1 | mysql> create database backup;Query OK, 1 row affected (0.06 sec) |
写入数据：
1 | mysql> insert into number(updatetime) values(now());Query OK, 1 row affected (0.04 sec) |
查询数据：
1 | mysql> select * from number;+----+---------------------+| id | updatetime |+----+---------------------+| 1 | 2021-07-02 11:01:52 |+----+---------------------+1 row in set (0.00 sec) |
3. 全量备份
在源实例增加备份用户：
1 | mysql> CREATE USER `u_xtrabackup`@`localhost` IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Ijnbgt@123';Query OK, 0 rows affected (0.02 sec) |
在源实例进行全量备份：
1 | xtrabackup --defaults-file=/data/mysql/conf/my.cnf -uu_xtrabackup -p'Ijnbgt@123' --backup --stream=xbstream --target-dir=./ >/data/backup/xtrabackup.xbstream |
将全量备份传到目标实例上：
1 | scp /data/backup/xtrabackup.xbstream 192.168.150.123:/data/backup/recover/ |
4. 模拟增量数据写入
在源实例写入一条数据：
1 | mysql> insert into number(updatetime) values(now());Query OK, 1 row affected (0.05 sec) |
5. 模拟误操作
在源实例模拟删库误操作：
1 | mysql> drop database backup;Query OK, 1 row affected (0.07 sec) |
6. 恢复全量备份的数据
关闭目标实例运行的 MySQL：
1 | mysqladmin -S /tmp/mysql.sock -p shutdown |
清空目标实例数据目录和事务日志目录：
1 | rm /data/mysql/data/* -rfrm /data/mysql/binlog/* -rf |
将全备导入目标实例：
1 | cd /data/backup/recover/xbstream -x < xtrabackup.xbstreamxtrabackup --prepare --target-dir=./xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./ |
修改目标实例 MySQL 数据目录的属主：
1 | chown -R mysql.mysql /data/mysql/ |
修改配置文件 /data/mysql/conf/my.cnf（配置启动时不启动复制、relay log 元数据通过文件形式记录，server-id 不能跟原实例相同）：
1 | [mysqld]skip-slave-start = 1relay_log_info_repository=fileserver-id = 150123 |
启动 MySQL：
1 | nohup /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/conf/my.cnf --user=mysql & |
查看数据（此时只是恢复了全量数据，所以数据不完整）：
1 | mysql> select * from backup.number;+----+---------------------+| id | updatetime |+----+---------------------+| 1 | 2021-07-02 15:06:04 |+----+---------------------+1 row in set (0.00 sec) |
7. 恢复增量数据
清空目标实例的系统变量 gtid_purged 和 gtid_executed：
1 | mysql> reset master;Query OK, 0 rows affected (0.01 sec) |
设置 gtid_purged（这个位点取至 xtrabackup_binlog_info）：
1 | mysql> set global gtid_purged='10242962-da16-11eb-8ea5-fa163e1c875d:1-22';Query OK, 0 rows affected (0.00 sec) |
让该 MySQL 知道自己是一个从库（192.168.1.1 是随便指定的 IP）：
1 | mysql> change master to master_host='192.168.1.1';Query OK, 0 rows affected (0.04 sec) |
关闭目标实例：
1 | mysqladmin -S /tmp/mysql.sock -p shutdown |
删除该实例的 relay-log.info：
1 | rm /data/mysql/data/relay-log.info -rf |
删除所有 relay log：
1 | rm /data/mysql/binlog/mysql-relay-bin.* -rf |
拷贝源实例 MySQL 全备之后的 Binlog：
1 | scp /data/mysql/binlog/mysql-bin.000008 192.168.150.123:/data/mysql/binlog |
在目标实例中，将 Binlog 改成 Relay 文件：
1 | cd /data/mysql/binlog/rename mysql-bin mysql-relay-bin mysql-bin.000008 |
写入 relay log 的索引文件：
1 | ls /data/mysql/binlog/mysql-relay-bin.0* >mysql-relay-bin.index |
查看 relay log 的索引文件：
1 | cat mysql-relay-bin.index/data/mysql/binlog/mysql-relay-bin.000008 |
修改事务日志目录下文件的属组：
1 | chown -R mysql.mysql /data/mysql/binlog |
启动目标实例：
1 | nohup /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/conf/my.cnf --user=mysql & |
执行 change master：
1 | change master to relay_log_file='mysql-relay-bin.000008',relay_log_pos=196; |
(这个位点来源于 备份 xtrabackup_binlog_info)
解析误操作时间点的 Binlog（Binlog 较大的情况可以增加时间范围）：
1 | mysqlbinlog mysql-relay-bin.000008 --base64-output=decode-rows -v >/data/0702.sql |
解析 Binlog 的结果文件 /data/0702.sql 内容如下：
1 | /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; |
启动 sql 线程，同步数据到误操作之前的一个事务：
1 | start slave sql_thread until sql_before_gtids='10242962-da16-11eb-8ea5-fa163e1c875d:24'; |
该 gtid 值取至上面解析的 Binlog，为误操作这个事务的 GTID。
在目标实例上查询数据（此时的数据已经恢复到误操作前一刻）：
1 | mysql> select * from backup.number; |
最终可以将误删除的库恢复到原实例。