VitePress

Appearance

MySQL 数据库备份与恢复 ​

冷备份，号称MySQL最简单的备份方式！ ​

在《MySQL备份类型》一节介绍过冷备份，冷备份可以称为 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 数据库的主版本号一致。只有两个 MySQL 数据库主版本号相同时，才能保证它们的数据文件类型是相同的。

关于 MySQL 数据库服务器的版本号 ​

第一个数字表示主版本号，例如 MySQL 5.5.21 和 MySQL 5.5.01 这两个版本的主版本号都是 5，所以这两个数据库拥有相同类型的数据文件。

MySQL冷备份所需物理文件 ​

在《MySQL冷备份》一节我们介绍了冷备份主要通过复制相关数据文件来实现，下面主要介绍 MyISAM 和 InnoDB 存储引擎需要备份什么物理文件。

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 是以共享表空间存放数据还是以独享表空间方式存储数据。

以上几个参数的相关使用说明我们在《MySQL InnoDB存储引擎》一节中做了相应的解释，在 MySQL 的官方手册中也有较为详细的说明，这里就不再赘述了。

如果使用的是共享表空间的存储方式，那么需要备份 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 参数所设定的位置的所有日志文件。

MySQL热备份（实时备份）及恢复 ​

与冷备份正好相反，热备份是在数据库处于运行状态时直接备份，不影响现有业务的正常进行。热备份又细分为逻辑备份和裸文件备份，下面我们介绍逻辑备份和冷备份以及它们常用的备份和恢复的方法。

逻辑备份 ​

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

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

1）mysqldump ​

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

恢复数据时可以使用 mysql -uroot -p <backup.sql 直接调用备份文件执行所有命令，将数据完全恢复到备份时候的状态。如果已经连接上了 MySQL 服务器，那么可以通过 source /path/backup.sql 来进行恢复。

我们在《MySQL mysqldump备份数据库》和《MySQL恢复数据库》中详细介绍了上面所讲的备份和恢复数据的方法。

2）SELECT INTO…OUTFILE ​

SELECT INTO…OUTFILE 语句可以把表数据导出到一个文本文件中，且能将数据库中的表数据以特定分隔符进行分隔后记录在文本文件中，以达到逻辑备份的效果。

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

这种备份方法恢复起来会稍微麻烦一点，需要一个表一个表通过相关命令来进行恢复。当然如果是通过脚本来实现自动多表恢复也是比较方便的。恢复方法有 2 个，一个是通过 MySQL 的 LOAD DATA INFILE 命令来恢复数据，另一种方法就是通过 MySQL 提供的使用工具 mysqlimport 来进行恢复。

我们在《MySQL导出表数据》和《MySQL数据库恢复（LOAD DATA）》中详细介绍了上面所讲的备份和恢复数据的方法。

3）mydumper ​

mydumper 是针对 MySQL 数据库备份的一个轻量级第三方的开源工具，备份方式为逻辑备份。它支持多线程，备份速度远高于原生态的 mysqldump 以及其它众多优异特性。与其配套的相应恢复数据为 myloader 工具。DBA 称 mydumper 和 myloader 为备份界的“小钢炮”。

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

逻辑备份的作用如下：

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

裸文件备份 ​

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

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

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

实际应用中，如何将MySQL数据库迁移到其它机器？ ​

数据库迁移就是把数据从一个系统移动到另一个系统上，迁移过程其实就是在源数据库备份和目标数据库恢复的过程组合。迁移的原因是多种多样的，比如：

  • 需要安装新的数据库服务器
  • MySQL 版本更新
  • 数据库管理系统的变更（如从 SQL Server 迁移到 MySQL）

根据实际操作等情况，可以将数据库迁移操作分成以下 3 种形式。

  • 相同版本 MySQL 数据库之间的迁移。
  • 不同版本 MySQL 数据库之间的迁移。
  • 不同数据库间的迁移。

下面将详细介绍数据库迁移的各种方式。

1. 相同版本的迁移 ​

相同版本的 MySQL 数据库是指主版本号一致的数据库。主版本号一致的数据库迁移最容易实现。由于迁移前后 MySQL 数据库的主版本号相同，所以可以通过复制数据库目录来实现数据库迁移。

最安全和最常用的方式是通过使用 mysqldump 命令进行数据库备份，然后使用 mysql 命令将备份文件还原到新的 MySQL 数据库。迁移时的备份和还原操作可以同时执行。

假设从一个名为 hostname1 的机器中备份出所有数据库，然后将这些数据库迁移到名为 hostname2 的机器上，具体语法形式如下：

mysqldump -h hostname1 -u root -password=password1 -all-databases
|
mysql -h hostname2 -u root -password=password2

其中：

  • 符号“|”用来实现将命令 mysqldump 备份的文件送给 mysql 命令；
  • password1 为 hostname1 主机上 root 用户的密码；
  • password2 为 hostname2 主机上 root 用户的密码；
  • -all-databases 表示迁移全部的数据库，可省略。

通过上述语句就可以直接迁移。

2. 不用版本的迁移 ​

不同版本的 MySQL 数据库之间的数据迁移通常是 MySQL 升级的原因。例如，服务器使用 4.0 版本的 MySQL 数据库，现在要升级为 5.7 版本的。这样就需要不同版本的 MySQL 数据库之间进行数据迁移。

不同版本下的数据库迁移，分为 2 种方式：

  • 低版本数据库向高版本数据库进行迁移
  • 高版本数据库向低版本数据库进行迁移

低版本数据库向高版本数据库进行迁移时，由于高版本会兼容低版本，所以该种方式也是最容易实现的操作。对于存储类型为 MyISAM 的表，最安全和最常用的操作是直接复制数据文件。对于存储类型为 InnoDB 的表，最安全和最常用的操作是执行 mysqldump 命令进行备份和执行 mysql 命令还原恢复数据。

但是高版本数据库向低版本数据库进行迁移时，因为高版本数据库可能有一些新的特性，这些特性是低版本数据库所不具有的，所以数据库迁移时要特别小心，最好使用 mysqldump 命令来进行备份，避免迁移时造成数据丢失。

3. 不同数据库的迁移 ​

不同数据库之间的迁移是指从其它类型的数据库迁移到 MySQL 数据库，或者从 MySQL 数据库迁移到其他类型的数据库。例如，某个网站原来使用 Oracle 数据库，因为运营成本太高等诸多原因，希望改用 MySQL 数据库。或者，某个管理系统原来使用 MySQL 数据库，因为某种特殊性能的要求，希望改用 Oracle 数据库。这样的不同数据库之间的迁移也经常会发生。但是这种迁移没有普通适用的解决办法。

其它数据库也有类似 mysqldump 这样的备份工具，可以将数据库中的文件备份成 sql 文件或普通文本。但是，不同的数据库厂商并没有完全按照 SQL 标准来设计数据库，这就造成了不同数据库使用的 SQL 语句的差异。例如，微软的 SQL Server 软件使用的是 T-SQL 语言。T-SQL 中包含了非标准的 SQL 语句。这就造成了 SQL Server 和 MySQL 的 SQL 语句不能兼容。

除了 SQL 语句存在不兼容的情况外，不同的数据库之间的数据类型也有差异。例如，MySQL 不支持 SQL Server 中的 ntext、 Image 等数据类型。同样，SQL Server 也不支持 MySQL 中的 ENUM 和 SET 等数据类型。数据类型的差异也造成了迁移的困难。

从某种意义上说，这种差异是商业数据库公司故意造成的壁垒，这种行为是阻碍数据库市场健康发展的。

但是不同数据库服务器间的迁移并不是完全不可能。在 Windows 操作系统下，如果要实现从 MySQL 数据库服务器向 SQL SERVER 数据库服务器迁移，可以通过 MyODBC 来实现；如果要实现从 MySQL 数据库服务器向 ORACLE 数据库服务器迁移，可以先通过执行 mysqldump 命令导出 sql 文件，然后手动修改 sql 文件中的 CREATE 语句。