GTID的常见错误和处理方法

男娘i 2022-07-13 14:11 816阅读 0赞

# *主从同步错误* #

[MySQL][]> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> set global sql\_slave\_skip\_counter=1;

ERROR 1858 (HY000): sql\_slave\_skip\_counter can not be set when theserver is running with @@GLOBAL.GTID\_MODE = ON. Instead, for each transactionthat you want to skip, generate an empty transaction with the same GTID as thetransaction

解决方法：

根据当前的在从库的状态，手工设置下一个GTID值，并写一个空的事务提交后，相当于使得从库上执行了这个有冲突的事务(跟sql\_slave\_skip\_counter一样，只是解决冲突，并不会修改不一致的数据)，然后再把GTID值设置回auto模式

mysql> show master status;

\+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+

\+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+

4e4592b2-8dd5-11e5-aa65-525400646024:1-33 |

\+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+

1 row in set (0.00 sec)

注意：找到4e4592b2-8dd5-11e5-aa65-525400646024:1-33这条记录，对应的当前从库的主库的uuid，将33改为34，不要找错

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SET SESSION GTID\_NEXT='4e4592b2-8dd5-11e5-aa65-525400646024:34';

Query OK, 0 rows affected (0.00 sec)

mysql> begin;commit;

Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION GTID\_NEXT =AUTOMATIC;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec

# *mysqlbinlog解析后导入无效* #

原因分析：普通方式导出成sql文件后，sql文件中设置了下一次的gtid值，但是这个gtid在之前已经执行过，那么mysql就会跳过而不报错，自然就无效了

![Center][]

解决方法

首先保证mysqlbinlog版本为3.4及以上，然后在mysqlbinlog中添加--skip-gtids=true参数，即

/opt/udb/program/mysql/mysql-5.6.20/bin/mysqlbinlog--skip-gtids=true mysql-bin.000005>/tmp/jj1.sql

这时我们再看导出的sql文件就正常了

![Center 1][]

# *关于mysqldump的选择和新建一个gtid的从库方法* #

选择：5.5的mysqldump默认不会到处gtid信息，而5.6版本的mysqldump默认会导出gitd信息，这里的gtid信息指的就是在dump时会记录以下这些信息

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='f79230ed-9970-11e5-b616-e8611f1041d0:1-4';

导出完成后再把sql\_log\_bin该回原来的值

SET @MYSQLDUMP\_TEMP\_LOG\_BIN = @@SESSION.SQL\_LOG\_BIN;  
SET @@SESSION.SQL\_LOG\_BIN= 0;

当导出gtid信息时会给出一个warining  
  
  
\[root@js-udb06 ~\]\# mysqldump--version

mysqldump  Ver 10.13 Distrib 5.5.35, for Linux (x86\_64)

\[root@js-udb06 ~\]\# mysqldump -h10.13.5.235-P3306 -uucloudbackup -pSMSXkkeUIu --all-databases>/tmp/jj.sql

\-- Warning: Skipping the data of tablemysql.event. Specify the --events option explicitly.

\[root@js-udb06 ~\]\#/opt/udb/program/mysql/mysql-5.6.20/bin/mysqldump  -h10.13.5.235 -P3306 -uucloudbackup-pSMSXkkeUIu --all-databases>/tmp/jj.sql

Warning: Using a password on the commandline interface can be insecure.

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

如果不想导出gtid信息，则在5.6版本的mysqldump时添加以下参数

Mysqldump时添加--set-gtid-purged=off参数

\[root@js-udb06 mysql-5.6\]\#/opt/udb/program/mysql/mysql-5.6.20/bin/mysqldump  -h10.13.5.235 -P3306 -uucloudbackup-pSMSXkkeUIu --set-gtid-purged=off --all-databases>/tmp/jj.sql

Warning: Using a password on the commandline interface can be insecure.

那么如果我已经有一个开启gtid的主库时，如果再创建一个gtid复制的从库呢？

原来很简单，先导入主库备份，记录当时的gtid的purged值，那么change master的时候会自动识别出这个purged值，自动从这个点以后复制了

![Center 2][]

报错很明显，gtid\_executed有内容，这时无法设置gtid\_purged，解决方法入下图，reset  master一下就好

![Center 3][]

关于gtid\_purged的解释，官网解释如下

he set of all transactions that have been purged from the binary log. This is a subset of the set of transactions in [`gtid_executed`][gtid_executed].

When the server starts, the global value of [`gtid_purged`][gtid_purged] is initialized to the set of GTIDs contained by the `Previous_gtid_log_event` of the oldest binary log. When a binary log is purged, [`gtid_purged`][gtid_purged] is re-read from the binary log that has now become the oldest one.

To update the value of this variable, [`gtid_mode`][gtid_mode] must be `ON`, [`gtid_executed`][gtid_executed] must be the empty string, and therefore [`gtid_purged`][gtid_purged] will also be the empty string. This can occur either when replication has not been started previously, or when replication was not previously using GTIDs.

After executing `SET gtid_purged`, you should note down the current binary log filename, which can be checked using [`SHOW MASTER STATUS`][SHOW MASTER STATUS]. If the server is restarted before this file has been purged, then you should use [`binlog_gtid_simple_recovery=0`][binlog_gtid_simple_recovery_0] (the default in 5.6) to avoid[`gtid_purged`][gtid_purged] or [`gtid_executed`][gtid_executed] being computed incorrectly.

Issuing [`RESET MASTER`][RESET MASTER] causes the value of this variable to be reset to an empty string.

## *设置成gtid的复制模式后，就无法改回传统的binlog+pos的模式了* ##

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> change master tomaster\_host='10.13.5.235',master\_user='ucloudbackup',master\_password='SMSXkkeUIu',master\_log\_pos=920,master\_log\_file='mysql-bin.000005';

ERROR 1776 (HY000): ParametersMASTER\_LOG\_FILE, MASTER\_LOG\_POS, RELAY\_LOG\_FILE and RELAY\_LOG\_POS cannot be setwhen MASTER\_AUTO\_POSITION is active.

mysql> set global gtid\_mode=OFF;

ERROR 1238 (HY000): Variable 'gtid\_mode'is a read only variable

# *start slave until用法* #

## *在指定的gtid之前停止复制* ##

![Center 4][]

## *在指定的gtid之后停止复制* ##

![Center 5][]

## *这两个操作完之后IO线程不受影响* ##

![Center 6][]

[MySQL]: http://lib.csdn.net/base/14
[Center]: /images/20220713/f05be249533b41b4964fee6ae1716f92.png
[Center 1]: /images/20220713/72f66faeb7c64bad864ceb78a61edc0c.png
[Center 2]: /images/20220713/4f3a41a25a9b42d58d98cf1eeaea735a.png
[Center 3]: /images/20220713/9f405a1aa4984f038743ac2c3911206c.png
[gtid_executed]: http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_gtid_executed
[gtid_purged]: http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_gtid_purged
[gtid_mode]: http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_gtid_mode
[SHOW MASTER STATUS]: http://dev.mysql.com/doc/refman/5.6/en/show-master-status.html
[binlog_gtid_simple_recovery_0]: http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_binlog_gtid_simple_recovery
[RESET MASTER]: http://dev.mysql.com/doc/refman/5.6/en/reset-master.html
[Center 4]: /images/20220713/1337620e25ea4abaa7964ca8a8a59f82.png
[Center 5]: /images/20220713/1d3b822254dc421ab41e8d565a68b850.png
[Center 6]: /images/20220713/11e378fc8d7141838e06bd2846038f2d.png