MySQL数据库备份与恢复

备份类型

备份是以防万一的一种必要手段，在出现硬件损坏或非人为的因素而导致数据丢失时，可以使用备份恢复数据，以将损失降低到最小程度，因此备份是必须的。备份可以分为以下几个类型。

根据备份的方法（是否需要数据库离线）可以将备份分为：

• 热备（Hot Backup）
• 冷备（Cold Backup）
• 温备（Warm Backup）

热备份可以在数据库运行中直接备份，对正在运行的数据库操作没有任何的影响，数据库的读写操作可以正常执行。这种方式也称为Online Backup（在线备份）。

冷备份必须在数据库停止的情况下进行备份，数据库的读写操作不能执行。这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式也称为Offline Backup（离线备份）。

温备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，备份时仅支持读操作，不支持写操作。

按照备份后文件的内容，热备份又可以分为：逻辑备份、裸文件备份。

在 MySQL 数据库中，逻辑备份是指备份出的文件内容是可读的，一般是文本内容。内容一般是由一条条 SQL 语句，或者是表内实际数据组成。如mysqldump和SELECT * INTO OUTFILE的方法。这类方法的好处是可以观察导出文件的内容，一般适用于数据库的升级、迁移等工作。但其缺点是恢复的时间较长。

裸文件备份是指复制数据库的物理文件，既可以在数据库运行中进行复制（如 ibbackup、xtrabackup 这类工具），也可以在数据库停止运行时直接复制数据文件。这类备份的恢复时间往往比逻辑备份短很多。

按照备份数据库的内容来分，备份又可以分为：完全备份、部分备份。

完全备份是指对数据库进行一个完整的备份，即备份整个数据库，如果数据较多会占用较大的时间和空间。

部分备份是指备份部分数据库（例如，只备份一个表）。

部分备份又分为：增量备份、差异备份。

增量备份需要使用专业的备份工具。指的是在上次完全备份的基础上，对更改的数据进行备份。也就是说每次备份只会备份自上次备份之后到备份时间之内产生的数据。因此每次备份都比差异备份节约空间，但是恢复数据麻烦。

差异备份指的是自上一次完全备份以来变化的数据。和增量备份相比，浪费空间，但恢复数据比增量备份简单。

MySQL 中进行不同方式的备份还要考虑存储引擎是否支持，如 MyISAM 不支持热备，支持温备和冷备。而 InnoDB 支持热备、温备和冷备。

一般情况下，我们需要备份的数据分为以下几种：

• 表数据
• 二进制日志、InnoDB 事务日志
• 代码（存储过程、存储函数、触发器、事件调度器）
• 服务器配置文件

下面是几种常用的备份工具：

• mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备。
• cp、tar等归档复制工具：物理备份工具，适用于所有的存储引擎、冷备、完全备份、部分备份。
• lvm2 snapshot：借助文件系统管理工具进行备份。
• mysqlhotcopy：名不副实的一个工具，仅支持 MyISAM 存储引擎。
• xtrabackup：一款由 percona 提供的非常强大的 InnoDB/XtraDB 热备工具，支持完全备份、增量备份。

冷备份

冷备份可以称为Offline Backup（离线备份）。这种备份最为简单，一般只需要复制相关的数据库物理文件到另外的位置即可。

由于 MySQL 服务器中的数据文件是基于磁盘的文本文件，所以最简单、最直接的备份操作，就是将数据文件直接复制出来。但是由于 MySQL 服务器的数据文件在运行时期，总是处于打开和使用状态，因此备份文件不一定有效。为了解决该问题，在复制数据文件时，需要先停止 MySQL 服务器。

这样做的好处是可以保证数据库的完整性，备份过程简单且恢复速度相对快一些，但是关闭数据库会影响现有业务的进行。服务器停止运行期间，用户不能再继续访问网站。冷备一般用于不是很重要、非核心的业务上面。

冷备份的优点如下：

• 备份简单、快速，只要复制相关文件即可。
• 备份文件易于在不同操作系统，不同 MySQL 版本上进行恢复。
• 恢复相当简单，只需要把文件恢复到指定位置即可。
• 恢复速度快，不需要执行任何 SQL 语句，也不需要重建索引。
• 低度维护，高度安全。

冷备份的缺点如下：

• 备份过程中，数据库不能做其它的工作，且必须是关闭状态。
• InnoDB 存储引擎冷备的文件通常比逻辑文件大很多，因为表空间存放着很多其它的数据，如undo段，插入缓冲等信息。
• 若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。
• 冷备也不总是可以轻易的跨平台。操作系统、MySQL 的版本、文件大小写敏感和浮点数格式都会成为问题。

数据库的物理文件主要由数据库的数据文件、日志文件以及配置文件等组成。MySQL 系统有一些共有的日志文件和系统表的数据文件。每种存储引擎的物理文件也不一样。

冷备的备份与恢复过程也很简单。仅仅需要如下几步：

1. 为了保证所备份数据的完整性，在停止 MySQL 数据库服务器之前，需要先执行 FLUSH TABLES 语句将所有数据写入到数据文件的文本文件里。
2. 停掉 MySQL 服务，命令（2种方式）如下：

   mysqladmin -uroot -proot shutdown
   NET START mysql

3. 备份过程就是复制整个数据目录到远程备份机或者本地磁盘上，Linux 和 Windows 命令如下：

   Scp -r /data/mysql/ root@远程备份机ip:/新的目录
   Copy -r /data/mysql/ 本地新目录

   备份到本次磁盘也可以手动复制上述相关目录里的数据文件。
4. 恢复过程就更简单了，仅仅需要把已备份的数据目录替换原有的目录就可以了，最后重启 MySQL 服务。

需要注意的是，通过复制数据文件这种方式实现数据恢复时，必须保证两个 MySQL 数据库的主版本号一致。只有两个 MySQL 数据库主版本号相同时，才能保证它们的数据文件类型是相同的。

冷备份所需物理文件

MyISAM存储引擎

MyISAM 存储引擎的所有数据默认存放在C:/ProgramData/MySQL/MySQL Server 5.7/Data路径下，即配置文件（my.ini或my.cnf）中datadir参数的值。

实际上不管我们使用的是 MyISAM 存储引擎还是其他存储引擎，每一个数据库都会在datadir目录下有一个文件夹（包括系统信息的数据库mysql也是一样）。

在各个数据库中每一个 MyISAM 存储引擎的表都会有 3 个文件存在，即记录表结构元数据的.frm文件，存储表数据的.MYD文件，存储索引数据的.MYI文件。

MyISAM 属于非事务性存储引擎，它没有自己的日志文件。所以 MyISAM 存储引擎的物理备份除了需要备份 MySQL 系统共有的物理文件之外，还需要备份上面的 3 种文件。

Innodb 存储引擎

Innodb 存储引擎属于事务性存储引擎，存放数据的位置也可能与 MyISAM 存储引擎有所不同，这主要取决于 Innodb 的相关配置。

指定 Innodb 存放数据和日志文件的位置参数为innodb_data_home_dir、innodb_data_file_path和innodb_log_group_home_dir。以及决定 Innodb 的表空间存储方式参数innodb_file_per_table，它决定 Innodb 是以共享表空间存放数据还是以独享表空间方式存储数据。

如果使用的是共享表空间的存储方式，那么需要备份innodb_data_home_dir和innodb_data_file_path参数设定的所有数据文件，以及datadir中相应数据库目录下的所有 Innodb 存储引擎表的 frm文件。

而如果使用的是独享表空间，那么除了需要备份上面共享表空间方式所需要备份的所有文件之外，我们还需要备份datadir中相应数据库目录下的所有.idb文件，该文件中存放的才是独享表空间方式下 Innodb 存储引擎表的数据。

那么既然是使用独享表空间，为什么还要备份共享表空间“才使用到”的数据文件呢？其实这是很多人的一个共性误区，以为使用独享表空间的时候 Innodb 的所有信息就都存放在“datadir”所设定数据库目录下的“.ibd”文件中。实际上并不是这样的，“.ibd”文件中所存放的仅仅只是我们的表数据而已。

大家都很清楚，Innodb 是事务性存储引擎，它需要undo和redo信息，而不管 Innodb 使用的是共享还是独享表空间的方式来存储数据。与事务相关的undo信息以及其他的一些元数据信息，都是存放在innodb_data_home_dir和innodb_data_file_path这两个参数所设定的数据文件中的。所以要想 Innodb 的物理备份有效，就必须备份innodb_data_home_dir和innodb_data_file_path参数所设定的数据文件。

此外，除了上面所说的数据文件之外，Innodb 还有存放自己的redo信息和相关事务信息的日志文件在innodb_log_group_home_dir参数所设定的位置。所以要想 Innodb 物理备份能够有效使用，我们还必须要备份innodb_log_group_home_dir参数所设定的位置的所有日志文件。

热备份及恢复

与冷备份正好相反，热备份是在数据库处于运行状态时直接备份，不影响现有业务的正常进行。热备份又细分为逻辑备份和裸文件备份。

逻辑备份

逻辑备份的最大优点就是对于各种存储引擎，都可以用同样的方法来备份。而冷备份则不同，不同的存储引擎的备份方法也各不相同。因此，对于不同存储引擎混合的数据库，用逻辑备份会更简单一些。

逻辑备份可以说是最简单，也是目前中小型系统最常用的备份方法。逻辑备份主要有以下几种方法：

1. mysqldump
mysqldump是 MySQL 自带的逻辑备份工具。它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，然后将查询出的数据转换成对应的INSERT语句。当我们需要还原恢复这些数据时，只要执行这些INSERT语句，就能将对应的数据还原。所以有的资料也将这种备份方式称为INSERT备份。

恢复数据时可以使用mysql -uroot -p <backup.sql直接调用备份文件执行所有命令，将数据完全恢复到备份时候的状态。如果已经连接上了 MySQL 服务器，那么可以通过source /path/backup.sql来进行恢复。
2. SELECT INTO…OUTFILE
SELECT INTO…OUTFILE语句可以把表数据导出到一个文本文件中，且能将数据库中的表数据以特定分隔符进行分隔后记录在文本文件中，以达到逻辑备份的效果。

这种备份方式与mysqldump方法相比，使用的存储空间更小，数据格式更加清晰明确，编辑方便。但是这种方法只能导出或导入数据的内容，不包括表的结构，如果表的结构文件损坏，则必须先恢复原来的表的结构。而且这种方法不能在同一个备份文件中存在多个表的备份数据，增加了文件维护和恢复的成本。

这种备份方法恢复起来会稍微麻烦一点，需要一个表一个表通过相关命令来进行恢复。当然如果是通过脚本来实现自动多表恢复也是比较方便的。恢复方法有 2 个，一个是通过 MySQL 的LOAD DATA INFILE命令来恢复数据，另一种方法就是通过 MySQL 提供的使用工具 mysqlimport 来进行恢复。
3. mydumper
mydumper是针对 MySQL 数据库备份的一个轻量级第三方的开源工具，备份方式为逻辑备份。它支持多线程，备份速度远高于原生态的mysqldump以及其它众多优异特性。与其配套的相应恢复数据为myloader工具。

我们可以看出所谓的逻辑备份就是备份 SQL 语句，然后恢复数据时执行备份 SQL，从而实现数据库数据的重现。逻辑备份完成后所形成的文件都可以直接编辑。

逻辑备份的作用如下：

• 通过逻辑备份，我们可以仅仅恢复备份中的部分数据而不需要全部恢复。不会影响不相关的数据；
• 通过全库的逻辑备份，我们可以在新的 MySQL 环境下完全重建出一个与备份时完全一样的数据库，并且不受平台类型限制；
• 通过特定条件的逻辑备份，我们可以将某些特定数据轻松迁移（或者同步）到其它的 MySQL 或另外的数据库环境。

裸文件备份

裸文件备份主要在底层复制数据文件，所以备份速度要比逻辑备份更快。

我们利用 Percona 公司发布的一个 XtraBackup 热备份工具来完成裸文件备份，据官方介绍它是世界上唯一一款开源的能够对 InnoDB 和 XtraDB 数据库进行热备的工具。

它的优点就是备份与恢复过程的速度很快，安全可靠，而且在备份过程中不会缩表，不影响现有业务。但它目前还是不能对表结构文件和其它非事务类型的表进行备份。

打赏