GTID的常见错误和处理方法

主从同步错误

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;

+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+

| File             | Position| Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                |

+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+

| mysql-bin.000004 |     8319|              |                  |09f5ef8b-8dd7-11e5-aa70-e8611f12a96a:1,

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就会跳过而不报错，自然就无效了

解决方法

首先保证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文件就正常了

关于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值，自动从这个点以后复制了

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

关于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.

When the server starts, the global value of 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 is re-read from the binary log that has now become the oldest one.

To update the value of this variable, gtid_mode must be ON, gtid_executed must be the empty string, and therefore 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. If the server is restarted before this file has been purged, then you should use binlog_gtid_simple_recovery=0 (the default in 5.6) to avoidgtid_purged or gtid_executed being computed incorrectly.

Issuing 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之前停止复制

在指定的gtid之后停止复制

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