MySQL 删库到恢复

MySQL数据库联盟 2021-07-05

622

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

如果要恢复，一般采用的办法是使用上一次全备先恢复数据，增量数据通过导入从全备开始到误操作之前的 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/* -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-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=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 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 = 162
COMMIT/*!*/;
# 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 = 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-07-02 15:06:04 |
|  2 | 2021-07-02 15:08:05 |
+----+---------------------+
2 rows in set (0.00 sec)

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

mysql

文章转载自MySQL数据库联盟，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。