MySQL基于xtrabackup全量备份进行单表恢复

MySQL单表恢复

在后续将MySQL备份恢复重新整理了一下，发了公众号文章，可参考
MySQL单表恢复

一、背景

​ Percona-xtrabackup是Percona开发的用于MySQL数据库物理热备的备份工具，在生产环境中经常用于做MySQL InnoDB类型表的备份。数据库使用者经常可能更新数据时忘记添加WHERE条件或者进行了误删除操作。此时若进行整库恢复会造成时间成本很高，并且DBA一般不会直接操作业务数据，这时会新建一个临时的独立节点只针对误操作的表进行单表恢复。

二、所需要的文件

由于备份时进行了压缩，所以这里显示的数据文件都有.qp后缀，恢复时也需先进性解压缩

1. 公共文件

• mysql目录：MySQL数据库节点的用户等基础信息
• perfomance_schema目录：MySQL数据库节点的基础信息
• ibdata1.qp：共享表空间文件

2. 备份时的点位文件

• xtrabackup_binlog_info：记录备份完成时的binlog点位信息
• xtrabackup_checkpoints：记录备份类型，全量备份为backup_type = full-backuped，增量备份为backup_type = incremental
• xtrabackup_info：记录备份时的一些参数，例如：脚本版本、数据库版本、备份时间等信息
• xtrabackup_logfile.qp：记录备份开始到备份结束所有的redo日志，xtrabakcup在apply log时读取该文件，进行redo回滚
• xtrabackup_slave_info：记录主库的binlog信息，新建从库时可以根据该文件指向与备份节点的同一个主库
• backup-my.cnf：启动备份集需要的最小参数

3. 要还原表的数据文件

• t_test.frm：要还原表的表结构文件
• t_test.ibd.qp：要还原表的数据文件

三、恢复

1. 和业务确认还原库表信息及时间点

## 例如：这里要还原至192.168.1.1:3306库的2021-03-01 17:10:00时间点
需还原信息：
主库IP：192.168.1.1
端口：3306
库名：db_test
表名：restore_test
时间：2021-03-01 17:10:00

2. 新建临时实例并清空数据目录内容

​ 每个公司内部应该都有不同的创建空实例的方法和基础配置，按照从库模板创建个空实例即可

## 假设创建空实例的命令为mysql_install
### 按照基础模板创建即可
mysql_install --port 33061 --version 57 ...

## 删除数据目录文件，也就是my.cnf配置文件中指定的datadir路径
rm -fr /path/to/datadir/*

3. 从备份中获取所需文件

## 新建一个恢复目录，防止进行整库恢复
mkdir /tmp/restore3306
## 假设备份路径为/path/to/backup
### 1. 获取公共文件
cp -r /path/to/backup/data/{mysql,performance_schema,ibdata1.qp} /tmp/restore3306

### 2. 获取点位文件
cp -r /path/to/backup/data/{xtrabackup_binlog_info,xtrabackup_checkpoints,xtrabackup_info,xtrabackup_logfile.qp,xtrabackup_slave_info,backup-my.cnf} /tmp/restore3306

### 3. 获取恢复表文件
mkdir /tmp/restore3306/db_test
cp -r /path/to/backup/data/db_test/restore_test* /tmp/restore3306/db_test/

​ 截止到目前，恢复路径/tmp/restore3306的目录结构如下：

# ls -l
total 4550652
-rw-r--r-- 1 root root        263 Dec  9 16:55 backup-my.cnf
drwxr-xr-x 2 root root         77 Dec  9 17:04 db_test
-rw-r----- 1 root root 1173915128 Dec  9 16:55 ibdata1.qp
drwxr-xr-x 2 root root       4096 Dec  9 16:54 mysql
drwxr-xr-x 2 root root       4096 Dec  9 16:54 performance_schema
-rw-r--r-- 1 root root         27 Dec  9 16:55 xtrabackup_binlog_info
-rw-r----- 1 root root        103 Dec  9 16:55 xtrabackup_checkpoints
-rw-r--r-- 1 root root       1038 Dec  9 16:55 xtrabackup_info
-rw-r----- 1 root root 3485918050 Dec  9 16:55 xtrabackup_logfile.qp
-rw-r--r-- 1 root root         78 Dec  9 16:55 xtrabackup_slave_info

4. 解压缩

​ 这里由于备份时进行了压缩，所以恢复时需要进行解压缩，否则需要跳过这一步

## 使用16个线程进行解压，可根据服务器压力以及恢复需求紧急情况调整
innobackupex --decompress --parallel=16 /tmp/restore3306/

5. 应用redo日志

## 使用16G内存进行apply log，如果数据量较大，且需要快速恢复，可酌情况调整
innobackupex --use-memory=16G --apply-log /tmp/restore3306/

6. 修改配置文件

​ 由于我们是进行单表恢复，所以恢复后追主从延迟需要在配置文件中设置只对恢复表进行同步操作

## 在[mysqld]中添加这一行，需指定为对应表的库名.表名格式
vim /path/to/mysql/my.cnf
[mysqld]
...
replicate-wild-do-table = db_test.restore_test

...

7. 拷贝数据并启动MySQL实例

## 根据MySQL配置文件将对应的备份文件恢复到数据目录下
innobackupex --defaults-file=/path/to/mysql/my.cnf --move-back /tmp/restore3306
## 恢复后数据目录下的文件为系统操作用户权限，有可能不是mysql用户权限
chown -R mysql.mysql /path/to/datadir
## 启动MySQL实例
mysqld_safe --defaults-file=/path/to/mysql/my.cnf &

8. 获取初始点位

## 若在从库备份，则查看/tmp/restore3306/xtrabackup_slave_info文件找到备份点位，也就是恢复的初始点位
## 若在主库备份，则查看的是/tmp/restore3306/xtrabackup_info文件
cat /tmp/restore3306/xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.008391', MASTER_LOG_POS=845521553

9. 挂载从库

-- 登录进实例中进行change master to，假设恢复的临时实例端口为33061
mysql -h127.0.0.1 -P33061 -uroot -pxxxxxx
CHANGE MASTER TO
  MASTER_HOST='192.168.1.1',
  MASTER_USER='xxx',
  MASTER_PASSWORD='xxx',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.008391',
  MASTER_LOG_POS=845521553;
  
-- 此时一定不能start slave开启主从同步

10. 主库获取误操作时间点位

​ 在主库获取当时误操作时间的binlog点位信息

## 需要恢复到的时间为2021-03-01 17:00:00，可根据时间以及业务提供误操作SQL解析当时的binlog并获取误操作点位
mysqlbinlog --base64-output='decode-rows' --start-datetime='2021-03-01 17:00:00' --stop-datetime='2021-03-01 17:20:00' mysql-bin.008394 > restore.log
## 假设最终找到点位为：mysql-bin.008394    end_log_pos 539183745

11. 开始主从同步

-- 开启IO线程获取主库binlog信息
START SLAVE IO_THREAD;
-- 开启SQL线程只同步至找到的误操作点位
start slave SQL_THREAD until MASTER_LOG_FILE='mysql-bin.008394',MASTER_LOG_POS=539183745;

12. 状态查看

-- 使用show slave status查看同步状态
show slave status\G
-- 直到Seconds_Behind_Master变为NULL，SQL线程停止表示已经恢复至当时误操作节点，此时恢复完成

四、业务数据恢复

​ 此时可以将临时节点的IP和端口信息发送给业务，业务根据这个临时实例和线上数据比对恢复误操作数据，由于我们也恢复了备份的mysql等公共基础信息，所以业务连接临时实例的用户名、密码等信息和线上一致。

PS：

• 若binlog比较大，主从延迟比较严重，可用set global innodb_flush_log_at_trx_commit = 0将innodb_flush_log_at_trx_commit参数改为0使主从同步快一些
• 恢复完成后只有SQL线程会自动关闭，记得使用stop slave;将IO线程也关闭掉，防止该临时节点一直同步主库的binlog