持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第5天，点击查看活动详情
失误删库不一定要跑路，只要有合理的备份策略，绝大多数情况都可以恢复到删库之前的那一刻。 如果要恢复，一般采用的办法是使用上一次全备先恢复数据，增量数据通过导入从全备开始到误操作之前的 Binlog，但是这种方式如果 Binlog 多，通常是比较慢的，并且很容易导入到一半时报错，这里给大家介绍另外一种方式进行误操作的恢复，关键时刻能救命
1 步骤
ps：切勿在生产进行删库，一切都在本地进行
大致过程：
在源实例写入基础数据，然后进行全量备份，再写入增量数据，之后模拟在源实例误删除一个数据库，之后通过全量备份在目标实例上进行恢复，把源实例的 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 '2021-11-08 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-11-08 13:01:52 |
+----+---------------------+
1 row in set (0.00 sec)
3 全量备份
在源实例增加备份用户：
mysql> CREATE USER `u_xtrabackup`@`localhost` IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'admin@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)
将全量备份传到目标实例上：
scp /data/backup/xtrabackup.xbstream 192.168.100.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-11-08 13:01:52 |
| 2 | 2021-11-08 13:05:31 |
+----+---------------------+
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/* -rf
rm /data/mysql/binlog/* -rf
将全备导入目标实例：
cd /data/backup/recover/
xbstream -x < xtrabackup.xbstream
xtrabackup --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 = 1
relay_log_info_repository=file
server-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-11-08 13:01:52 |
+----+---------------------+
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.100.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**(这个位点来源于 备份 xtrabackup_binlog_info)**
change master to relay_log_file='mysql-relay-bin.000008',relay_log_pos=196;
解析误操作时间点的 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 13: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 13:06:36 server id 6666 end_log_pos 196 CRC32 0x9ed4ca96 Previous-GTIDs
# 10242962-da16-11eb-8ea5-fa163e1c875d:1-22
# at 196
#210702 13: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-11-08 13:08:05.877833 CST)
# immediate_commit_timestamp=1625209685877833 (2021-11-08 13: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 13:08:05 server id 6666 end_log_pos 360 CRC32 0xbf114777 Querythread_id=18exec_time=0error_code=0
SET 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 13:08:05 server id 6666 end_log_pos 484 CRC32 0x5fdcc2f8 Table_map: `backup`.`number` mapped to number 179
# at 484
#210702 13: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 13:08:05 server id 6666 end_log_pos 559 CRC32 0x3f3da548 Xid = 162
COMMIT/*!*/;
# at 559
#210702 13: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-11-08 13:08:05.778358 CST)
# immediate_commit_timestamp=1625209718778358 (2021-11-08 13:08:05.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 13:08:38 server id 6666 end_log_pos 746 CRC32 0xb477763c Querythread_id=18exec_time=0error_code=0Xid = 164
SET 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-11-08 13:01:52 |
| 2 | 2021-11-08 13:05:31 |
+----+---------------------+
2 rows in set (0.00 sec)
