MySQL 数据备份与恢复（十三）

数据备份与恢复

• 保证数据安全最重要的一个措施是确保对数据进行定期备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复，这样就尽可能地降低了意外原因导致的损失。

数据备份

使用 mysqldump 命令备份

• mysqldump 命令执行时，可以将数据库备份成一个文本文件，该文件实际上包含了多个 CREATE 和 INSERT 语句，使用这些语句可以重新创建表和插入数据。

• mysqldump 备份单个数据库语句，基本语法格式如下：

  mysqldump -u user -h host -p dbname [tbname1 [tbname2...] ] > filename.sql
  

  tbname 为 dbname 数据库中需要备份的数据表，可以指定多个需要备份的表，多个表名之间用空格隔开；

  右箭头符号 “>” 告诉 mysqldump 将备份的数据表的定义和数据写入备份文件；

  filename.sql 为备份文件的名称。

• mysqldump 备份多个数据库语句，基本语法格式如下：

  mysqldump -u user -h host -p --databases [dbnane1 [dbname2...]] > filename.sql
  

  使用 --databases 参数之后，必须指定至少一个数据库的名称，多个数据库名称之间用空格隔开。

  另外，使用 --all-databases 参数可以备份系统中所有的数据库，语句如下：

  mysqldump -u user -h host -p --all-databases > filename.sql
  

• 备份文件包含的信息：

  • 文件开头首先标明备份文件使用的 mysqldump 工具的版本号；

  • SET 语句：这些语句将一些系统变量赋给用户定义变量，以确保被恢复的数据库的系统变量和原来备份时的变量相同；

  • 以 “--” 字符开头的语句为行为注释语句；

  • 以 “/*!” 开头、“*/” 结尾的语句为可执行的 MySQL 注释，这些语句可以被 MySQL 执行，但在其他数据库管理系统将被作为注释忽略，这可以提高数据库的可移植性。

  • 以数字开头的语句，这些数字代表了 MySQL 版本号，该数字表明这些语句只有在指定的 MySQL 版本或者比该版本高的情况下才能执行。

    例如：

    /*!40101 SET CHARACTER_SET_CLIENT = @OLD_CHARACTER_SET_CLIENT */
    

    表明这个语句只有在 MySQL 版本号为 4.01.01 或者更高的条件下才可以执行。

• mysqldump 提供许多选项，允许帮助命令 mysqldump --help，可以获得特定版本的完整选项列表。

• 提示：

  • 如果在服务器上进行备份，并且表均为 MyISAM 表，应考虑使用 mysqlhotcopy，因为可以更快地进行备份和恢复。

  • 如果使用最新版本的 mysqldump 程序备份数据，并用于恢复到比较旧版本的 MySQL 服务器中，则不要使用 --opt 或 -e 选项。

    --opt：该选项是速记，等同于指定 --add-drop-tables、 --add-locking、 --create-option、--disable-keys、--extended-insert、--lock-tables-quick 和 --set-charset。它可以快速进行转储操作并产生一个能很快装入 MySQL 服务器的转储文件。该选项默认开启，但可以用 --skip-opt 禁用。

    --extended-insert，-e：使用包括几个 VALUES 列表的多行 INSERT 语法。这样使转储文件更小，重载文件时可以加速插入。

直接复制整个数据库目录

• 因为 MySQL 表保存为文件格式，所以可以直接复制 MySQL 数据库的存储目录及文件进行备份。
• 备份前需要对相关执行 LOCK TABLES 操作，这样当复制数据库目录的中文件时，允许其他客户继续查询表；然后对表执行 FLUSH TABLES 操作，确保开始备份前将所有激活的索引页写入硬盘，或者也可以停止 MySQL 服务再进行备份操作。
• 提示：在 MySQL 版本中，第一个数字表示主版本号，主版本号相同的 MySQL 数据库文件格式相同。

使用 mysqlhotcopy 工具快速备份

• mysqlhotcopy 是一个 Perl 脚本。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快途径，但它只能运行在数据库目录所在的机器上，并且只能备份 MyISAM 类型的表。mysqlhotcopy 在 UNIX 系统中运行。

• mysqlhotcopy 命令语法格式如下：

  mysqlhotcopy db_name_1,...db_name_n /path/to/new_directory
  

  /path/to/new_directory 指定备份文件目录。

• 执行 mysqlhotcopy，必须可以访问备份的表文件，具有那些表的 SELECT 权限、RELOAD 权限（以便能够执行 FLUSH TABLES）和 LOCK TABLES 权限。

• 提示：mysqlhotcopy 只是将表所在的目录复制到另一个位置，只能用于备份 MyISAM 和 ARCHIVE 表。备份 InnoDB 类型的数据表时会出现错误信息。由于它复制本地格式的文件，因此也不能移植到其他硬件或操作系统下。

数据恢复

使用 MySQL 命令恢复

• 备份的 sql 文件中包含 CREATE、INSERT 语句。MySQL 命令可以直接执行文件中的这些语句。其语法如下：

  mysql -u user -p [dbname] < filename.sql
  

  执行该语句前，必须先在 MySQL 服务器中创建指定数据库。

• 在已经登录 MySQL 服务器下，使用 source 命令导入 sql 文件。source 语句语法如下：

  source filename
  

  执行该语句前，必须使用 use 语句选择数据库。且 filename 中路径分隔符用反斜杠 “\” 。

直接复制到数据库目录

• 通过这种方式恢复时，必须保存备份数据的数据库和待恢复的数据库服务器的主版本号相同。而且这种方式只对 MyISAM 引擎的表有效，对于 InnoDB 引擎的表不可用。
• 执行恢复操作以前关闭 MySQL 服务，将备份的文件或目录覆盖 MySQL 的 data 目录，启动 MySQL 服务。对于 Linux/Unix 操作系统来说，复制完文件需要将文件的用户和组更改为 MySQL 运行的用户和组，通常用户是 MySQL，组也是 MySQL。

mysqlhotcopy 快速恢复

• mysqlhotcopy 备份后的文件也可以用来恢复数据库，在 MySQL 服务器停止运行时，将备份的数据库文件复制到 MySQL 存放数据的位置（MySQL 的 data 文件夹），重新启动 MySQL 服务器即可。执行该操作前需要指定数据库文件的所有者，输入语句如下：

  chown -R mysql.mysql /var/lib/mysql/dbname
  

  从 mysqlhotcopy 复制的备份恢复到数据库，输入语句如下：

  cp -R 备份文件所在路径 usr/local/mysql/data
  

  执行完该语句，重启服务器，MySQL 将恢复到备份状态。

• 提示：如果需要恢复的数据库已经存在，则需使用 DROP 语句输出已经存在的数据库之后恢复才能成功。另外，MySQL 不同版本之间必须兼容，恢复之后的数据才可以使用。

数据库迁移

• 数据库迁移就是把数据从一个系统移动到另一个系统上。数据迁移有以下原因：
  1. 需要按照新的数据库服务器。
  2. MySQL 版本更新
  3. 数据库管理系统的变更（如从 Microsoft SQL Server 迁移到 MySQL）

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

• 最常用和最安全的方式是使用 mysqldump 命令导出数据，然后在目标数据库服务器使用 MySQL 命令导入。

• 将 A 主机上的 MySQL 数据库全部迁移到 B 主机上的执行命令如下：

  mysqldump -h A -uroot -ppassword dbname |
  mysql -h B -uroot -ppassword
  

  mysqldump 导入的数据直接通过管道符 “|” 传给 MySQL 命令导入到主机 B 数据库中；

  dbname 为需要迁移的数据库名称，如果要迁移全部数据库，可使用参数 --all-databases 。

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

• 由于数据库升级等原因，需要将较旧版本 MySQL 数据库中的数据迁移到较新版本的数据库中。MySQL 服务器升级时，需要先停止服务，然后卸载旧版本，并安装新版的 MySQL。
• 如果想保留旧版本的用户控制访问信息，则需要备份 MySQL 中的 MySQL 数据库，在新版本 MySQL 安装完成之后，重新读入 MySQL 备份文件中的信息。
• 旧版本与新版本的 MySQL 可能使用不同的默认字符集。如果数据库中有中文数据时，迁移过程中需要对默认字符集进行修改，不然可能无法正常显示结果。

不同数据库之间的迁移

• 迁移之前，需要了解不同数据库的架构，比较它们之间的差异。不同数据库中定义相同类型数据的关键字可能会不同。
• 另外，数据库厂商并没有完全按照 SQL 标准来设计数据库系统，导致不同的数据库系统的 SQL 语句有所差别，因此在迁移时必须对这些语句进行语句映射处理。
• 数据库迁移可以使用一些工具。例如在 Windows 系统下，可以使用 MyODBC 实现 MySQL 和 SQL Server 之间的迁移。MySQL 官方提供的工具 MySQL Migration Toolkit 也可以在不同数据库间进行数据迁移。

表的导出和导入

• MySQL 数据库中的数据可以导出成 sql 文本文件、xml 文件或者 html 文件。同样这些导出文件也可以导入到 MySQL 数据库中。

使用 SELECT...INTO OUTFILE 导出文本文件

• MySQL 数据库导出数据时，允许使用包含导出定义的 SELECT 语句进行数据的导出操作。
• 该文件被创建到服务器主机上，因此必须拥有文件写入权限（FILE 权限）才能使用此语法。
• “SELECT... INTO OUTFILE ‘filename’ ”形式的 SELECT 语句可以把被选择的行写入一个文件中，filename 不能是一个已经存在的文件。
• SELECT...INTO OUTFILE 语句基本格式如下：

SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTIONS]

--OPTIONS 选项
		FIELDS TERMINATED BY 'value'
	FIELDS [OPTIONALLY] ENCLOSED BY 'value'
	FIELDS ESCAPED BY 'value'
	LINES STARTING BY 'value'
	LINES TERMINATED BY 'value'

• [OPTIONS] 为可选参数选项。OPTIONS 部分的语法包括 FIELDS 和 LINES 子句，可能的取值有：

  • FIELDS TERMINATED BY 'value'：设置字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符 ’\t‘ （tab）。
  • FIELDS [OPTIONALLY] ENCLOSED BY 'value'：设置字段的包围字符，只能为单个字符，如果使用了 OPTIONALLY 则只有 CHAR 和 VARCHAR 等字符数据字段被包括。
  • FIELDS ESCAPED BY 'value'：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为 ’\‘ 。
  • LINES STARTING BY 'value'：设置每行数据开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。
  • LINES TERMINATED BY 'value'：设置每行数据结尾的字符，可以为单个或多个字符，默认值为 ’\n‘。

• FIELDS 和 LINES 两个子句都是自选的，但是如果两个都被指定了，FIELDS 必须位于 LINES 的前面。

• SELECT...INTO OUTFILE 语句可以非常快速地把一个表转储到服务器上。如果想要在服务器主机之外的部分客户主机上创建结果文件，不能使用 SELECT...INTO OUTFILE。在这种情况下，应该在客户主机上使用比如 “ mysqldump -T ” 的命令来生成文件。

• 默认情况下，如果遇到 NULL 值，将会返回 “\N”。

使用 mysqldump 命令导出文本文件

• mysqldump 工具不仅可以将数据导出为包含 CREATE、INSERT 的 sql 文件，也可以导出为纯文本文件。

• mysqldump 导出文本文件的基本语法格式如下：

  mysqldump -T path -u username -p dbname [tables] [OPTIONS] --文件路径需要加双引号
  
  --OPTIONS 选项 (value字符串不需要加引号)
  --fields-terminated-by=value
  --fields-enclosed-by=value
  --fields-optionally-by=value
  --fields-escaped-by=value
  --lines-terminated-by=value
  

  只有指定了 -T 参数才可以同时导出纯文本文件；

  path 表示导出数据的目录；

  tables 为指定要导出的表名称，如果不指定，将导出数据库中所有表；

  [OPTIONS]为可选参数选项，这些选项需要结合 -T 选项使用。

  使用 OPTIONS 常用的取值有：

  • --fields-terminated-by=value：设置字段之间的分隔符，可以为单个或多个字符。默认情况下为制表符 “\t”。
  • --fields-enclosed-by=value：设置字段的包围字符。
  • --fields-optionally-by=value：设置字段的包围字符，只能为单个字符，只能包括 CHAR 和 VARCHAR 等字符数据字段。
  • --fields-escaped-by=value：控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为反斜线 “\”。
  • --lines-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认值为 “\n”。

使用 LOAD DATA INFILE 方式导入文本文件

• MySQL 提供了一些导入数据的工具，这些工具有 LOAD DATA 语句和 source 命令。LOAD DATA INFILE 语句用于高速地从一个文本文件中读取行，并装入一个表。

• LOAD DATA 语句的基本格式如下：

  LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES]
  
  	--OPTIONS 选项
  		FIELDS TERMINATED BY 'value'
  	FIELDS [OPTIONALLY] ENCLOSED BY 'value'
  	FIELDS ESCAPED BY 'value'
  	LINES STARTING BY 'value'
  	LINES TERMINATED BY 'value'
  

  IGNORE number LINES 选项表示忽略文件开始处的行数，number 表示忽略的行数。执行 LOAD DATA 语句需要 FILE 权限。

使用 mysqlimport 命令导入文本文件

• 使用 mysqlimport 可以导入文本文件，并且不需要登录 MySQL 客户端。

• mysqlimport 命令的基本格式如下：

  mysqlimport -u username -p dbname filename.txt [OPTIONS] --文件路径不用加引号
  
  --OPTIONS 选项 (value字符串不需要加引号)
  --fields-terminated-by=value
  --fields-enclosed-by=value
  --fields-optionally-by=value
  --fields-escaped-by=value
  --lines-terminated-by=value
  --ignore-lines=n
  

  注意：mysqlimport 命令不指定导入数据库的表名称，数据表的名称由导入文件名称确定，即文件名作为表名，导入数据之前该表必须存在。若只指定文件名，则该文件只能存放在指定数据库文件夹里。

注意事项

• 使用 mysqldump 和 LOAD DATA 语句时需要注意文件所在文件夹的读写权限，而 mysqlimport 则不需要。

• 如果在导出 txt 文件时指定了一些特殊分隔字符，则恢复语句也要指定这些字符，已确保恢复后数据的完整性和正确性。

• 备份时必须确保没有使用这些表。如果在复制一个表的同时服务器正在修改它，则复制无效。备份文件时，最好关闭服务器。然后重新启动服务器。为了保证数据的一致性，需要在备份文件前执行以下 SQL 语句：

  FLUSH TABLES WITH READ LOCK;
  

  也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证复制过程中不会有新的数据写入。

• mysqldump 如果只指定数据库的名称，则该语句只备份了数据库下所有的表。所以在恢复时，如果指定数据库不存在，则需要创建后再进行恢复。