mysql备份与恢复策略,mysql8 参考手册--备份和恢复策略示例

本节讨论执行备份的过程，该过程使您能够在几种类型的崩溃后恢复数据：

操作系统崩溃

电源(检测)失败

文件系统崩溃

硬件问题(硬盘驱动器，主板等)

该示例命令不包括诸如 --user和 --password针对 的mysqldump和MySQL的客户端程序。您应该包括必要的选项，以使客户端程序能够连接到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数据，这意味着必须已经进行了备份。为确保这种情况，请设计并实施备份策略。

建立备份策略

为了有用，必须定期计划备份。可以使用多种工具在MySQL中进行完整备份(时间点上的数据快照)。例如， MySQL Enterprise Backup可以执行整个实例的 物理备份，并进行了优化以最大程度地减少开销并避免在备份InnoDB数据文件时造成中断；mysqldump提供在线 逻辑备份。本讨论使用mysqldump。

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

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看到的数据 不会更改。(mysqldump进程InnoDB看不到其他客户端对表所做的更改。)如果备份操作包括非事务性表，则一致性要求它们在备份期间不要更改。例如，对于 数据库中的 表，在备份期间不得对MySQL帐户进行任何管理更改。 MyISAMmysql

完全备份是必需的，但是创建备份并不总是很方便。它们会产生较大的备份文件，并且需要花费一些时间来生成。从每个连续的完整备份都包括所有数据的意义上说，它们不是最佳的，即使是自上次完整备份以来未更改的部分。进行初始完整备份，然后进行增量备份，效率更高。增量备份更小，花费的时间更少。代价是，在恢复时，您不能仅通过重新加载完整备份来恢复数据。您还必须处理增量备份以恢复增量更改。

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

-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001

-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002

-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003

-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004

-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005

-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006

-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index

每次重新启动时，MySQL服务器都会使用序列中的下一个数字创建一个新的二进制日志文件。当服务器运行时，您还可以告诉它关闭当前的二进制日志文件，并通过发出FLUSH LOGSSQL语句或使用mysqladmin flush-logs命令手动开始一个新的日志文件 。 mysqldump也有一个刷新日志的选项。该.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命令create 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二进制日志之前应验证的内容。请参见 第13.4.1.1节“ PURGE BINARY LOGS语句”。

使用备份进行恢复

现在，假设我们在星期三上午8点发生了灾难性的崩溃，需要从备份中恢复。要恢复，首先我们要还原上一次的完整备份(从星期日下午1点开始的完整备份)。完整的备份文件只是一组SQL语句，因此还原它非常容易：

shell>mysql < backup_sunday_1_PM.sql

此时，数据将恢复到星期日下午1点的状态。要恢复此后所做的更改，我们必须使用增量备份。即 gbichot2-bin.000007和 gbichot2-bin.000008二进制日志文件。如有必要，从备份位置获取文件，然后按以下方式处理其内容：

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

现在，我们已将数据恢复到星期二下午1点为止的状态，但是仍然缺少从该日期到崩溃日期的更改。为了不丢失它们，我们需要让MySQL服务器将其MySQL二进制日志存储在与其存储数据文件的位置不同的安全位置(RAID磁盘，SAN等)中，以便这些日志不会在损坏的磁盘上。(也就是说，我们可以使用一个--log-bin选项来启动服务器，该 选项指定与数据目录所在的另一台物理设备不在同一物理设备上的位置。这样，即使包含该目录的设备丢失，日志也是安全的。)我们已经做到了，我们将拥有gbichot2-bin.000009 文件(以及所有后续文件)，我们可以使用mysqlbinlog和mysql来应用它们， 以恢复最新的数据更改，直到崩溃时为止都不会丢失：

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

有关使用mysqlbinlog 处理二进制日志文件的更多信息，请参见 第7.5节“使用二进制日志进行时间点(增量)恢复”。

备份策略摘要

在操作系统崩溃或电源故障的情况下， InnoDB它本身会执行恢复数据的所有工作。但要确保您可以睡个好觉，请遵循以下准则：

始终使用--log-bin选项甚至运行MySQL服务器，该 选项甚至 日志文件名位于与数据目录所在的驱动器不同的某些安全介质上。如果您有这种安全的介质，则此技术也可以很好地平衡磁盘负载(从而提高性能)。 --log-bin=log_name

使用前面在第7.3.1节“建立备份策略”中显示的mysqldump命令进行定期的完整备份，该 命令 将进行联机无阻塞备份。

通过使用FLUSH LOGS或 mysqladmin flush-logs刷新日志来进行定期增量备份 。