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線程不受影響
總結
以上是生活随笔為你收集整理的GTID的常见错误和处理方法的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql 一个死锁的分析
- 下一篇： 大数据NoSQL技术之Couchbase