数据库数据备份与恢复 MySQL数据备份与恢复

试试语音读文章

热门答案：

• 数据库备份和恢复类型
• 数据库备份方式
• 数据备份恢复策略
• 使用mysqldump备份数据
• 时间点恢复（增量恢复）
• Myisan表维护和数据恢复

ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss

数据库备份和恢复类型

• 物理备份和逻辑备份

物理备份：将数据库目录中的文件完全拷贝一份。其中包括数据文件。索引文件。日志文件以及与MySQL服务器配置相关的配置文件等等。这种类型的备份适用于出现问题时需要快速恢复的大型重要数据库。

逻辑备份：保存表的数据结构和内容。保存为包含CREAT TABLE（创建数据结构）和INSERT TABLE（插入数据内容）的脚本文件。这种类型的备份适用于少量数据。您可以在其中编辑数据值或表结构。或在不同的机器架构上重新创建数据。

//物理备份具有如下特点：
1、备份由数据库目录和文件的精确副本组成。通常这是 MySQL 数据目录的全部或部分的副本。
2、物理备份方法比逻辑备份方法更快。因为它们只涉及文件复制而不进行转换。
3、备份和恢复粒度范围从整个数据目录级别到单个文件级别。这可能会或可能不会提供表级粒度。具体
取决于存储引擎。例如。 InnoDB每个表可以在一个单独的文件中。或者与其他 InnoDB表共享文件存储；
每个 MyISAM表唯一地对应于一组文件。
4、除了数据库之外。备份还可以包括任何相关文件。例如日志或配置文件。
5、MEMORY存储引擎以这种方式备份表中的 数据很棘手。因为它们的内容不存储在磁盘上。（MySQL
Enterprise Backup 产品具有一项功能。您可以MEMORY在备份期间从表中检索数据。）
6、备份只能移植到具有相同或相似硬件特征的其他机器上。
7、可以在 MySQL 服务器未运行时执行备份。如果服务器正在运行。则需要执行适当的锁定。以使服务
器在备份期间不会更改数据库内容。MySQL Enterprise Backup 自动为需要它的表执行此锁定。//逻辑备份具有以下特点：
1、逻辑备份是通过查询 MySQL 服务器来获取数据库结构和内容信息来完成的。
2、逻辑备份比物理方法慢。因为服务器必须访问数据库信息并将其转换为逻辑格式。
3、逻辑备份输出大于物理备份。尤其是以文本格式保存时。
4、备份和还原粒度可在服务器级别（所有数据库）、数据库级别（特定数据库中的所有表）或表级别使用。
无论存储引擎如何。都是如此。
5、备份不包括日志或配置文件。或不属于数据库的其他与数据库相关的文件。
6、以逻辑格式存储的备份独立于机器且高度可移植。
7、逻辑备份是在 MySQL 服务器运行的情况下执行的。服务器未脱机。
8、逻辑备份工具包括mysqldump 程序和SELECT ... INTO OUTFILE语句。这些适用于任何存储引擎。
甚至MEMORY.
9、要恢复逻辑备份。可以使用mysql客户端处理 SQL 格式的转储文件。要加载分隔文本文件。请使用
LOAD DATA语句或mysqlimport 客户端

• 在线备份和离线备份

在 MySQL 服务器运行时进行在线备份。以便可以从服务器获取数据库信息。离线备份在服务器停止时进行。这种区别也可以描述为“热”备份与 “冷”备份；“热”备份是服务器保持运行状态。但当您从外部访问数据库文件时。必须锁定。以防止修改数据的备份 。

//在线备份特点：
1、在线备份对其他客户端的干扰较小。它们可以在备份期间连接到 MySQL 服务器。并且能够根据
需要执行的操作访问数据。
2、必须注意施加适当的锁定。以免发生会损害备份完整性的数据修改。MySQL Enterprise Backup 产品
会自动执行此类锁定。//离线备份特点：
1、由于服务器在备份期间不可用。客户端可能会受到不利影响。因此。此类备份通常取自可以脱机
而不会损害可用性的副本。
2、备份过程更简单。因为不会受到客户端活动的干扰。

• 本地和远程服务器备份

本地备份是在 MySQL 服务器运行的同一主机上执行的。而远程备份是从不同的主机上完成的。对于某些类型的备份。即使输出是本地写入服务器上的。也可以从远程主机启动备份。

1、mysqldump可以连接到本地或远程服务器。对于 SQL 输出（CREATE和 INSERT语句）。可以进行
本地或远程转储并在客户端生成输出。对于分隔文本输出（带有 --tab选项）。在服务器主机上创建数据
文件。
2、SELECT ... INTO OUTFILE可以从本地或远程客户端主机启动。但输出文件是在服务器主机上创建的。
3、物理备份方法通常在 MySQL 服务器主机上本地启动。以便服务器可以脱机。尽管复制文件的目标
可能是远程的。

• 快照备份

一些文件系统允许拍摄“快照” 。它们在给定时间点提供文件系统的逻辑副本。而不需要整个文件系统的物理副本。（例如。可以使用写时复制技术。以便只复制在快照时间之后修改的部分文件系统。） MySQL 本身不提供获取文件系统快照的能力。它可通过第三方解决方案获得。例如 Veritas、LVM 或 ZFS。

• 全局备份和增量备份

全局备份包括 MySQL 服务器在给定时间点管理的所有数据。增量备份包括在给定时间跨度内（从一个时间点到另一个时间点）对数据所做的更改。MySQL 有不同的方法来执行全局备份。例如本节前面描述的那些。通过启用服务器的二进制日志来实现增量备份。服务器使用它来记录数据更改。

• 全局恢复和时间点恢复（增量恢复）

全局恢复从全局备份中恢复所有数据。这会将服务器实例恢复到进行备份时的状态。如果该状态不够最新。则可以在完全恢复之后恢复自全局备份以来所做的增量备份。以使服务器处于更新状态。

增量恢复是恢复在给定时间跨度内所做的更改。这也称为时间点恢复。因为它使服务器的状态在给定时间之前保持最新状态。时间点恢复基于二进制日志。通常在从备份文件完全恢复之后。将服务器恢复到备份时的状态。然后将写入二进制日志文件中的数据更改作为增量恢复应用。以重做数据修改并将服务器恢复到所需的时间点。

• 备份计划。压缩和加密

备份计划对于自动化备份过程很有价值。备份输出的压缩减少了空间需求。输出加密提供了更好的安全性。防止未经授权访问备份数据。MySQL 本身不提供这些功能。MySQL Enterprise Backup 产品可以压缩InnoDB备份。并且可以使用文件系统实用程序来实现备份输出的压缩或加密。其他第三方解决方案可能可用。

数据备份和恢复的方法

• 使用mysqldump命令备份

mysqldump程序可以进行备份 。详细备份过程待会在使用mysqldump备份数据章节中具体介绍。

• 启用二进制日志文件进行增量备份

MySQL 支持使用二进制日志进行增量备份。二进制日志文件为您提供了在执行备份之后将更改复制到数据库所需的信息。因此。要让服务器恢复到某个时间点。必须在其上启用二进制日志记录。这是 MySQL 8.0 的默认设置.

在您想要进行增量备份（包含自上次全局或增量备份以来发生的所有更改）的那一刻。您应该使用FLUSH LOGS. 完成后。您需要将所有二进制日志复制到备份位置。范围从最后一次完整或增量备份到最后一个。

二进制日志文件增量备份配合全局备份。使备份的数据更加完整。

• 通过复制文件进行备份

MyISAM 表可以通过复制表文件（*.MYD、*.MYI文件和相关*.sdi文件）来备份。要获得一致的备份。请停止服务器或锁定并刷新相关表。如下：

FLUSH TABLES tbl_list WITH READ LOCK;		//复制进行中。禁止刷新表。但是可以读

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

只要服务器不更新任何内容。您也可以简单地通过复制表文件来创建二进制备份。（但请注意。Innodb中表空间不支持复制。此外。即使服务器没有主动更新数据。InnoDB也可能仍将修改后的数据缓存在内存中并且未刷新到磁盘。所以Innodb不支持通过复制文件的方式来备份数据）

• 使用副本进行备份

如果您在进行备份时遇到服务器性能问题。一种可以提供帮助的策略是设置复制并在从服务器而不是主服务器上执行备份

如果您正在备份副本。 则无论您使用哪种备份方法。都应在备份副本的数据库时备份其连接元数据存储库和应用程序元数据存储库（请参阅第 17.2.4 节。“中继日志和复制元数据存储库”）选择。恢复副本的数据后。始终需要此信息来恢复复制。如果您的副本正在复制LOAD DATA语句。您还应该备份SQL_LOAD-*副本用于此目的的目录中存在的所有文件。副本需要这些文件来恢复任何中断LOAD DATA操作的复制。这个目录的位置就是系统变量的值replica_load_tmpdir（来自 MySQL 8.0.26）或slave_load_tmpdir（在 MySQL 8.0.26 之前）。如果服务器未使用该变量集启动。则目录位置是tmpdir系统变量的值。

• 使用企业版相关产品备份热点数据

MySQL Enterprise Edition 的客户可以使用MySQL Enterprise Backup产品对 整个实例或选定的数据库、表或两者进行物理备份。该产品包括增量和压缩备份的功能。备份物理数据库文件使恢复比mysqldump命令等逻辑技术快得多 。InnoDB使用热备份机制复制表。（理想情况下。这些InnoDB表应该代表大部分数据。）来自其他存储引擎的表是使用热备份复制的机制。

• 制作分隔文本文件进行备份

通过db.table的形式来创建数据的文本文件。可以防止同表名的数据文件的覆盖。以下两种方式都可以创建数据的文本文件：

 1、SELECT * INTO OUTFILE 'file_name' FROM tbl_name：此方法适用于任何类型的数据文件。
 但只保存表数据。而不保存表结构。
 2、mysqldump --tab tableName

• 恢复损坏的表

如果您必须恢复MyISAM已损坏的表。请先尝试使用REPAIR TABLE或myisamchk -r恢复它们。这应该适用于所有情况的 99.9%。

使用备份恢复策略

数据备份主要应用与操作系统或者数据库服务器崩溃后进行数据恢复。以下几种情况需要进行数据备份：

1、操作系统崩溃
2、文件系统崩溃
3、断电
4、硬件问题（硬盘驱动器和主板）

• 建立备份策略

为了有用。必须定期安排备份。可以使用多种工具在 MySQL 中完成全局备份（某个时间点的数据快照）。例如。MySQL Enterprise Backup可以对整个实例进行物理备份。并进行优化以最大限度地减少开销并避免备份InnoDB数据文件时中断；mysqldump提供在线逻辑备份。本讨论使用mysqldump。

//假设我们 InnoDB在周日下午 1 点使用以下命令对所有数据库中的所有表进行完整备份。此时负载较低：
$> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql

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

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

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

完全备份是必要的。但创建它们并不总是很方便。它们会生成大型备份文件并需要时间来生成。它们不是最优的。因为每个连续的完整备份都包括所有数据。即使是自上次完整备份以来未更改的部分。进行初始完整备份。然后进行增量备份更有效。增量备份更小。生成时间更短。考虑到。在恢复时。您不能仅通过重新加载完整备份来恢复数据。您还必须处理增量备份以恢复增量更改。

要进行增量备份。我们需要保存增量更改。在 MySQL 中。这些更改在二进制日志中表示。因此 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 LOGS在服务器运行时。您还可以通过发出SQL 语句或使用mysqladmin flush-logs命令 来告诉它关闭当前的二进制日志文件并手动开始一个新的日志文件 。mysqldump还有一个刷新日志的选项。数据目录中的.index文件包含目录中所有 MySQL 二进制日志的列表。

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

//备份的同时还可以刷新二进制文件
$> 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二进制日志文件或更高版本中。
*/

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

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

• 使用备份恢复数据

①恢复全局备份：$> mysql < backup_sunday_1_PM.sql
②恢复增量备份：$> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

• 备份策略总结

在操作系统崩溃或电源故障的情况下。InnoDB它本身会完成恢复数据的所有工作。但为了确保您能睡得好。请遵守以下准则：

1、始终在启用二进制日志记录的情况下调整 MySQL 服务器（这是 MySQL 8.0 的默认设置）。
2、使用前面所示的mysqldump命令 进行定期完整备份。该 命令进行联机、非阻塞备份。
3、FLUSH LOGS通过使用 mysqladmin flush-logs 刷新 日志进行定期增量备份 。

使用mysqldump备份数据

• 使用mysqldump转储SQL格式数据

本节介绍如何使用mysqldump生成转储文件。以及如何重新加载转储文件。转储文件可以通过多种方式使用：

1、作为备份以在数据丢失的情况下启用数据恢复。
2、作为设置副本的数据源。
3、作为实验的数据来源：
			--在不更改原始数据的情况下制作可以使用的数据库副本。
      --测试潜在的升级不兼容性。

mysqldump产生两种类型的输出。取决于是否–tab给出了选项：

1、如果没有--tab。 mysqldump会将 SQL 语句写入标准输出。此输出由 CREATE创建转储对象
（数据库、表、存储例程等）的 INSERT语句和将数据加载到表中的语句组成。输出可以保存在文件中。
稍后使用 mysql重新加载以重新创建转储对象。选项可用于修改 SQL 语句的格式。并控制转储哪些
对象。
2、使用--tab。 mysqldump为每个转储表生成两个输出文件。服务器将一个文件写入为制表符分隔的文本。
tbl_name.txt 此文件在输出目录中命名 。服务器还将 CREATE TABLE表的语句发送到mysqldump。
它将其作为 tbl_name.sql 输出目录中命名的文件写入

• 导入SQL备份文件

要重新加载由mysqldump编写的包含 SQL 语句的转储文件 。请将其用作mysql客户端的输入。如果转储文件是由mysqldump使用–all-databasesor–databases选项创建的。它包含CREATE DATABASE和USE语句。并且不需要指定加载数据的默认数据库：

$> mysql < dump.sql
//或者
$> mysql> source dump.sql

如果文件是不包含CREATE DATABASE和USE语句的单数据库转储。请先创建数据库（如有必要）：

$> mysqladmin create db1

然后在加载转储文件时指定数据库名称：

$> mysql db1 < dump.sql

或者。从mysql中创建数据库。选择它作为默认数据库。然后加载转储文件：

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

• mysqldump使用方法

/*
在考虑升级 MySQL 时。谨慎的做法是与当前的生产版本分开安装较新的版本。然后。您可以从生产服务器
转储数据库和数据库对象定义。并将它们加载到新服务器中以验证它们是否得到正确处理。
（这对于测试降级也很有用。）
*/
//在生产服务器上：
$> mysqldump --all-databases --no-data --routines --events > dump-defs.sql
//在升级的服务器上：
$> mysql < dump-defs.sql
/*
因为转储文件不包含表数据。所以可以快速处理。这使您能够发现潜在的不兼容性。而无需等待冗长的
数据加载操作。在处理转储文件时查找警告或错误。
*/
/*在您确认定义被正确处理后。转储数据并尝试将其加载到升级的服务器中。*/
//在生产服务器上：
$> mysqldump --all-databases --no-create-info > dump-data.sql
//在升级的服务器上：
$> mysql < dump-data.sql

/*分别转储表定义和内容*/
/*
该--no-data选项告诉 mysqldump不要转储表数据。导致转储文件仅包含创建表的语句。
相反。该 --no-create-info选项告诉mysqldump禁止 CREATE输出中的语句。以便转储文件仅包含
表数据。
*/
//例如。要为test数据库单独转储表定义和数据。请使用以下命令
$> mysqldump --no-data test > dump-defs.sql
$> mysqldump --no-create-info test > dump-data.sql
//对于仅定义转储。添加 --routines 和 --events 选项以还包括存储的例程和事件定义：
$> mysqldump --no-data --routines --events test > dump-defs.sql

/*转储存储的程序*/
//几个选项控制mysqldump如何 处理存储程序（存储过程和函数、触发器和事件）：
--events：转储事件调度程序事件
--routines: 转储存储过程和函数
--triggers: 表的转储触发器
默认情况下启用该--triggers选项。以便在转储表时。它们会伴随它们拥有的任何触发器。
其他选项默认禁用。必须明确指定以转储相应的对象。要明确禁用这些选项中的任何一个。
请使用其跳过形式： --skip-events、 --skip-routines或 --skip-triggers。

/* 将数据库从一台服务器复制到另一台服务器*/
/*在服务器 1 上：*/
$> mysqldump --databases db1 > dump.sql				//将转储文件从服务器 1 复制到服务器 2。
/*在服务器 2 上：*/
$> mysql < dump.sql
/*
--databases与 mysqldump命令行一起 使用会导致转储文件包含创建数据库（如果确实存在）
CREATE DATABASE并 USE使其成为重新加载数据的默认数据库的语句。
*/
/*然后您需要在服务器 2 上创建数据库（如果需要）。并在重新加载转储文件时将其指定为默认数据库。*/
/*在服务器 1 上：*/
$> mysqldump db1 > dump.sql
/*在服务器 2 上：*/
$> mysqladmin create db1
$> mysql db1 < dump.sql
/*在这种情况下。您可以指定不同的数据库名称。因此省略mysqldump--databases命令可以让您
从一个数据库转储数据并将其加载到另一个数据库*/

/*
制作数据库副本。不要--databases在mysqldump命令行上使用。因为这会导致USE db1包含
在转储文件中。这会覆盖 mysql命令行 db2上的命名效果。
*/
        $> mysqldump db1 > dump.sql
        $> mysqladmin create db2
        $> mysql db2 < dump.sql

时间点恢复数据（增量恢复）

• 使用二进制进行时间点恢复

时间点恢复的信息来源是完整备份操作后生成的一组二进制日志文件。因此。要允许服务器恢复到某个时间点。必须在其上启用二进制日志记录。这是 MySQL 8.0 的默认设置。

要从二进制日志中恢复数据。您必须知道当前二进制日志文件的名称和位置。默认情况下。服务器会在数据目录中创建二进制日志文件。但可以使用–log-bin选项指定路径名以将文件放置在不同的位置。要查看所有二进制日志文件的列表。请使用以下语句：

mysql> SHOW BINARY LOGS;

要确定当前二进制日志文件的名称。请发出以下语句：

mysql> SHOW MASTER STATUS;

mysqlbinlog实用程序将二进制日志文件中 的事件从二进制格式转换为文本。以便可以查看或应用它们。mysqlbinlog具有用于根据事件时间或事件在日志中的位置选择二进制日志部分的选项。

应用二进制日志中的事件会导致它们所代表的数据修改被重新执行。这可以在给定的时间跨度内恢复数据更改。要应用二进制日志中的事件。请 使用mysql客户端处理mysqlbinlog输出 ：

$> mysqlbinlog binlog_files | mysql -u root -p

当您需要确定事件时间或位置以在执行事件之前选择部分日志内容时。查看日志内容会很有用。要查看日志中的事件。请将mysqlbinlog输出发送到分页程序：

$> mysqlbinlog binlog_files | more

或者。将输出保存在文件中并在文本编辑器中查看文件：

$> mysqlbinlog binlog_files > tmpfile
$> ... edit tmpfile ...

编辑文件后。应用如下内容：

$> mysql -u root -p < tmpfile

如果您在 MySQL 服务器上应用了多个二进制日志。请使用单个连接来处理您要处理的所有二进制日志文件的内容。这是一种方法：

$> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

另一种方法是将整个日志写入单个文件。然后处理该文件：

$> mysqlbinlog binlog.000001 >  /tmp/statements.sql
$> mysqlbinlog binlog.000002 >> /tmp/statements.sql
$> mysql -u root -p -e "source /tmp/statements.sql"

• 使用事件位置进行时间点恢复

。。。。。。

大家还在搜：

• 基础茶具怎么选 如何选择适合的茶具
• 中国十大国粹 最具代表性
• 煎牛排教程图文保姆级教程 附自用产品清单
• 蒸汽眼罩怎么选 蒸汽眼罩的注意事项
• 最健康的作息表 让你健康的安排生活作息

以上就是由互联网推广工程师 网创网 整理编辑的，如果觉得有帮助欢迎收藏转发~