《MySQL》备份与恢复

备份和恢复的类型

物理备份与逻辑备份

物理备份保存的是存储数据库内容的目录和文件的原始副本。这种类型的备份适用于需要在出现问题时快速恢复的大型重要数据库。

逻辑备份保存的是表示数据库结构和数据内容的信息，如 CREATE DATABASE,CREATE TABLE 语句和 INSERT 语句。这种类型的备份适用于较小的数据量，您可以在其中编辑数据值或表结构，或者在不同的计算机体系结构上重新创建数据。

在线备份与离线备份

在线备份在 MySQL 服务器运行时进行，以便可以从服务器获取数据库信息。离线备份在服务器停止时进行。这种区别也可以描述为“热”备份与“冷”备份；“热”备份是指在外部访问数据库文件时，服务器保持运行，但在外部访问数据库文件时，不允许修改数据。

在线备份方法具有以下特征：

• 备份对其他客户端的干扰较小，其他客户端可以在备份期间连接到MySQL服务器，并且可以根据需要执行的操作来访问数据。

• 必须小心施加适当的锁定，以便不会发生会损害备份完整性的数据修改。

离线备份方法具有以下特征：

• 客户端可能会受到不利影响，因为备份期间服务器不可用。 因此，此类备份通常来自复制从属服务器，可以脱机而不会损害可用性。

• 备份过程更简单，因为不会受到客户端活动的干扰。

在线和离线之间的类似区别适用于恢复操作。 但是，与在线备份相比，客户端更有可能受到在线恢复的影响，因为恢复需要更强的锁定。 在备份期间，客户端可能能够在备份数据时读取数据。 恢复修改数据而不仅仅是读取数据，因此必须防止客户端在恢复数据时访问数据。

完整备份与增量备份

完整备份包括 MySQL 服务器在给定时间点管理的所有数据。 增量备份包括在给定时间跨度内（从一个时间点到另一个时间点）对数据所做的更改。

MySQL 有不同的方法来执行完整备份，例如前面所述的那些。通过启用服务器的二进制日志（服务器用于记录数据更改），可以实现增量备份。

完整备份和增量备份相结合，恢复至完整备份时的状态后，利用增量备份可以完全恢复自完整备份以来所做的增量变化，使服务器恢复至最新状态

MySQL Enterprise Backup

MySQL 企业版可以使用 MySQL Enterprise Backup 产品对整个数据库实例、选定的数据库、数据表，使用热备份的方式进行物理备份，此产品包括增量备份和压缩备份的功能。

复制文件进行物理备份

备份

可以通过复制表文件来备份 MyISAM 表。要获得一致的备份，需停止服务器或锁定并刷新相关表后再进行复制操作：

FLUSH TABLES tbl_list WITH READ LOCK;

只需要一个读锁即可，这样一来其他客户端可以在复制数据库目录中的文件时继续查询表。 需要刷新以确保在开始备份之前将所有活动索引页写入磁盘。

对于含有 InnoDB 表的库，官方说的是不能通过复制文件进行备份，因为 InnoDB 可能仍然有修改过的数据缓存在内存中，而没有刷新到磁盘。
如果仅仅是这个原因，使用 FLUSH TABLES tbl_list WITH READ LOCK 主动刷新并锁表即可。
通过实际操作以及结合网上的说法，除了备份所有的表空间文件（*.ibd）外，还需要备份所有的共享表空间（ibdata1）、日志文件、配置文件等内容。最好是备份整个数据目录，恢复时完整恢复。

恢复

只需将文件复制会原来的位置即可

mysqldump 逻辑备份

mysqldump 命令把数据库备份成一个文件，该文件包含表示数据库表结构和数据内容的语句，如 CREATE INSERT 等。

mysqldump 支持的选项

过滤选项

不使用任何过滤选项时 mysqldump 将命令行上的第一个名称参数视为数据库名称，将后续名称视为表名称。

1. --all-databases

   备份所有数据库。使用此过滤选项后，不需要名称参数。

2. --databases

   备份指定的一个或几个数据库。使用此选项，将所有名称参数视为数据库名称

连接选项

1. --host=host_name -h host_name

   从给定主机上的 MySQL 服务器转储数据。 默认主机是 localhost

2. --port=port_num -P part_num

   用于连接的端口号。默认端口号是 3306

3. --user=user_name -u user_name

   连接到服务器时使用的 MySQL 用户名。

4. --password[=password] -p[password]

   连接服务器时使用的密码。 如果使用短选项 -p ，则选项和密码之间不能有空格。 使用明文密码不安全，建议在命令行中省略密码，在后续的交互中输入密码

与文件有关的选项

1. --defaults-file=file_name

   仅使用给定的配置文件。如果文件不存在或无法访问，则会发生错误。 file_name 如果作为相对路径名而不是完整路径名给出，则表示相对于当前目录

DDL 选项

1. --add-drop-database

   在每一个 CREATE DATABASE 语句前加上 DROP DATABASE 语句

2. --add-drop-table

   在每一个 CREATE TABLE 语句前加上 DROP TABLE 语句

3. --add-drop-trigger

   在每一个 CREATE TRIGGER 语句前加上 DROP TRIGGER 语句

格式选项

1. --result-file=file_name -r file_name

   直接输出到指定的文件。 即使在生成转储时发生错误，也会创建文件并覆盖其先前的内容。

   > file_name 生成的文件内部使用换行符 \r\n，而此选项生成的文件内部使用换行符 \n

   在 Windows 上建议使用此选项代替 > file_name，以防止换行符 \n 转换为 \r\n

事务选项

1. --add-locks

   在每一个表转储前后加上 LOCK TABLES 和 UNLOCK TABLES 语句

   默认开启，使用 --skip-add-locks 禁用

2. --lock-tables

   通过锁定所有表为只读来创建一致性快照。通过常规查询日志可以发现，此选项是使用 LOCK TABLES tb1 READ LOCAL,tb2 READ LOCAL 锁定单个 database 中的所有表。

   如果数据库中存在 MyISAM 引擎的表，在转储的过程中，此选项允许 MyISAM 表并发插入，不能完全保障一致性

   如果一次转储多个 database 时，每次锁定一个 database 下的所有表，未锁定的库仍可以继续更新操作，需要使用 --flush-logs 选项刷新二进制日志，结合二进制日志来保障一致性不被破坏

   默认开启，使用 --skip-lock-tables 禁用

3. --lock-all-tables

   通过锁定所有表为只读来创建一致性快照。通过常规查询日志可以发现，此选项是使用 FLUSH TABLES WITH READ LOCK 一次性锁定所有 database 下的所有表。

   使用此选项，即使是 MyISAM 表也会被禁止并发插入，可以保障一致性

   使用此选项后，将自动被关闭 --lock-tables（因为不需要重复锁表）和 --single-transaction （因为不再需要事务隔离）

4. --single-transaction

   通过转储单个事务中的所有表来创建一致性快照。此选项将事务隔离模式设置为 REPEATABLE READ 并在转储数据之前将 SQL 语句 START TRANSACTION 发送到服务器，转储的内容是发送 START TRANSACTION 时数据库的内容，而不会阻塞任何其他操作

   仅适用于存储在支持事务的存储引擎中的表（目前只有 InnoDB 支持），不能保障其他存储引擎表的一致性

   使用此选项将自动关闭 --lock-tables（因为锁表会导致事务隐式提交，况且也不需要再锁表）

   在使用 --single transaction 导出时，其他连接不应使用以下语句：ALTER table、DROP table、RENAME table、TRUNCATE table。因为一致性快照不会与它们隔离，使用这些语句会导致 mysqldump 程序获取到变更后的数据内容

5. --flush-logs

   启动转储之前刷新服务器中的二进制日志文件（二进制日志会切换到新的日志文件）。刷新二进制日志文件可以更好进行增量备份（新的二进制日志文件中的操作即为本次转储后对数据库所做的所有更改）

   如果一次转储多个数据库（使用选项 --databases 或 --all-databases），则转储每个 database 前均刷新一次日志。因为默认开启的 --lock-tables 选项每次只锁一个 database，如果只刷新一次日志，将无法区分新的二进制日志文件中的哪些是增量操作，哪些是已经转储进备份文件的操作

   这里啰嗦一个例子：假如限制对 a b c d e 五个数据库进行转储，它们将依次被锁定，并在锁定的同时新增一个二进制日志文件（假定锁定 a 时新增的文件为 A，以此类推）

   1. 锁定 a 时，b c d e 库未锁定，文件 A 中会写入对 b c d e 库的操作。因为 b c d e 库目前尚未转储，这些操作后续还会被转储，不必作为增量
   2. 释放 a ，锁定 b 时，a c d e 库未锁定，文件 B 中会写入对 a c d e 库的操作。因为 c d e 库目前尚未转储，这些操作后续还会被转储，不必作为增量；而对 a 库的操作因为 a 库已经完成转储，这些操作将成为增量
   3. 释放 b ，锁定 c 时，a b d e 库未锁定，文件 C 中会写入对 a b d e 库的操作。因为 d e 库目前尚未转储，这些操作后续还会被转储，不必作为增量；而对 a b 库的操作因为 a b 库已经完成转储，这些操作将成为增量
   4. 释放 c ，锁定 d 时，a b c e 库未锁定，文件 D 中会写入对 a b c e 库的操作。因为 e 库目前尚未转储，这些操作后续还会被转储，不必作为增量；而对 a b c 库的操作因为 a b c 库已经完成转储，这些操作将成为增量
   5. 释放 d ，锁定 e 时，a b c d 库未锁定，文件 E 中会写入对 a b c d 库的操作。对 a b c d 库的操作因为 a b c d 库已经完成转储，这些操作将成为增量
   6. 释放 e 完成转储，a b c d e 库未锁定，文件 E 中会继续写入对 a b c d e 库的操作。这些操作都将成为增量
      综上，文件 B 中对 a 库的操作，文件 C 中对 a b 库的操作，文件 D 中对 a b c 库的操作，文件 E 中的所有操作，这些操作全都是本次转储之后的增量操作

   如果同时使用了 --lock-all-tables 或 --master-data 或 --single-transaction，二进制日志则只在 FLUSH TABLES WITH READ LOCK 锁表的同时刷新一次日志。

   备份过程中其他客户端对数据库进行的更新会以二进制日志的形式记录，因此此参数往往结合 --flush-logs 参数一起使用，新生成的二进制日志即为增量更新的内容，定期做增量备份。

性能选项

1. --quick

   此选项对于转储大型表非常有用。 它强制 mysqldump 一次一行地从服务器检索表的行，而不是检索整个行集并在写出之前在内存中缓冲它

复制选项

1. --master-data[={1 | 2}]

   此选项主要用来转储主服务器的数据库来生成转储文件，该文件可用于将其他服务器设置为主服务器的从属服务器。它使转储输出包含一个CHANGE MASTER TO 语句，该语句指示主服务器的二进制日志坐标（文件名和位置）。在将转储文件加载到从服务器后，从服务器应从坐标位置开始复制，以此来实现主从同步

   如果选项值为2，则该 CHANGE MASTER TO 语句将写为 SQL 注释，因此仅提供信息; 重新加载转储文件时没有任何效果。如果选项值为1，则该语句不会写为注释，并在重新加载转储文件时生效。 如果未指定选项值，则默认值为1。

   此选项将自动启用 --lock-all-tables，并自动关闭 --lock-tables

   如果同时使用了选项 --single-transaction，全局读取锁定将只在转储开始时占用很短的时间

   在任何情况下，对日志的任何操作都将在转储的同时进行。

2. --delete-master-logs

   完成转储后删除主服务器的二进制日志（可能会很危险，因为从服务器可能尚未完全处理二进制日志的内容）。使用该选项将自动启用 --master-data 选项。

还原逻辑备份

mysql [dbname] < file_name

dbname 表示数据库名称，为可选参数，如果 file_name 文件中包含创建数据库的语句（使用 --all-databases 或 --databases 选项导出的），则不必指定数据库名