09MySQL数据库的备份与恢复

数据库备份是指通过导出数据或者复制表文件的方式来制作数据库的副本。当数据库出现故障或者误操作时，可以将备份的数据库加载到系统中，从而使数据库恢复到正常状态。
对数据库进行备份是非常重要的，当系统发生故障或者误操作库后，对于已有备份的数据库，你不用担心数据会丢失，因为你可以恢复和再一次运行你的数据。MySQL 提供了多种备份策略，我们要根据实际情况来选择适合的备份策略。

备份按模式分为逻辑备份和物理备份。

逻辑备份

逻辑备份是将数据库中表的数据以文本文件形式备份。

在 MySQL 数据库中有一个自带的逻辑备份工具 mysqldump，用这个工具来备份是把数据库从 MySQL 库中以逻辑的 SQL 语句形式直接输出或生成备份文件。我们可以使用 mysqldump -help 来查看该工具的使用方法。也可以使用 mysqldump --help 来查看更多关于 mysqldump 的内容，如果感兴趣，可以阅读 mysqldump 命令详解

备份指定数据库的多个表

mysqldump [主机号][端口号][用户名][密码][数据库名][表1][表2] > [文件名].sql

例如，我们把 test 数据库中的 t1、t2 这两张表备份到桌面上。

mysqldump -u root test t1 t2 > /home/shiyanlou/Desktop/info.sql

备份指定一个或者多个数据库

mysqldump [主机号][端口号][用户名][密码] --databases [数据库名1][数据库名2] > [文件名].sql

例如，创建了 a 和 b 两个数据库，我们把这两个数据库的数据备份到桌面上。

mysqldump -u root --databases a b > /home/shiyanlou/Desktop/a_b.sql

这时我们的桌面上会对应生成一个 a_b.sql 的文件。

备份所有的数据库

mysqldump [主机号][端口号][用户名][密码] --all-databases > [文件名].sql

例如，我们现在备份所有的数据库，把备份文件保存到桌面上。

mysqldump -u root --all-databases > /home/shiyanlou/Desktop/all_databases.sql

这时桌面上会看到 all_databases.sql 这个文件。

在日常备份数据库，是不会把数据备份到桌面上的，你应该在 mysql 的文件夹下，新建一个文件夹来专门存放备份文件。
除了 MySQL 自带的工具进行逻辑备份外，我们还可以使用 mydumper 进行逻辑备份。mydumper 是一个多线程的备份工具，它通过增加线程数，提高了备份的速度。在用法上与 mysqldump 相差无几，但还是有不同的地方，它的不同之处在于，mydumper 为每个表创建一个或者多个文本文件，方便并行备份和并行恢复数据。mydumper 的功能会更多一些，比如，它可以进行正则匹配备份、多线程备份等，当数据库中的数据相当多时，使用 mydumper 会看出运行速度会快很多。但是一般数据量的话，两者在性能上相差无几。若要使用它，我们需要安装，由于在日常应用中，使用较多的还是 mysqldump，所以这里就不具体讲解了，感兴趣的同学可以阅读 mydumper 介绍及使用。

物理备份

物理备份是以磁盘块为单位的备份。物理备份又可以分为热备份、冷备份。

• 热备份是数据库在运行情况下，备份数据文件同时产生二进制日志的备份方法，也就说热备份是系统处于正常运转状态下的备份。
• 冷备份是停掉数据库服务的备份，一般很少用此方法，因为很多应用不允许长时间停机。

在 MySQL 中使用热备份，对于不同的存储引擎来说，进行热备份的方式也有所不同。
在 InnoDB 中进行热备份一般有两种方法。一种是使用表空间的迁移技术，另一种是使用专门的热备份工具。接下来为大家介绍这两种热备份方法的使用吧。

表空间迁移技术

在迁移过程中，表仅处于只读状态，也就是说要求 innodb_file_per_table 的参数值为 ON。我们可以使用 SHOW VARIABLES LIKE '%PER_TABLE%' 来查看一下目前系统中独立表空间的参数。如果它的参数为 OFF，我们可以使用 SET GLOBAL innodb_file_per_table=ON 来设置。

下面我们通过一个例子来讲解如何利用表空间迁移技术来备份。在本次例子中，我们会用到一个名为 animal 的数据库，去备份该数据库中 pet 表的数据。

# 切换到根目录下
sudo -i

# 进入 animal 数据库目录下
cd /var/lib/mysql/animal

# 查看 pet 表中的所有文件
ll pet.*

.ibd 是保存表的数据和索引文件，.frm 是保存了表的元数据和表的结构。
如果不知道自己的 mysql 数据存放路径，我们可以使用 SHOW VARIABLES LIKE '%DIR%'来查看，其中的参数 datadir 就是数据文件的存储路径啦。
使用 FLUSH TABLES <表名> FOR EXPORT 来锁定表。该表执行此操作后，不能再执行增删改查的操作了。

USE animal
FLUSH TABLES pet FOR EXPORT;

现在我们开始备份 .ibd，在这个文件中存放着我们的表数据。

# 进入桌面
cd /home/shiyanlou/Desktop
# 在桌面上创建一个名为 backup 的文件夹
mkdir backup
# 数据备份到 backup 文件夹中
cp /var/lib/mysql/animal/pet.ibd /home/shiyanlou/Desktop/backup/

在热备份中，我们还可以使用 Xtrabackup 这个工具来进行备份。Xtrabackup 是一款基于 InnoDB 的免费在线热备份工具，同学们可以在官网进行下载。

按照备份数据库的内容来划分：

• 全量备份：对数据库进行一个完全备份。
• 增量备份：是在全量备份的基础上进行的，对于更改的数据进行备份。
• 日志备份：MySQL 数据库二进制日志的备份。

通过复制表文件做一个备份，对于使用它自己文件的存储引擎来说，表能够通过复制这些文件来备份。例如，MyISAM表能够作为文件被存储，通过复制这些文件，它是很容易备份的。你仅仅需要设置一个读锁，当其他用户查询表时，你在数据库对这些表做一个备份。通过复制所有的表文件，你能够创建一个二进制日志备份。

用二进制日志做备份，首先你需要使用 --log-bin 去启动服务器和开启二进制日志。

数据库恢复

数据库恢复是以数据库的备份为基础的，与备份相对应的系统维护和管理操作。系统在进行恢复操作时，先执行一些系统安全性的检查，包括检查所要恢复的数据库是否存在、数据库是否变化及数据库文件是否兼容等，然后根据数据库的备份类型采取相应的恢复措施。
用二进制日志进行恢复，我们需要用到 mysqlbinlog 这个工具，可以使用 mysqlbinlog -help 来看看它的参数介绍。

完全恢复

完全恢复：就是将备份作为输入执行，其语法结构如下所示：

mysql -u root [数据库名] < [恢复文件名]

这样恢复的数据并不完整，需要将备份后的日志进行重做，重做日志的语法格式如下：

mysqlbinlog [二进制日志文件名]|mysql -u root [数据库名]

例如，备份 animal 数据库中 food 表的数据，备份到 animal_food.sql 中，但是不小心删除了 food 表，利用完全恢复来重新找回数据。

1. 备份数据

   mysqldump -u root animal food > /home/shiyanlou/Desktop/animal_food.sql
   

2. 模拟误删操作

   DROP TABLE food;
   SHOW TABLES;
   

3. 恢复数据

   sudo -i
   mysql -u root animal < /home/shiyanlou/Desktop/animal_food.sql
   

   SHOW TABLES;
   SELECT * FROM food;
   

不完全恢复

接下来介绍两种不完全恢复，分别是指定时间点的恢复和指定位置的恢复。

若要指定恢复时间，首先保证该数据库已经有逻辑备份了。当误操作发生时，需要记住确定的误操作时间，使用 mysqlbinlog --no-defaults --stop-datetime 来恢复到误操作之前的时间点。
例如，我们误删除了 intern 表中的数据，现在需要使用指定时间的方式进行恢复。

1. 备份

   mysqldump -u root company intern employee > /home/shiyanlou/Desktop/intern_employee.sql
   

2. 模拟误删操作

   # 删除 intern 表中的一行数据，记住这个时间点
   DELETE FROM intern WHERE id = 1;
   

3. 恢复数据

   mysqlbinlog --no-defaults --stop-datetime='2020-6-8 16:23' /var/log/mysql/mysql-bin.000010 | mysql -u root
   

   最后，跳过误操作的时间点，使用 mysqlbinlog --no-defaults --start-datetime 来继续执行后面的二进制日志，这时恢复完成。

   mysqlbinlog --no-defaults --start-datetime='2020-6-8 18:00' /var/log/mysql/mysql-bin.000010 | mysql -u root
   

如果在同一时间执行了多条 sql 语句，这时候我们很难知道哪条语句是误操作。对于这个问题，我们可以使用指定位置的恢复来解决。
首先，我们需要知道误操作位置的行号，对于一般大小的日志文件可以使用下面的语句来查看误操作的行号：

SHOW BINLOG EVENTS

然后，恢复到误操作前的位置号，操作如下：

mysqlbinlog --stop-position

最后，跳过误操作的位置号，使用 mysqlbinlog --start-position 继续执行后面的二进制日志，完成该数据的恢复。 我们可以在 cd /var/log/mysql 中找到所有二进制日志的位置信息和时间信息。

其实我们还可以使用 myloader 来进行 mydumper 备份的数据恢复，它是 mydumper 配套的恢复工具，能够实现并行恢复数据。感兴趣可以阅读 MySQL Backup myloader。