参考官方文档：

https://dev.mysql.com/doc/refman/5.7/en/backup-strategy-example.html

本节讨论执行备份的过程，使您可以在发生多种类型的崩溃后恢复数据：

• 操作系统崩溃
• 断电
• 文件系统崩溃
• 硬件问题（硬盘，主板等等）

示例命令不包括诸如mysqldump和mysql客户端程序的--user和--password之类的选项。 您应该包含必要的选项以使客户端程序能够连接到MySQL服务器。

假设数据存储在InnoDB存储引擎中，该引擎支持事务和自动崩溃恢复。 假设MySQL服务器在崩溃时处于负载状态。 如果不是，则不需要恢复。

对于操作系统崩溃或电源故障，我们可以假设MySQL的磁盘数据在重新启动后可用。 由于崩溃，InnoDB数据文件可能不包含一致的数据，但InnoDB会读取其日志并在其中找到尚未刷新到数据文件的预提交和未提交事务的列表。 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

对于文件系统崩溃或硬件问题，我们可以假设MySQL磁盘数据在重新启动后不可用。 这意味着MySQL无法成功启动，因为某些磁盘数据块不再可读。 在这种情况下，需要重新格式化磁盘，安装新磁盘，或者修复基本问题。 然后有必要从备份中恢复我们的MySQL数据，这意味着备份必须已经完成。 为了确保这种情况，请设计并实施备份策略。

7.3.1 建立备份策略

在这里讨论使用mysqldump。

假设我们在周日下午1点使用以下命令在负载较低时对所有数据库中的所有InnoDB表进行完整备份：

shell> mysqldump --all-databases --master-data--single-transaction > backup_sunday_1_PM.sql

由mysqldump生成的生成的.sql文件包含一组SQL INSERT语句，可用于稍后重新加载被转储的表。

此备份操作获取转储开始时所有表上的全局读锁（使用FLUSH TABLES WITH READ LOCK）。 一旦获得该锁，就会读取二进制日志坐标并释放锁。 如果在发出FLUSH语句时长时间更新语句正在运行，备份操作可能会停止，直到这些语句完成。 之后，转储不会加锁并且不会干扰读取和写入表格。

之前假设要备份的表是InnoDB表，因此--single-transaction使用一致的读取并保证mysqldump看到的数据不会改变。 （由其他客户端对InnoDB表所做的更改不会被mysqldump进程看到。）如果备份操作包含非事务表，则一致性要求在备份过程中它们不会更改。 例如，对于MySQL数据库中的MyISAM表，在备份过程中不得对MySQL帐户进行管理更改。

完整的备份是必要的，但创建它们并不总是方便的。 它们会生成大量的备份文件并需要大量时间。 从每次连续完整备份包含所有数据的意义上说，它们并不是最佳选择，甚至是自上次完全备份以来那部分数据没有更改过。 进行初始完整备份，然后进行增量备份会更高效。 增量备份更小，生产时间更短。 折中的是，在恢复时，只能通过重新加载完整备份来恢复数据。 您还必须处理增量备份以恢复增量更改。

要进行增量备份，我们需要保存增量更改。 在MySQL中，这些更改用二进制日志表示，所以MySQL服务器应始终使用--log-bin选项启动以启用该日志。 启用二进制日志记录后，服务器在更新数据时将每个数据更改写入文件。 查看使用--log-bin选项启动的MySQL服务器的数据目录，该服务器已经运行了几天，我们发现这些MySQL二进制日志文件。

每次重新启动时，MySQL服务器都会使用序列中的下一个数字创建一个新的二进制日志文件。 在服务器正在运行时，您还可以通过发出FLUSH LOGS SQL语句或使用mysqladmin flush-logs命令来告诉它关闭当前二进制日志文件并手动开始新文件。 mysqldump也有一个选项来刷新日志。 data目录中的.index文件包含目录中所有MySQL二进制日志的列表。

MySQL二进制日志对恢复很重要，因为它们构成了增量备份集。 如果您确保在进行完整备份时刷新日志，则以后创建的二进制日志文件将包含自备份以来所做的所有数据更改。 让我们修改一下前面的mysqldump命令，以便在完整备份时刷新MySQL二进制日志，并使转储文件包含新的当前二进制日志的名称：

shell> mysqldump --single-transaction --flush-logs--master-data=2 --all-databases > backup_sunday_1_PM.sql

执行此命令后，数据目录包含一个新的二进制日志文件gbichot2-bin.000007，因为--flush-logs选项会导致服务器刷新其日志。 --master-data选项会导致mysqldump将二进制日志信息写入其输出，因此生成的.sql转储文件包含以下行：

--Position to start replication or point-in-time recovery from--CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

由于mysqldump命令进行了完整备份，因此这些行意味着两件事情：

• 转储文件包含在写入gbichot2-bin.000007二进制日志文件或更高之前所做的所有更改。
• 备份后记录的所有数据更改都不存在于转储文件中，但存在于gbichot2-bin.000007二进制日志文件或更高的文件中。

星期一下午1点，我们可以通过刷新日志来创建一个增量备份来开始一个新的二进制日志文件。 例如，执行mysqladmin flush-logs命令可创建gbichot2-bin.000008。 星期日下午1点之间的所有变化 完整备份和星期一下午1点。 将位于gbichot2-bin.000007文件中。 这种增量备份非常重要，因此将其复制到安全的地方是一个不错的主意。 （例如，将其备份到磁带或DVD上，或将其复制到另一台计算机上。）星期二下午1点，执行另一个mysqladmin flush-logs命令。 星期一下午1点之间的所有变化 和星期二下午1点。 将在gbichot2-bin.000008文件中（这也应该在安全的地方复制）。

MySQL二进制日志占用磁盘空间。 为了释放空间，不时清除它们。 一种方法是通过删除不再需要的二进制日志，例如当我们进行完整备份时：

shell> mysqldump --single-transaction --flush-logs--master-data=2 --all-databases --delete-master-logs> backup_sunday_1_PM.sql

注意：

如果您的服务器是复制主服务器，使用mysqldump --delete-master-logs删除MySQL二进制日志可能很危险，因为从属服务器可能尚未完全处理二进制日志的内容。 PURGE BINARY LOGS语句的说明解释了在删除MySQL二进制日志之前应该验证的内容。

--也就是说，在主从复制环境，验证后手动进行PURGE BINARY LOGS 比较合适。

7.3.2 使用备份来恢复

现在，假设我们周三早上8点发生灾难性的崩溃，需要从备份中恢复。 要恢复，首先我们恢复我们的最后一次完整备份（从星期日下午1点开始）。 完整备份文件只是一组SQL语句，因此恢复它非常简单：

shell> mysql < backup_sunday_1_PM.sql

此时，数据将恢复到截止到星期日下午1点的状态。要恢复从那时起所做的更改，我们必须使用增量备份; 即gbichot2-bin.000007和gbichot2-bin.000008二进制日志文件。 如果需要，从文件备份位置获取文件，然后像这样处理它们的内容：

shell> mysqlbinloggbichot2-bin.000007 gbichot2-bin.000008 | mysql

我们现在已经将数据恢复到了星期二下午1点的状态，但仍然错过了从该日期到崩溃时间的更改。 为了不丢失它们，我们需要让MySQL服务器将其MySQL二进制日志存储到一个安全的位置（RAID磁盘，SAN，...），与存储其数据文件的地方不同，以便这些日志不是 在被毁坏的磁盘上。 （也就是说，我们可以使用--log-bin选项启动服务器，该选项指定与数据目录所在物理设备不同的物理设备上的位置。这样，即使包含目录的设备 ）如果我们这样做了，我们将手头上有gbichot2-bin.000009文件（以及任何后续文件），我们可以使用mysqlbinlog和mysql来应用它们以恢复最近的数据更改，而在崩溃的那一刻不会有丢失：

shell> mysqlbinlog gbichot2-bin.000009 ... | mysql

7.3.3 备份策略总结

在操作系统崩溃或电源故障的情况下，InnoDB本身可以完成恢复数据的所有工作。 但要确保您可以安然入睡，请遵守以下准则：

• 请始终使用--log-bin选项运行MySQL服务器，甚至使用--log-bin = log_name运行MySQL服务器，其中日志文件名称位于与数据目录所在的驱动器不同的安全介质上。 如果你有这样安全的介质，这种技术对磁盘负载平衡也是很好的（这可以提高性能）。
• 使用第7.3.1节“建立备份策略”中的前面所示的mysqldump命令定期进行完整备份，该命令可进行在线非阻塞备份。
• 通过使用FLUSH LOGS或mysqladmin flush-logs刷新日志来定期进行增量备份。