GTID环境下mysqldump set-gtid-purged取值

转载

mb5ff980f81f3d8 2021-03-17 15:51:00

$ mysqldump --help | grep 'set-gtid-purged' -A 10 

  --set-gtid-purged[=name] Add 'SET @@GLOBAL.GTID_PURGED' to the output.

Possible values for this option are ON, OFF and AUTO.

If ON is used and GTIDs are not enabled on the server, an error is generated. If OFF is used, this option does nothing.

If AUTO is used and GTIDs are enabled on the server, 'SET @@GLOBAL.GTID_PURGED' is added to the output. If GTIDs are disabled, AUTO does nothing.

If no value is supplied then the default (AUTO) value will be considered.

从命令提供的注释中可以看出，其实该参数有3种取值：

控制是否在备份文件中添加SET @@GLOBAL.GTID_PURGED语句。

1. set-gtid-purged=0|off 不添加

2. set-gtid-purged=1|on 如果gtid没有开启，则报错;如果开启gtid，则添加。

3. 如果没有提供set-gtid-purged,默认是auto，如果gtid没有开启，不添加；如果开启gtid,则添加。


如果 set-gtid-purged=1 在mysqldump文件中会有两条 

SET @@SESSION.SQL_LOG_BIN= 0;

SET @@GLOBAL.GTID_PURGED='85e07268-dfa5-12e7-b0bc-080027a59108:1-2';


这样导入时可能造成主备不一致情况。 
所以，对GTID使用mysqldump 要注意 set-gtid-purged的指定

==============================================================================================================

文档二：

gtid环境下mysqldump对于set-gtid-purged的取值

gtid环境备份的时候，还在为set-gtid-purged=0|1的选择而烦恼吗，一起来分析一下。

GTID环境下mysqldump set-gtid-purged取值_sql

[mysql@lxd-vm1@/home/mysql]$ mysqldump --help | grep 'set-gtid-purged' -A 10
  --set-gtid-purged[=name] 
                      Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible
                      values for this option are ON, OFF and AUTO. If ON is
                      used and GTIDs are not enabled on the server, an error is
                      generated. If OFF is used, this option does nothing. If
                      AUTO is used and GTIDs are enabled on the server, 'SET
                      @@GLOBAL.GTID_PURGED' is added to the output. If GTIDs
                      are disabled, AUTO does nothing. If no value is supplied
                      then the default (AUTO) value will be considered.

GTID环境下mysqldump set-gtid-purged取值_sql

从命令提供的注释中可以看出，其实该参数有3种取值：

控制是否在备份文件中添加SET @@GLOBAL.GTID_PURGED语句。

1. set-gtid-purged=0|off 不添加。

2. set-gtid-purged=1|on 如果gtid没有开启，则报错;如果开启gtid，则添加。

3. 如果没有提供set-gtid-purged,默认是auto，如果gtid没有开启，不添加；如果开启gtid,则添加。

GTID环境下mysqldump set-gtid-purged取值_sql

mysqldump -h5.5.5.101 -uroot -proot --single-transaction --set-gtid-purged=off lxddb t1 > lxddb_t1.sql
[mysql@lxd-vm1@/home/mysql]$ egrep -v '^$|^--|^/' lxddb_t1.sql 
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `i1` int(11) NOT NULL DEFAULT '0',
  `i2` int(11) NOT NULL DEFAULT '0',
  `d` date DEFAULT NULL,
  PRIMARY KEY (`i1`,`i2`),
  KEY `k_d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `t1` WRITE;
INSERT INTO `t1` VALUES (3,1,'1998-01-01'),(3,2,'1999-01-01'),(3,3,'2000-01-01'),(3,4,'2001-01-01'),(3,5,'2002-01-01');
UNLOCK TABLES;
[mysql@lxd-vm1@/home/mysql]$

GTID环境下mysqldump set-gtid-purged取值_sql

mysqldump -h5.5.5.101 -uroot -proot --single-transaction --set-gtid-purged=on lxddb t1 > lxddb_t1.sql
[mysql@lxd-vm1@/home/mysql]$ egrep -v '^$|^--|^/' lxddb_t1.sql 
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED='84e06268-dfa5-11e7-b0bc-080027a59108:1-2';
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `i1` int(11) NOT NULL DEFAULT '0',
  `i2` int(11) NOT NULL DEFAULT '0',
  `d` date DEFAULT NULL,
  PRIMARY KEY (`i1`,`i2`),
  KEY `k_d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `t1` WRITE;
INSERT INTO `t1` VALUES (3,1,'1998-01-01'),(3,2,'1999-01-01'),(3,3,'2000-01-01'),(3,4,'2001-01-01'),(3,5,'2002-01-01');
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
[mysql@lxd-vm1@/home/mysql]$