开启gtid导入报错

导入报错

[root@redis02 data]# mysql -u root -p < ht.sql
Enter password:
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

简单粗暴的玩法先把活干拉在说,vi ht.sql 删除一下几行

--
-- GTID state at the beginning of the backup
--

SET @@GLOBAL.GTID_PURGED='f0019921-6d42-11e8-bd74-0800272bcfc4:1-6,
f27d825f-792f-11e8-b745-0800272bcfc4:1-6';

在导入数据就没报错，这个问题在导出数据时就告警拉

[root@redis01 data]# mysqldump -u root -p --databases ht > ht.sql
Enter password:
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.

查看gtid

 1 mysql> show global VARIABLES LIKE '%GTID%';
 2 +---------------------------------------------------+----------------------------------------------------+
 3 | Variable_name                                     | Value                                              |
 4 +---------------------------------------------------+----------------------------------------------------+
 5 | binlog_gtid_simple_recovery                       | ON                                                 |
 6 | enforce_gtid_consistency                          | ON                                                 |
 7 | group_replication_allow_local_disjoint_gtids_join | OFF                                                |
 8 | group_replication_gtid_assignment_block_size      | 1000000                                            |
 9 | gtid_executed                                     | f0019921-6d42-11e8-bd74-0800272bcfc4:1-6,
10 f27d825f-792f-11e8-b745-0800272bcfc4:1-6 |
11 | gtid_executed_compression_period                  | 1000                                               |
12 | gtid_mode                                         | ON                                                 |
13 | gtid_owned                                        |                                                    |
14 | gtid_purged                                       |                                                    |
15 | session_track_gtids                               | OFF                                                |
16 +---------------------------------------------------+----------------------------------------------------+
17 10 rows in set (0.00 sec)

View Code

提示的很明显，不想用gtid可以把它关闭，改成下面就正常。

mysqldump -u root -p --set-gtid-purged=OFF --databases ht > ht.sql

如果表有外键导入时也可能报错，如果报错用 FOREIGN_KEY_CHECKS解决

set FOREIGN_KEY_CHECKS=0;    #在导入的脚本命令行最前面设置为不检查外键约束
。。。。。。。。。。。。     #导入数据的命令行
set FOREIGN_KEY_CHECKS=1;    #在导入后恢复检查外键约束

任何工具都有一定的局限性用在合适的地方就好。