MySQL 学习笔记（五）

mysqldump 与 –set-gtid-purged 设置

(1)  mysqldump

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the –single-transaction option is not used, and (as of MySQL 5.7.31) PROCESS if the –no-tablespaces option is not used.

mysqldump advantages include the convenience and flexibility of viewing or even editing the output before restoring. You can clone databases for development and DBA work, or produce slight variations of an existing database for testing. It is not intended as a fast or scalable solution for backing up substantial amounts of data. With large data sizes, even if the backup step takes a reasonable time, restoring the data can be very slow because replaying the SQL statements involves disk I/O for insertion, index creation, and so on. For large-scale backup and restore, a physical backup is more appropriate, to copy the data files in their original format that can be restored quickly.

(2)  –set-gtid-purged=value

This option enables control over global transaction ID (GTID) information written to the dump file, by indicating whether to add a SET @@GLOBAL.gtid_purged statement to the output. This option may also cause a statement to be written to the output that disables binary logging while the dump file is being reloaded. The following table shows the permitted option values.

The default value is AUTO.

A partial dump from a server that is using GTID-based replication requires the –set-gtidpurged={ON|OFF} option to be specified. Use ON if the intention is to deploy a new replication slave using only some of the data from the dumped server. Use OFF if the intention is to repair a table by copying it within a topology. Use OFF if the intention is to copy a table between replication topologies that are disjoint and will remain so.

(3)  –set-gtid-purged 与 导出文件中SET @@SESSION.SQL_LOG_BIN=0

The –set-gtid-purged option has the following effect on binary logging when the dump file is reloaded:

• –set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not added to the output.

• –set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to the output.

• –set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added to the output if GTIDs are enabled on the server you are backing up (that is, if AUTO evaluates to ON).

(4)  举例说明

 在开启GTID模式的实例上执行mysqldump，假如导出命令如下:

/usr/local/mysql/bin/mysqldump --master-data=2 -u账……号 -p密……码 --databases 数据库1 数据库2 --single-transaction -R --triggers > /data/dbdump/db1_db2_dump.sql

执行，我们会收到一个Warning,如下：

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don"t want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

命令执行后，查看导出的文件，会在文件的开头看到以下命令:

...................................................................................................。。。。。。。。。。。。。。。
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0; --
-- GTID state at the beginning of the backup 
--

SET @@GLOBAL.GTID_PURGED="66fe6059-18c7-22e6-1d21-000c27cswbda:1908761-14";

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE="1ogbin-003413", MASTER_LOG_POS=999;

--
...................................................................................................。。。。。。。。。。。。。。。

可以看出，在开启GITD的server上，执行mysqldump,将–set-gtid-purged 设置为on 或者不设置（The default value is AUTO.）时，产生的sql文件，会显式指明（导入时会执行）GLOBAL.GTID_PURGED 和将当前session 设置为SQL_LOG_BIN= 0。

(5)  场景延申

假设：当前有一个mysql 集群123，一主一从，主为serverA1，从为Server B1，现在又新搭建了一个集群321， 也是一主一从（注意已搭建主从复制）一主一从，主为serverA2，从为Server B2.

目前需求是将集群123 中的部分数据库 –数据库2、数据库4—-同步到新集群321 中，并建立主从。

 示意图如下:

注意四台机器都已开启GTID模式。

我们使用mysqldump来完成这个工作会遇到什么挑战呢？

首先是导出，导出就是上面的命令，没有带–set-gtid-purged。接着时修改ServerA2实例的my.cnf配置文件，指定复制的库。然后，将dump文件copy到新实例serverA2上。

现在我们主要看看导入时遇到的问题。

（1）导入命令

mysql -u用……户……名 -p密……码 < /data/dumprestore/db1_db2_dump.sql　　

 收到报错信息

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

解决方案,执行以下命令

reset master;

(2) 如果执行了 reset master，Server A2 和 Server B2的主从关系就会坏掉。

(3) 如果在导入前，主从都是没有业务数据库的新集群。主从修复，可以都执行了 reset master，重新搭建主从。

change master to
master_host="IP地址",
master_port=端口号,
master_user="用 ……户 ……名",
master_password="密……码",
master_auto_position=1;

因为是刚刚reset master ,导入时不会遇到ERROR 1840 …,，可以将数据灌入到Server A2中。

但是，这个时候你会发现 Server A2 和 Server B2的主从已不能同步新导入的数据。

因为你执行导入的session,执行设定了SET @@SESSION.SQL_LOG_BIN= 0;

(4)执行时间过长

建议 nohup,后台执行。

mysql导入数据耗时远远大于mysqldump导出耗时。测试中，我们将大小都是60G的两个数据库备份还原，mysqldump 执行55分钟，mysql导入执行了5个小时。

(5)灌入数据后，可以通过xtraback备份还原来修复重建ServerA2 和 ServerB2的主从。

(6) 如果导入数据时不想破坏掉ServerA2 和 ServerB2的主从关系，可以考虑，导入前先注释掉SET @@SESSION.SQL_LOG_BIN= 0;，再导入。

(7) 另外，如果场景更复杂，例如新集群321 需要承接、同步来自多个集群（不仅仅是来自集群123）的数据，也可以考虑 通过传统模式搭建主从（指定binlog文件+位点），这个场景下，mysqldump 时，

–set-gtid-purged 设置为off，或者导出后，考虑将SET @@SESSION.SQL_LOG_BIN= 0;和SET @@GLOBAL.GTID_PURGED=”？？？？？？？？？？？？？？”;注释掉。

查看位点的命令:

head -n 100 dump文件.sql | grep "CHANGE MASTER TO"