MySQL备份和恢复

备份对于数据库至关重要，因为当数据库出现系统崩溃、硬件故障或者用户误删除数据时，可以利用备份进行恢复。另外，在升级 MySQL 之前进行备份也是一种必要的安全措施；而且备份还可以用于在另一台服务器上恢复 MySQL 环境，或者创建复制结构中的从节点。

MySQL 支持各种备份方法和策略，可以根据需求进行选择。

备份类型

备份可以按照不同的方式进行分类。

物理备份与逻辑备份

物理备份（Physical backup）由数据库目录和文件的副本组成，通常速度更快，适用于需要快速恢复的大型重要数据库。

逻辑备份（Logical backup）保存的是创建数据库（CREATE DATABASE）和生成数据（INSERT 语句或者文本文件）的 SQL 脚本，适合小型的数据库。

在线备份和离线备份

在线备份（Online backup）是指在 MySQL 服务器运行时进行的备份，也称为热备。热备时其他客户端可以连接到 MySQL 服务器，并且可以执行的数据操作；备份工具需要执行适当的锁定，以免数据修改操作损害备份的完整性。InnoDB 支持热备，MyISAM 不支持热备。

离线备份（Offline backup）是指 MySQL 服务器停止时进行的备份，也称为冷备。冷备时客户端无法连接服务器，因此通常使用主从复制结构的从节点进行备份，而主节点能够继续提供服务。

还有一种备份称为温备（Warm backup），是指服务器保持运行，客户端可以访问数据库但是不能修改数据。

全量备份和增量备份

全量备份（Full Backup）包含了一个 MySQL 实例在某一时间点的所有数据。

增量备份（Incremental Backup） 只备份上一次完全备份或增量备份之后改变的数据。MySQL 通过不断备份二进制日志文件（binary log）来达到增量备份的效果。

差异备份（Differential Backup） 只备份上一次完全备份之后改变的数据。

全量备份需要备份的内容较多，备份时较慢，但是恢复速度更快；增量备份每次备份的数据量较小，但是恢复时需要基于全量备份依次恢复增量部分，时间较长；差异备份位于两者之间。在实际环境中可以结合全量备份和增量/差异备份，以平衡备份和恢复所需的存储空间和时间。

完全备份和部分备份

完全备份（Whole Backup）需要备份完整的数据目录或者全部数据库。

部分备份（Partial Backup）可以选择备份部分文件或者某些的表。

对于物理备份，InnoDB 表如果使用单独的存储文件就可以执行部分备份；MyISAM 表拥有自己的数据文件，支持部分备份。对于逻辑备份，任何表都可以执行服务器级别、数据库级别或者表级别的备份。

快照备份

某些文件系统实现了“快照”功能，它们能够提供特定时间点的文件系统逻辑拷贝，而不需要整个文件系统的物理拷贝。例如，通过使用写时复制（copy-on-write）技术，只需要拷贝快照点之后修改的文件系统部分。MySQL 本身不支持执行文件系统快照的功能，但是可以通过第三方解决方案，例如 Veritas、LVM 或 ZFS。

全量恢复和增量恢复

全量恢复（Full Recovery）使用全量备份的文件将数据库还原到执行备份时的状态。备份时间点之后的数据变更无法通过还原进行恢复。

增量恢复（Incremental Recovery）基于全量备份文件还原到备份的时间点，然后再应用增量备份的二进制日志文件（binary log）将数据库恢复到最新状态或者指定时间点。这种方式也称为时间点恢复（point-in-time recovery）。

逻辑备份只能执行还原操作；物理备份加上二进制日志的增量恢复可以将数据库恢复到指定的时间点。

备份恢复工具

mysqldump

mysqldump 工具用于执行 MySQL 逻辑备份，可以备份单个或者全部数据库。mysqldump 默认导出一个或多个 SQL 文件，但是也支持输出 CSV、其他分隔符的文本文件或者 XML 文件。

mysqlpump

mysqlpump 是 MySQL 5.7 之后引入的逻辑备份工具，是 mysqldump 的增强版；支持更多的功能，例如并行备份、包含或排除指定对象（数据库、表、、触发器、用户等）、生成压缩文件、显示备份进度以及更多的控制选项。

Xtrabackup

Xtrabackup 是 Percona 公司开发的开源备份工具，支持在线的物理热备。

MySQL Enterprise Backup

MySQL Enterprise Backup（mysqlbackup）是 MySQL 官方提供的商业备份工具，支持 InnoDB 和其他存储引擎表的物理备份，以及 MyISAM 表的文件系统备份命令（cp、scp、tar、rsync）。

对于 InnoDB 表，MySQL Enterprise Backup 执行在线热备；对于 MyISAM 或者其他存储引擎执行温备。

myisamchk

myisamchk 工具可以用于 MyISAM 表的分析、错误检查、修复以及优化。运行 myisamchk 时需要确保没有其他的程序使用相应的表；最好的方式是关闭 MySQL 服务器，或者锁定 myisamchk 访问的表。

备份策略

如果数据量不大，可以每天晚上做一次全备，物理备份或者逻辑备份都可以。

定期执行全备，例如周日全备；然后每天增量备份。可以将备份命名写成一个脚本，利用操作系统的定时任务进行备份。

逻辑备份与还原

我们介绍如何使用 mysqldump 工具进行逻辑备份，mysqlpump 工具的使用方式几乎也是一样。

使用 mysqldump 备份数据库

mysqldump 工具位于 MySQL 安装目录下的 root/bin 目录中。使用以下命令备份所有的数据库：

mysqldump -u user_name -p -r file_name --all-databases

其中，user_name 表示用户名；file_name 表示备份文件名；--all-databases 表示备份所有的数据库。

我们也可以通过指定数据库名导出特定的数据库：

mysqldump -u user_name -p -r file_name --databases db_name1 db_name2

其中，--databases 表示需要导出的数据库，可以备份一个或多个数据库。 

使用 mysqldump 备份数据表

mysqldump 除了导出数据库之外，也可以选择导出指定的表：

mysqldump -u user_name -p -r file_name db_name table1 table2

以上命令表示导出数据库 db_name 中的表 table1 和 table2。

使用 mysqldump 备份数据库结构

使用以下命令导出所有数据库的对象，不包含数据：

mysqldump -u user_name -p -r file_name --no-data --all-databases

其中，--no-data 表示不导出任何表数据。另外，--no-create-info 不会导出 CREATE TABLE 语句，可以用于只导出数据。

mysqldump 工具支持大量的控制选项，可以使用 --help 或 -? 选项查看，或者参考官方文档。

使用 mysql 工具还原数据库

逻辑备份的默认格式就是 SQL 文件，可以直接通过 mysql 工具执行还原：

mysql -u user_name -p < file_name

其中，file_name 就是 mysqldump 备份导出的文件。如果备份导出的是分隔符文件，例如 CSV，可以通过 LOAD DATA 语句或者 mysqlimport 客户端还原。

物理备份与恢复

使用 Xtrabackup 执行全备

Xtrabackup 提供了二进制安装和源码编译安装，我们使用 YUM 源进行安装：

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

然后启用该 YUM 源：

percona-release enable-only tools release

最后使用 yum 命令安装 Xtrabackup： 

yum install percona-xtrabackup-80

安装之后就可以执行备份操作了，我们可以创建一个专用的备份账户并且授予相应的权限： 

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'Pswd123!';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';

执行备份之前，我们创建一个测试表：

CREATE TABLE test (id int);
INSERT INTO test values (1);

以下命令用于执行一个物理全备：

xtrabackup -u bkpuser -p --backup --target-dir=/data/full_backups/

其中，--backup 表示执行备份操作；--target-dir 指定了一个备份目录，这里是 /data/backups/。备份完成后可以看到类似以下数据：

ls /data/full_backups/
backup-my.cnf  ib_buffer_pool  mysql.ibd           undo_001.qp             xtrabackup_checkpoints
binlog.000050  ibdata1         mysql.ibd.qp        undo_002                xtrabackup_info
binlog.index   ibdata1.qp      performance_schema  undo_002.qp             xtrabackup_logfile
ds             mydb            sys                 world_x                 xtrabackup_logfile.qp
hrdb           mysql           undo_001            xtrabackup_binlog_info  xtrabackup_tablespaces

其中的 xtrabackup_checkpoints 文件记录了本次备份的信息：

backup_type = full-backuped
from_lsn = 0
to_lsn = 605477605
last_lsn = 605477615
flushed_lsn = 0

to_lsn 表示备份到了 InnoDB 的日志序列号 605477605。

使用 Xtrabackup 执行增量备份

Xtrabackup 支持 InnoDB 的增量备份，增量备份需要基于之前的全备或者增量备份。我们先为 test 表增加一些数据：

INSERT INTO test values (2);
INSERT INTO test values (3);

现在，我们基于上面的全备执行增量备份：

xtrabackup -u bkpuser -p --backup --target-dir=/data/inc1 --incremental-basedir=/data/full_backups

其中，--incremental-basedir 表示本次备份的基础备份集；再次查看 /data/inc1/xtrabackup_checkpoints 文件：

backup_type = incremental
from_lsn = 605477605
to_lsn = 605477635
last_lsn = 605477645
flushed_lsn = 0

其中的 from_lsn 就是上次全备的最后一个检查点。

我们可以使用相同的方法继续基于全备执行增量备份（差异备份），或者基于 /data/inc1 执行增量备份。

使用 Xtrabackup 执行全备还原

我们利用上面的全备执行还原操作，首先需要利用 Xtrabackup 准备好备份数据：

xtrabackup -u bkpuser -p --prepare --target-dir=/data/full_backups/

其中，--prepare 用于准备备份数据，同步日志中已提交的事务并回滚未提交的事务，使得备份数据处于一致性状态。我们可以看到类似以下输出：

Shutdown completed; log sequence number 605477900
200313 15:53:13 completed OK!

然后执行还原操作

xtrabackup -u bkpuser -p --copy-back --target-dir=/data/full_backups/

需要注意，MySQL 的数据目录必须清空；否则将会提示以下错误：

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql 
xtrabackup: recognized client arguments: --user=bkpuser --password --copy-back=1 --target-dir=/data/backups/ 
Enter password: 
xtrabackup version 8.0.9 based on MySQL server 8.0.18 Linux (x86_64) (revision id: c5cbbe4)
Original data directory /var/lib/mysql is not empty!

同时，正在运行的 MySQL 服务必须关闭：

service mysqld stop

执行完还原操作之后，我们需要确保 MySQL 数据目录的拥有者修改为 mysql：

chown -R mysql:mysql /var/lib/mysql

然后，启动 MySQL 后台服务： 

service mysqld start

我们验证此时 test 表中的数据：

SELECT *  FROM test;
id|
--|
 1|

此时只有 1 条记录，全备之后的数据没有恢复。接下来我们通过增量备份将数据库恢复到最新状态。 

使用 Xtrabackup 执行时间点恢复

执行时间点恢复之前同样需要准备好备份文件，不过参数略有不同：

xtrabackup -u bkpuser -p --prepare --apply-log-only --target-dir=/data/full_backups

以上命名用于准备基础的全备数据，--apply-log-only 选项用于同步日志中已提交的事务，但是不回滚未提交的事务（这些事务可能在后面的增量备份中提交）。

接下来准备增量备份文件：

xtrabackup -u bkpuser -p --prepare --target-dir=/data/full_backups --incremental-dir=/data/inc1

由于 /data/inc1 是最后一个增量备份，我们不需要 --apply-log-only 选项；否则，可以依次使用 --apply-log-only 选项准备好所有的增量备份。

解析来的操作和全备的恢复操作一样：

xtrabackup -u bkpuser -p --copy-back --target-dir=/data/full_backups/

我们再次验证 test 表中的数据： 

SELECT *  FROM test;
id|
--|
 1|
 2|
 3|

test 表恢复到了最新的状态。

自动实例恢复

如果文件系统或者硬件故障导致 MySQL 磁盘数据损坏，需要修复磁盘问题并且利用备份进行恢复。

如果使用了 InnoDB 存储引擎（支持事务和自动故障恢复），并且发生了操作系统或者电源故障但是磁盘没有损坏；InnoDB 可以通过 redo 日志查找已经提交但未刷新到数据文件的事务，将其刷新到数据文件；同时回滚日志中未提交的事务。整个实例的恢复过程由 InnoDB 自动执行，用户可以通过 MySQL 错误日志查看相关的信息。例如：

InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

 

参考：

• 备份与恢复