ERROR 3546 (HY000) at line 26: @@GLOBAL.GTID_PURGED cannot .....overlap with @@GLOBAL.GTID_EXECUTED

Eucalyptus2024-04-24

报错场景:

将master 节点的MySQL 备份导入到slave 节点时报错

报错信息:

ERROR 3546 (HY000) at line 26: GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with GLOBAL.GTID_EXECUTED

master备份时使用的语法

1	MySQL -uroot -p --all-databases > 2024.4.24.sql

报错原因:

如果在启用了GTID复制的环境中,不使用 --set-gtid-purged OFF参数进行备份,然后将备份数据导入到另一个同样启用GTID的数据库服务器,可能会引发GTID冲突。这是因为备份文件中包含了原服务器的GTID历史信息,导入时会尝试在目标服务器上应用这些GTID,如果目标服务器已经存在相同的GTID 例如,因为两台服务器原本就共享过一部分复制历史 ,则可能导致事务重复或跳过的问题。

解决方法:

备份时添加 --set-gtid-purged OFF参数

1	MySQL -uroot -p --all-databases --set-gtid-purged OFF > 2024.4.24.sql

添加--set-gtid-purged OFF参数后成功导入

知识点

--set-gtid-purged OFF 是 mysqldump 命令的一个参数,用于控制在生成数据库备份文件时如何处理全局事务标识符 Global Transaction Identifier, GTID 。具体来说,该参数的作用如下:

阻止生成 GTID 相关的 SQL 语句:

当在备份过程中指定 --set-gtid-purged OFF 时,mysqldump 不会在生成的备份脚本中包含 SET GLOBAL.GTID_PURGED 或 SET SESSION.GTID_NEXT 这类与GTID相关的SQL语句。这些语句通常用来标识备份文件中包含的事务已提交且应被视为已从服务器的GTID历史中清除。

避免导入时的冲突:

如果在启用了GTID复制的环境中,不使用此参数进行备份,然后将备份数据导入到另一个同样启用GTID的数据库服务器,可能会引发GTID冲突。这是因为备份文件中包含了原服务器的GTID历史信息,导入时会尝试在目标服务器上应用这些GTID,如果目标服务器已经存在相同的GTID 例如,因为两台服务器原本就共享过一部分复制历史 ,则可能导致事务重复或跳过的问题。

使用 --set-gtid-purged OFF 可以避免在备份文件中记录GTID信息,从而使得导入数据时不会自动尝试更新目标服务器的GTID状态,降低了因GTID冲突而导致的数据恢复失败的风险。
应用场景:

当备份单个数据库或特定表,并计划将这些数据导入到另一个独立的、GTID模式下运行的MySQL实例时,使用 --set-gtid-purged OFF 是合适的。在这种情况下,由于只迁移部分数据,而不是完整的复制链路,避免携带源服务器的GTID历史有助于保证目标服务器的GTID一致性。

如果备份目的是为了在相同GTID环境下的不同节点间进行数据迁移或恢复,通常不需要使用此参数,因为在这种场景下保持GTID历史的连续性对于复制同步至关重要。
总结:

--set-gtid-purged OFF 参数在使用 mysqldump 备份启用了GTID复制的MySQL数据库时,用于禁用在备份脚本中记录GTID相关信息。这样做有助于避免在将备份数据导入到另一个GTID环境中时可能出现的GTID冲突,特别适用于仅迁移部分数据或独立数据库的场景。在需要保持完整复制链路或确保GTID历史连续性的场合,一般不建议使用此参数。