mysqldump导出部分数据必须加--set-gtid-purged=off参数
解决的问题一:如果导入的主库实例已经存在了gtid全局事务标识符,那么导出开启gtid的实例时就必须加上--set-gtid-purged=OFF,因为不加的话直接导出的SQL文件中会有设置SET @@GLOBAL.GTID_PURGED这行,就会导致报错:GTID_PURGED can only be set when GTID_EXECUTED is empty。3.当时数据导入后,后续已经有
-bash-4.2$ ./mysqldump -uroot -p1234 mysql test1 >/home/mysql/test1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
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.
mysql> drop table test1;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test1;
ERROR 1146 (42S02): Table 'mysql.test1' doesn't exist
mysql> source /home/mysql/test1.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysql> select * from test1;
+----+----------+
| id | name |
+----+----------+
| 5 | jjj |
| 6 | ppp |
| 4 | rrr |
| 9 | saf |
| 7 | WANGWANG |
| 1 | www |
| 2 | xxx |
| 3 | yyy |
+----+----------+
8 rows in set (0.00 sec)
mysql>
vi /home/mysql/test1.sql
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='0f7a8bd3-d7cd-11ef-9ed5-00505695e953:1-6';
DROP TABLE IF EXISTS `test1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_1` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test1`
--
LOCK TABLES `test1` WRITE;
/*!40000 ALTER TABLE `test1` DISABLE KEYS */;
INSERT INTO `test1` VALUES (5,'jjj'),(6,'ppp'),(4,'rrr'),(9,'saf'),(7,'sdafaf'),,
(1,'www'),(2,'xxx'),(3,'yyy');
/*!40000 ALTER TABLE `test1` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
不管是mysqldump还是mysqlpump在使用--set-gtid-purged=OFF参数时都会将设置gtid的语句SET @@GLOBAL.GTID_PURGED和SET @@SESSION.SQL_LOG_BIN取消掉 ,所以加上--set-gtid-purged=OFF参数可以解决两个问题:
解决的问题一:如果导入的主库实例已经存在了gtid全局事务标识符,那么导出开启gtid的实例时就必须加上--set-gtid-purged=OFF,因为不加的话直接导出的SQL文件中会有设置SET @@GLOBAL.GTID_PURGED这行,就会导致报错:GTID_PURGED can only be set when GTID_EXECUTED is empty。
解决的问题二:导入的主库实例下面还挂有从库,那么导出开启gtid的实例时就必须加上--set-gtid-purged=OFF,因为不加的话直接导出的SQL文件中会有设置SET @@SESSION.SQL_LOG_BIN=0这行,就会导致导入的数据不写二进制日志,没有二进制日志从库也就没有数据了。
2.mysqldump部分导出一定要加--set-gtid-purged=off
3.当时数据导入后,后续已经有新的数据进来了,如果使用root重新导入,新的数据就会被删掉了。
4.如果使用root执行,主从数据将不一致
魔乐社区(Modelers.cn) 是一个中立、公益的人工智能社区,提供人工智能工具、模型、数据的托管、展示与应用协同服务,为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作,由全产业链共同建设、共同运营、共同享有,推动国产AI生态繁荣发展。
更多推荐


所有评论(0)