點擊下載
本文文檔

當前位置：首頁 - 科技 - 知識百科 - 正文

MySQL5.6GTIDReplication

來源：懂視網(wǎng) 責編：小采時間：2020-11-09 20:02:45

MySQL5.6GTIDReplication

MySQL5.6GTIDReplication:一. MySQL 5.6引入了GTID的概念，那么GTID是何方神圣？其實也不復雜，就是一個全局事務標示符。使用GTID時，每次事務提交都會在binlog里生成1個唯一的標示符，它由UUID和事務ID組成。首次提交的事務ID為1，第二次為2，第三次為3，以此例推。uuid是服務器

推薦度：

點擊下載本文 文檔為doc格式

導讀MySQL5.6GTIDReplication:一. MySQL 5.6引入了GTID的概念，那么GTID是何方神圣？其實也不復雜，就是一個全局事務標示符。使用GTID時，每次事務提交都會在binlog里生成1個唯一的標示符，它由UUID和事務ID組成。首次提交的事務ID為1，第二次為2，第三次為3，以此例推。uuid是服務器

一. MySQL 5.6引入了GTID的概念，那么GTID是何方神圣？其實也不復雜，就是一個全局事務標示符。使用GTID時，每次事務提交都會在binlog里生成1個唯一的標示符，它由UUID和事務ID組成。首次提交的事務ID為1，第二次為2，第三次為3，以此例推。uuid是服務器的身份ID，在第一次啟動MySQL時，會自動生成一個server_uuid, 并且默認寫入到數(shù)據(jù)目錄下的auto.cnf文件里。我們一般無需修改，官方也不建議修改。更為詳細的可以參考MariaDB官方文檔

https://mariadb.com/kb/en/mariadb/mariadb-documentation/replication-cluster-multi-master/replication/parallel-replication/

[root@mysql-server-01 data]# pwd/root/sandboxes/msb_5_6_19/data[root@mysql-server-01 data]# cat auto.cnf [auto]server-uuid=9b0147c3-eed0-11e3-9821-000c29e3621e[root@mysql-server-01 data]#

那么基于GTID的Replication有啥好處？好處主要有以下2點：

（1）在傳統(tǒng)的復制里面，當發(fā)生故障，需要主從切換，需要找到binlog和pos點，然后change master to指向新的master，相對來說比較麻煩，也容易出錯。在MySQL 5.6里面，不用再找binlog和pos點，我們只需要知道m(xù)aster的ip，端口，以及賬號密碼就行，因為復制是自動的，MySQL會通過內(nèi)部機制GTID自動找點同步。

（2）多線程復制（基于庫）。在MySQL 5.6以前的版本，slave的復制是單線程的。一個事件一個事件的讀取應用。而master是并發(fā)寫入的，所以延時是避免不了的。唯一有效的方法是把多個庫放在多臺slave，這樣又有點浪費服務器。在MySQL 5.6里面，我們可以把多個表放在多個庫，這樣就可以使用多線程復制，當只有1個庫，多線程復制是沒有用的。

GTID相關特性默認是關閉的（難道官方還覺得不夠成熟），如下：

mysql [localhost] {msandbox} ((none)) > show variables like '%gtid%';+--------------------------+-----------+| Variable_name| Value |+--------------------------+-----------+| enforce_gtid_consistency | OFF || gtid_executed| || gtid_mode| OFF || gtid_next| AUTOMATIC || gtid_owned | || gtid_purged| |+--------------------------+-----------+6 rows in set (0.01 sec)mysql [localhost] {msandbox} ((none)) >

binlog里面也不會有GTID相關的記錄，和普通復制時是一樣的，如下：

[root@mysql-server-01 data]# mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS mysql_sandbox5619-bin.000006 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#140706 20:53:36 server id 1end_log_pos 120 Start: binlog v 4, server v 5.6.19-log created 140706 20:53:36 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;# at 120#140706 21:32:15 server id 1end_log_pos 207 Query thread_id=1 exec_time=0 error_code=0SET TIMESTAMP=1404653535/*!*/;SET @@session.pseudo_thread_id=1/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1073741824/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!/C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;drop database yayunz/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@mysql-server-01 data]#

View Code

當開啟GTID特性以后，binlog記錄格式是這樣的（具體開啟后面介紹）

注意：（當啟用GTID以后，binlog格式變化很大，如果繼續(xù)采用低版本的mysqlbinlog命令查看，將會得到如下錯誤）。

[root@mysql-server-01 data]# mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS mysql-bin.000004/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#140706 22:08:25 server id 1end_log_pos 120 Start: binlog v 4, server v 5.6.19-log created 140706 22:08:25ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 71, event_type: 35ERROR: Could not read entry at offset 120: Error in log format or read error.DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@mysql-server-01 data]#

View Code

下面是使用MySQL 5.6的mysqlbinlog命令查看開啟了GTID日志的記錄。

[root@mysql-server-01 data]# /data/sandbox_mysql/5.6.19/bin/mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS mysql-bin.000002 | tail -n 30DELIMITER /*!*/;# at 4#140706 21:58:35 server id 1end_log_pos 120 CRC32 0xcb49c4feStart: binlog v 4, server v 5.6.19-log created 140706 21:58:35# at 120#140706 21:58:35 server id 1end_log_pos 191 CRC32 0x0e40da64Previous-GTIDs# 9b0147c3-eed0-11e3-9821-000c29e3621e:1# at 191#140706 22:00:37 server id 1end_log_pos 239 CRC32 0x52fc16edGTID [commit=yes]SET @@SESSION.GTID_NEXT= '9b0147c3-eed0-11e3-9821-000c29e3621e:2'/*!*/;# at 239#140706 22:00:37 server id 1end_log_pos 330 CRC32 0xf652f593Query thread_id=3 exec_time=0 error_code=0SET TIMESTAMP=1404655237/*!*/;SET @@session.pseudo_thread_id=3/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1073741824/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!/C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;create database dyy/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;# at 330#140706 22:00:42 server id 1end_log_pos 377 CRC32 0x68ff3fc0Rotate to mysql-bin.000003pos: 4DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@mysql-server-01 data]#

View Code

我們從上面輸出可以清楚的看見事務id如下（根據(jù)上面提到的，這個是第二個事務）：

SET @@SESSION.GTID_NEXT= '9b0147c3-eed0-11e3-9821-000c29e3621e:2'/*!*/;

GTID的簡單工作流程如下：

（1）在master上產(chǎn)生一個事務并且提交，并且寫入binlog

（2）master上的binlog發(fā)送到slave，slave接收完畢并且寫入relay log，slave讀取到這個GTID，并設置gtid_next的值，例如：

SET @@SESSION.GTID_NEXT= '9b0147c3-eed0-11e3-9821-000c29e3621e:2

然后告訴slave接下來的事務必須使用GTID，并寫入到它自己的binlog里。（3）slave檢查并確認這個GTID沒有被使用，如果沒有被使用，那么開始執(zhí)行這個事務并寫入到它自己的binlog里。

（4）由于gtid_next的值不是空的，slave不會嘗試去生成一個新的gtid，而是通過主從復制來獲取GTID。

二. 基于GTID的Replication的配置如下（最少配置選項，master和slave一樣，server-id則需要不一樣了，下面的參數(shù)只是針對于GTID，對于復制過濾選項和普通復制一樣，因為GTID模式的復制也可以轉(zhuǎn)換為普通模式的復制）

binlog_format = rowgtid-mode = ONenforce-gtid-consistency = ONlog-bin=mysql-binlog-slave-updates

少了相關選項，啟動則報錯，這是其中的報錯日志，如下：

2014-07-06 22:47:44 15228 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates

其中binlog_format = row不是必須的，但是推薦使用ROW格式，具體的參考我前面的文章提到的原因。

上面參數(shù)主從都配置以后，下面我們就來完成一個基于GTID的Replication（和普通復制有少量區(qū)別）

（1）在master上授權(quán)賬戶；

mysql [localhost] {root} ((none)) > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.10' IDENTIFIED BY '123456';Query OK, 0 rows affected (0.00 sec)mysql [localhost] {root} ((none)) > flush privileges;Query OK, 0 rows affected (0.00 sec)mysql [localhost] {root} ((none)) >

（2）在master上備份要同步的庫（這里的備份參數(shù)大家自行查閱文檔，當然有幾個參數(shù)或許用不到）

[root@mysql-server-01 msb_5_6_19]# mysqldump -uroot -pmsandbox -S /tmp/mysql_sandbox5619.sock -q -R --triggers --opt --single-transaction --flush-logs --master-data=2 dyy > /tmp/dyy.sql[root@mysql-server-01 msb_5_6_19]#

（3）將備份的數(shù)據(jù)在slave上導入：

[root@mysql-server-02 ~]# mysql -uroot -pmsandbox -S /tmp/mysql_sandbox5619.sock < dyy.sql [root@mysql-server-02 ~]#

（4）進行change master to操作

mysql [localhost] {root} (dyy) > CHANGE MASTER TO MASTER_HOST='192.168.0.100',MASTER_PORT=5619,MASTER_USER='repl',MASTER_PASSWORD='123456',master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.03 sec)mysql [localhost] {root} (dyy) > show warnings;+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message|+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note| 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. || Note| 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql [localhost] {root} (dyy) >

可以看見成功，但是有兩個警告，原來在MySQL 5.6里面越來越嚴格了，說不推薦這種使用方法。

mysql [localhost] {root} (dyy) > start slave;Query OK, 0 rows affected (0.01 sec)mysql [localhost] {root} (dyy) > show slave status/G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.0.100Master_User: replMaster_Port: 5619Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 151 Relay_Log_File: mysql_sandbox5619-relay-bin.000002Relay_Log_Pos: 361Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:Last_Errno: 0 Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 151Relay_Log_Space: 577Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File:Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher:Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 9b0147c3-eed0-11e3-9821-000c29e3621e Master_Info_File: /root/sandboxes/msb_5_6_19/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: Executed_Gtid_Set: 9b0147c3-eed0-11e3-9821-000c29e3621e:1-13,d3c1aada-fad2-11e3-a66f-000c29ce3f18:1-85Auto_Position: 11 row in set (0.00 sec)mysql [localhost] {root} (dyy) >

如果使用GTID模式的復制而change master to還使用普通模式的，則會報錯。

三. GTID的局限性（或者說還不夠完善的地方）

（1）目前GTID還不支持MyISAM表（GTID是基于事務的）

（2）對CREATE TABLE ..... SELECT語句不支持。

mysql [localhost] {root} (dyy) > create table dyy select * from t1;ERROR 1786 (HY000): CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.mysql [localhost] {root} (dyy) >

（3）必須開啟enforce-gtid-consistency參數(shù)，否則啟動MySQL報錯，如下所示：

2014-07-06 23:44:27 17563 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 requires --enforce-gtid-consistency2014-07-06 23:44:27 17563 [ERROR] Aborting

注：在5.6.9的版本還不支持創(chuàng)建臨時表，我測試的是5.6.19的版本，已經(jīng)可以創(chuàng)建臨時表了。

四. 多線程復制（基于庫）

多線程復制由參數(shù)slave-paralles-workers控制，設定從服務器的SQL線程數(shù)；0表示關閉多線程復制功能；默認為0，最大可以設置為1024個線程。在從庫的配置文件[mysqld]段落添加配置：

slave_parallel_workers = 6

重啟MySQL以后使用show processlist看到如下所示：

mysql [localhost] {msandbox} ((none)) > show processlist;+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+| Id | User| Host| db | Command | Time | State | Info |+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+|1 | system user | | NULL | Connect |173 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL ||2 | system user | | NULL | Connect |173 | Waiting for master to send event| NULL ||3 | system user | | NULL | Connect |173 | Waiting for an event from Coordinator | NULL ||4 | system user | | NULL | Connect |173 | Waiting for an event from Coordinator | NULL ||5 | system user | | NULL | Connect |173 | Waiting for an event from Coordinator | NULL ||6 | system user | | NULL | Connect |173 | Waiting for an event from Coordinator | NULL ||7 | system user | | NULL | Connect |173 | Waiting for an event from Coordinator | NULL ||8 | system user | | NULL | Connect |173 | Waiting for an event from Coordinator | NULL || 10 | msandbox| localhost | NULL | Query |0 | init| show processlist |+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+9 rows in set (0.00 sec)mysql [localhost] {msandbox} ((none)) >

此外用于保證relog信息以及master信息不丟失，需要設置相關參數(shù)來保證，可以參考我前面的文章。

總結(jié)：

我們盡可能的把一個庫中的表按照業(yè)務邏輯拆分為多個庫，這樣在master上寫操作時，slave就可以根據(jù)我們設置的線程數(shù)進行多線程復制，減少了傳統(tǒng)復制的問題--延時。比如2個庫我們就可以開啟2個線程，3個庫就可以開啟3個線程，以此類推。還有目前GTID好像還不太成熟，官方的版本里面也沒有默認開啟，所以還需要進行大量的測試。

參考資料

http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html

http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com