使用MySQLDUMP进行数据库逻辑备份与恢复

       MEB与PXB底层的技术依赖于物理文件的复制，也就是说它们能够支持的最小粒度，只能到达文件层面。这就意味着，当用户只需要某些行的数据，或者其他特定行的对象时，MEB和PXB将会无法满足要求。因为这类需求都是逻辑层面上的场景，所以本节将引入MySQL社区版自带的MySQLDUMP程序以进行补充。

MySQLDUMP软件介绍

MySQLDUMP概述

       MySQLDUMP主要用于执行逻辑备份，默认会生成一组SQL语句，执行这些语句可以重现原始数据库对象定义和表数据。MySQLDUMP支持转储一个或多个MySQL数据库，大多数用户会使用它来生成SQL或分隔符文本格式的两种类型的转储文件，当然它也支持生成CSV和XML格式的输出。

       MySQLDUMP的优点在于恢复数据之前可以灵活地查看甚至编辑导出的转储文件。可以快速地为开发、测试、运维或DBA克隆数据库，并交付对应的使用环境。它并不是一种用于备份大量数据的快速的或可伸缩的解决方案。对于大数据量，即使是最优化的备份步骤也需要一段合理的时间，恢复数据也可能会非常慢，因为重放SQL语句会涉及磁盘I/O的插入和索引创建等操作。对于大规模的备份和恢复，物理备份更合适，复制数据文件的原始格式，可以实现快速恢复。如果特定实例的数据主要是InnoDB表，或者是InnoDB和MyISAM表的组合，那么我们可以考虑使用MEB来实现。MEB为InnoDB备份提供了最优的性能，同时将影响面控制在最小范围之内；还可以备份来自MyISAM和其他存储引擎的表，并提供多个选项来适应不同的备份场景。

       MySQLDUMP章节的所有案例和场景，默认都是基于非GTID（Global Transaction Identifier，全局事务标识符）模式的环境。如果用户的环境是GTID模式，那么在指定“--all-databases”选项对实例进行全局转储时，建议显式启用“--set-gtid-purged”选项（默认是启用的），以获得InnoDB表数据一致性备份开始时的二进制日志的起始位置；对于未显式使用“--all-databases”选项的所有其他类型备份，必须禁用该选项，即设置“--set-gtid-purged=OFF”，否则会报错并中断转储。这里需要注意一种特殊情况，若使用了“--no-create-info”选项，那么即使显式指定了“--all-databases”选项也仍然会出现转储报错，该场景仍需设置“--set-gtid-purged=OFF”进行规避。

MySQLDUMP转储格式

正如前文所提到的，MySQLDUMP生成的转储文件基本上是SQL或分隔符文本格式，而这取决于它是否指定了关键的“--tab”选项。

·如果没有指定“--tab”选项，那么MySQLDUMP将按照SQL格式写入标准输出。这个输出包括创建转储对象（数据库、表、存储程序等）的CREATE语句，以及将数据加载到表中的INSERT语句。这些输出可以保存到一个文件中，稍后可以使用MySQL客户端程序重新加载，以重新创建转储对象。当然，MySQLDUMP也支持指定相关选项，来修改SQL语句的格式，并控制具体的转储对象。

·如果显式指定了“--tab”选项，MySQLDUMP就会为每个转储表生成两个输出文件。MySQL默认采用制表符分隔的形式，在输出目录中生成名为tbl_name.txt的文本数据文件。同时，MySQL还将表的CREATE TABLE语句发送给了MySQLDUMP，并在输出目录中生成名为tbl_name.sql的元数据定义文件。

MySQLDUMP限制

MySQLDUMP包含但不限于下列限制。

·默认情况下，MySQLDUMP不会转储information_schema、performance_schema或sys等系统库。

·MySQLDUMP不会转储NDB集群的ndbinfo数据库。

·MySQLDUMP不会转储InnoDB创建表空间语句。

·不建议将使用MySQLDUMP生成的转储恢复到启用了GTID模式的MySQL 5.6.9或更早版本的MySQL环境。

·MySQLDUMP包含用于重新创建general_log和slow_query_log表以转储MySQL数据库的语句，但日志表的内容不会被转储。

SQL格式转储与加载

本节将介绍如何使用MySQLDUMP创建SQL格式的转储文件。使用“--all-databases”或“--databases”选项时，MySQLDUMP会在每一个数据库的转储输出之前写入CREATE DATABASE和USE相关的SQL语句，这样可以确保在重新加载转储文件时，如果发现相关的数据库不存在，则创建对应的数据库，并将其作为默认数据库，以便将数据库的内容加载到原来的数据库中。如果希望在重新创建每个数据库之前强制删除旧的数据库，就可以使用“--add-drop-database”选项，这样MySQLDUMP会在每个CREATE DATABASE语句之前先写入一个DROP DATABASE语句。

如果导出的转储文件是由MySQLDUMP使用“--all-databases”或“--databases”选项创建的，那么就不需要指定默认的数据库来加载数据。处理这类转储文件比较简单，直接加载便能实现导入恢复。但如果准备导入的转储文件，不是通过指定“--all-databases”或“--databases”选项生成的，那么在目标环境中，就必须事先创建好相关的数据库名称。然后，在导入恢复的命令行中指定对应的数据库。因此，建议用户尽量使用“--all-databases”或“--databases”选项以简化转储，下文所介绍的场景和案例，都是基于该原则进行的。

设置MySQL默认选项文件

MySQLDUMP客户端程序在执行转储时，会读取默认选项文件中的[client]和[mysqldump]选项组中的配置，并且支持用后缀的形式来区分不同的实例。下文介绍的MySQLDUMP程序，可复用下列的[client]选项组设置。

（1）创建[client@scene1]选项组

使用@scene1后缀唯一标识，添加scene1实例连接信息，命令如下：

shell> vi /etc/my.cnf

[client@scene1]

host=localhost

user=root

password='Abcd321#'

socket=/mysql/product/scene1/data/mysql.sock

（2）创建[client@scenetmp]选项组

同理，使用@scenetmp后缀唯一标识，添加scenetmp实例连接信息，命令如下：

shell> vi /etc/my.cnf

[client@scenetmp]

host=localhost

user=root

password='Abcd321#'

socket=/mysql/product/scenetmp/data/mysql.sock

全局转储与加载

即使指定了“--all-databases”选项进行全局备份，很多MySQL存储程序也是默认不会导出转储的，因此需要显式指定特定选项。MySQLDUMP通常使用下列选项来处理这些对象。

·--events：转储调度程序事件。

·--routines：转储存储过程和函数。

·--triggers：转储基于表的触发器。

默认情况下，程序会自动启用“--trigger”选项，因此当表被转储时，会伴随着它们拥有的所有触发器对象。但“--routines”和“--events”选项默认是被禁用的，所以必须显式指定这些选项才能转储相应的存储过程、存储函数和调度事件等对象。当然，如果要显式禁用这些对象，可以指定下列选项：--skip-events、--skip-routine或--skip-trigger。

通过MySQLDUMP生成的转储文件，若其中包括存储函数，并且准备在目标环境上加载，则需要先启用log_bin_trust_function_creators变量，不然会报错，其他案例也是与之类似。示例代码如下：