MySQL备份恢复 | 国家一级保护废物

对个人乃至企业来说，数据都是至关重要的。不小心丢失或误操作都会造成不可估量的损失，但对数据的操作是无法避免的。数据损坏之后如何补救则成为了数据运维中非常重要的一部分。本文通过常用的 MySQL 数据库介绍数据的备份恢复基础概念及具体操作。

本文主要分为以下三部分：

数据备份
数据恢复
总结

一、数据备份

1.1 数据备份的意义

对数据的威胁通常很难提前预知防范。数据备份的作用就在于灾难发生后，可通过备份数据完整、快速、简单、可靠的恢复原有数据。

完整的备份系统包含备份数据的管理以及数据的全面恢复，专业的备份系统不仅保证了数据的正确性、有效性、安全性，也是防止主动型信息攻击的一道防线。

1.2 数据备份的分类

数据备份从不同的维度可区分为以下内容：

根据备份类型区分：
- 热备：在数据库运行过程中直接备份，不会影响正在运行的数据库。MySQL 官方手册中称之为 Online Backup (在线备份)
- 冷备：在数据库停止的状态下备份，一般只需要拷贝相关的数据物理文件即可。MySQL 官方手册中称之为 Offline Backup (离线备份)
- 温备：在数据库运行过程中备份，但会对当前数据库的操作产生影响，例如添加一个全局读锁保证备份数据的一致性
根据备份方式区分：
- 逻辑备份：备份表内具体数据，一般备份为可读的 SQL 文件。例如：mysqldump、into outfile 等方式
- 物理备份：基于数据文件进行备份。例如：cp、xtrabackup 等方式
根据备份策略区分：
- 全量备份：备份整个数据库数据
- 增量备份：备份上一次备份(全量或增量备份)以后的变化数据
- 差异备份：备份上一次全量备份以后的变化数据

不同备份策略优缺点

1.3 数据备份的方式

MySQL 官方及第三方都提供了不同的备份方法，常见的备份方式主要为以下几种：

Percona xtrabackup 以其备份快速可靠、数据压缩节省磁盘空间、更短的恢复时间等优势获得了人们的青睐。xtrabackup 可通过下面命令进行数据库的全量备份：

./bin/innobackupex \
--defaults-file=/path/to/dbconfig/my.cnf \
--user=db_user \
--password=db_pass \
--socket=/path/to/socket/mysql.sock \
--compress \
--compress-threads=4 \
--parallel=4 \
/MySQL_BACKUP/

其余备份命令参数可参考 Percona-xtrabackup 官网

二、数据恢复

2.1数据恢复的方法

在 MySQL 中一般基于全量备份 + binlog 日志的方式恢复出一份有效可靠的数据，但不同的故障场景下需要恢复出的数据需求则可能不同。例如：机器故障后需要全量恢复数据库实例；针对某些表发生了误操作则恢复对应表即可，使业务尽快获得一份完整的正确数据，减少故障时间。

下面以 Percona xtrabackup 备份工具为例描述如何进行数据的恢复操作。

2.2 全量恢复和单表恢复

使用 Percona 工具备份会得到下图中所列的数据文件。

基于某张表的数据文件 ( t1.frm 和 t1.ibd )恢复，新建实例中仅有 t1 表的数据，称为单表恢复
基于实例中所有库表数据文件恢复则称为全量恢复。

2.3 单表恢复

确认还原库表信息及时间点

## 例如：这里要还原至 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

创建临时实例

## 临时实例目录如下：
[root@localhost mysql]$ tree .  
.├── etc
 │   ├── my.cnf
 ├── log
 ├── tmp
 └── var

基础数据库架构包含 etc 配置目录，里面包含了数据库的配置文件；log 日志目录、tmp 临时目录以及var 数据目录

获取备份文件

## 新建一个恢复目录，防止进行整库恢复
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

解压缩(备份时未压缩需省略该步骤)

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

应用redo日志

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

设置数据库配置(全量恢复需省略)

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

拷贝备份数据启动临时实例

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

获取恢复初始点位

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

### 主从基于GTID复制
cat xtrabackup_infobinlog_pos = filename 'mysql-bin.000079', position '7372137', GTID of the last change '7acc0fb9-505a-11e9-aed3-246e96be16a0:1-66040'

挂载至集群主库

-- 登录进实例中进行change master to，假设恢复的临时实例端口为33061
-- 基于binlog file挂载
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;  

-- MySQL5.7以上版本基于GTID挂载
stop slave;
reset slave;
reset master;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
set global gtid_purged='7acc0fb9-505a-11e9-aed3-246e96be16a0:1-66040';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

CHANGE MASTER TO  
	MASTER_HOST='192.168.1.1',  
	MASTER_USER='xxx',  
	MASTER_PASSWORD='xxx',  
	MASTER_PORT=3306,  
	master_auto_position=1;
	
-- 此时一定不能start slave开启主从同步，否则误操作位置也会同步至新建实例

获取恢复时刻主库点位

## 以MySQL5.6基于binlog file复制为例
## 需要恢复到的时间为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

开启主从同步

-- 开启IO线程获取主库binlog信息
START SLAVE IO_THREAD;
-- 开启SQL线程只同步至找到的误操作点位

-- 基于binlog file同步
start slave SQL_THREAD until MASTER_LOG_FILE='mysql-bin.008394',MASTER_LOG_POS=539183745;

-- 基于GTID同步
START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS = 3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56;

恢复完成

-- 使用show slave status查看同步状态
show slave status\G

-- 直到Seconds_Behind_Master变为NULL，SQL线程停止表示已经恢复至当时误操作节点，此时恢复完成
-- 恢复完成后需 stop slave;停止同步，否则IO线程会不断获取主库日志

三、总结

数据库备份是一种防患于未然的手段，完整有效、安全可靠的备份数据有利于提高数据库的容错及可恢复性，基于备份数据的恢复是数据库系统崩溃时所提供的恢复代价最小的优解。所以，保证备份的准确可用是个人甚至公司必要且重要的事情。