MySQL 删库到恢复

失误删库不一定要跑路，只要有合理的备份策略，绝大多数情况都可以恢复到删库之前的那一刻。

如果要恢复，一般采用的办法是使用上一次全备先恢复数据，增量数据通过导入从全备开始到误操作之前的 Binlog，但是这种方式如果 Binlog 多，通常是比较慢的，并且很容易导入到一半时报错，这篇文章就介绍另外一种方式进行误操作的恢复。

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 数据写入

在源实例创建测试库和测试表：

mysql> create database backup;Query OK, 1 row affected (0.06 sec)
mysql> use backup;Database changed
mysql> CREATE TABLE `number` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`updatetime` timestamp NOT NULL DEFAULT '1970-01-02 00:00:00',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Query OK, 0 rows affected, 1 warning (0.85 sec)

写入数据：

mysql> insert into number(updatetime) values(now());Query OK, 1 row affected (0.04 sec)

查询数据：

mysql> select * from number;
+----+---------------------+
| id | updatetime          |
+----+---------------------+
|  1 | 2021-07-02 11:01:52 |
+----+---------------------+
1 row in set (0.00 sec)

3 全量备份

在源实例增加备份用户：

mysql> CREATE USER `u_xtrabackup`@`localhost` IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Ijnbgt@123';Query OK, 0 rows affected (0.02 sec)
mysql> GRANT SELECT, RELOAD, PROCESS, SUPER, LOCK TABLES,BACKUP_ADMIN ON *.* TO `u_xtrabackup`@`localhost`;Query OK, 0 rows affected, 1 warning (0.05 sec)

在源实例进行全量备份：

xtrabackup --defaults-file=/data/mysql/conf/my.cnf -uu_xtrabackup -p'Ijnbgt@123' --backup --stream=xbstream --target-dir=./ >/data/backup/xtrabackup.xbstream

将全量备份传到目标实例上：

scp /data/backup/xtrabackup.xbstream 192.168.150.123:/data/backup/recover/

4 模拟增量数据写入

在源实例写入一条数据：

mysql> insert into number(updatetime) values(now());Query OK, 1 row affected (0.05 sec)
mysql> select * from number;+----+---------------------+| id | updatetime          |+----+---------------------+|  1 | 2021-07-02 15:06:04 ||  2 | 2021-07-02 15:08:05 |+----+---------------------+2 rows in set (0.00 sec)

5 模拟误操作

在源实例模拟删库误操作：

mysql> drop database backup;Query OK, 1 row affected (0.07 sec)

6 恢复全量备份的数据

关闭目标实例运行的 MySQL：

mysqladmin -S /tmp/mysql.sock -p shutdown

rm /data/mysql/data/* -rfrm /data/mysql/binlog/* -rf

将全备导入目标实例：

cd /data/backup/recover/xbstream -x < xtrabackup.xbstreamxtrabackup --prepare --target-dir=./xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./

修改目标实例 MySQL 数据目录的属主：

chown -R mysql.mysql /data/mysql/

修改配置文件 /data/mysql/conf/my.cnf（配置启动时不启动复制、relay log 元数据通过文件形式记录，server-id 不能跟原实例相同）：

[mysqld]skip-slave-start = 1relay_log_info_repository=fileserver-id = 150123

启动 MySQL：

nohup /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/conf/my.cnf --user=mysql &

查看数据（此时只是恢复了全量数据，所以数据不完整）：

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：

mysql> reset master;Query OK, 0 rows affected (0.01 sec)

设置 gtid_purged（这个位点取至 xtrabackup_binlog_info）：

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）：

mysql> change master to master_host='192.168.1.1';Query OK, 0 rows affected (0.04 sec)

关闭目标实例：

mysqladmin -S /tmp/mysql.sock -p shutdown

删除该实例的 relay-log.info：

rm /data/mysql/data/relay-log.info -rf

删除所有 relay log：

rm /data/mysql/binlog/mysql-relay-bin.* -rf

拷贝源实例 MySQL 全备之后的 Binlog：

scp /data/mysql/binlog/mysql-bin.000008 192.168.150.123:/data/mysql/binlog

在目标实例中，将 Binlog 改成 Relay 文件：

cd /data/mysql/binlog/rename mysql-bin mysql-relay-bin mysql-bin.000008

写入 relay log 的索引文件：

ls /data/mysql/binlog/mysql-relay-bin.0* >mysql-relay-bin.index

查看 relay log 的索引文件：

# cat mysql-relay-bin.index/data/mysql/binlog/mysql-relay-bin.000008

修改事务日志目录下文件的属组：

chown -R mysql.mysql /data/mysql/binlog

启动目标实例：

nohup /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/conf/my.cnf --user=mysql &

执行 change master：

change master to relay_log_file='mysql-relay-bin.000008',relay_log_pos=196;

(这个位点来源于备份 xtrabackup_binlog_info)

解析误操作时间点的 Binlog（Binlog 较大的情况可以增加时间范围）：

mysqlbinlog mysql-relay-bin.000008 --base64-output=decode-rows -v >/data/0702.sql

解析 Binlog 的结果文件 /data/0702.sql 内容如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#210702 15:06:36 server id 6666  end_log_pos 125 CRC32 0x5ebbec6f Start: binlog v 4, server v 8.0.25 created 210702 15:06:36# Warning: this binlog is either in use or was not closed properly.# at 125#210702 15:06:36 server id 6666  end_log_pos 196 CRC32 0x9ed4ca96 Previous-GTIDs# 10242962-da16-11eb-8ea5-fa163e1c875d:1-22# at 196#210702 15:08:05 server id 6666  end_log_pos 275 CRC32 0x70a004b6 GTIDlast_committed=0sequence_number=1rbr_only=yesoriginal_committed_timestamp=162520968587783immediate_commit_timestamp=1625209685877833transaction_length=363/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp=1625209685877833 (2021-07-02 15:08:05.877833 CST)# immediate_commit_timestamp=1625209685877833 (2021-07-02 15:08:05.877833 CST)/*!80001 SET @@session.original_commit_timestamp=1625209685877833*//*!*/;/*!80014 SET @@session.original_server_version=80025*//*!*/;/*!80014 SET @@session.immediate_server_version=80025*//*!*/;SET @@SESSION.GTID_NEXT= '10242962-da16-11eb-8ea5-fa163e1c875d:23'/*!*/;# at 275#210702 15:08:05 server id 6666  end_log_pos 360 CRC32 0xbf114777 Querythread_id=18exec_time=0error_code=0SET TIMESTAMP=1625209685/*!*/;SET @@session.pseudo_thread_id=18/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1168113696/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8mb4 *//*!*/;SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;SET @@session.time_zone='SYSTEM'/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;BEGIN/*!*/;# at 360# at 428#210702 15:08:05 server id 6666  end_log_pos 484 CRC32 0x5fdcc2f8 Table_map: `backup`.`number` mapped to number 179# at 484#210702 15:08:05 server id 6666  end_log_pos 528 CRC32 0x954f3089 Write_rows: table id 179 flags: STMT_END_F### INSERT INTO `backup`.`number`### SET###   @1=2###   @2=1625209685# at 528#210702 15:08:05 server id 6666  end_log_pos 559 CRC32 0x3f3da548 Xid = 162COMMIT/*!*/;# at 559#210702 15:08:38 server id 6666  end_log_pos 636 CRC32 0x2f89ff88 GTIDlast_committed=1sequence_number=2rbr_only=nooriginal_committed_timestamp=162520971877835immediate_commit_timestamp=1625209718778358transaction_length=187# original_commit_timestamp=1625209718778358 (2021-07-02 15:08:38.778358 CST)# immediate_commit_timestamp=1625209718778358 (2021-07-02 15:08:38.778358 CST)/*!80001 SET @@session.original_commit_timestamp=1625209718778358*//*!*/;/*!80014 SET @@session.original_server_version=80025*//*!*/;/*!80014 SET @@session.immediate_server_version=80025*//*!*/;SET @@SESSION.GTID_NEXT= '10242962-da16-11eb-8ea5-fa163e1c875d:24'/*!*/;# at 636#210702 15:08:38 server id 6666  end_log_pos 746 CRC32 0xb477763c Querythread_id=18exec_time=0error_code=0Xid = 164SET TIMESTAMP=1625209718/*!*/;drop database backup/*!*/;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*/;

启动 sql 线程，同步数据到误操作之前的一个事务：

start slave sql_thread until sql_before_gtids='10242962-da16-11eb-8ea5-fa163e1c875d:24';

该 gtid 值取至上面解析的 Binlog，为误操作这个事务的 GTID。

在目标实例上查询数据（此时的数据已经恢复到误操作前一刻）：

mysql> select * from backup.number;
+----+---------------------+
| id | updatetime          |
+----+---------------------+
|  1 | 2021-07-02 15:06:04 |
|  2 | 2021-07-02 15:08:05 |
+----+---------------------+
2 rows in set (0.00 sec)

最终可以将误删除的库恢复到原实例。