什么是GTID？

　　GTID就是全局事務ID(global transaction identifier )，最初由google實現，官方MySQL在5.6才加入該功能。GTID實際上是由UUID+TID組成的。其中UUID是一個MySQL實例的唯一標識。TID代表了該實例上已經提交的事務數量，并且隨著事務提交單調遞增。

什么是多源復制？

　　多源復制意味著一個服務器能從多個從服務器上復制。這是MariaDB 10.0的一個新特性。

實驗系統：CentOS 6.6_x86_64

實驗前提：防火墻和selinux都關閉

實驗說明：本實驗共有3臺主機，ip分配如拓撲

實驗軟件：mariadb-10.0.20

實驗拓撲：

一、準備工作

　　1.修改三臺主機的名字，對應如下：

　　2.三臺主機配置相同的hosts文件為如下內容：

　　3.安裝mariadb：

tar xf mariadb-10.0.20-linux-x86_64.tar.gz  -C /usr/local/cd /usr/local/ln -sv mariadb-10.0.20-linux-x86_64 mysqlmkdir -pv /mydata/datauseradd -r mysqlchown -R mysql.mysql /mydata/data/cd mysql/chown -R root.mysql .scripts/mysql_install_db --user=mysql --datadir=/mydata/data/cp support-files/my-large.cnf /etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqldchkconfig --add mysqldchkconfig mysqld on

　　4.修改配置文件：

vim /etc/my.cnf--------------------------------->[mysqld]datadir = /mydata/data

　　5.創建虛擬機鏡像（可選）：

　　　　為了試驗不受干擾，做完GTID試驗我會將三臺主機恢復至初始狀態。

二、GTID復制

　　1.將mysql1配置為master：

[mysqld]server-id       = 1binlog-format=ROWlog-bin=/mydata/data/master-binlog-slave-updates=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-threads=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-host=mysql1

　　2.將mysql2配置為slave：

[mysqld]server-id       = 2binlog-format=ROWlog-bin=/mydata/data/mysql-binlog-slave-updates=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-threads=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-host=mysql2

　　3.將mysql3配置為slave：

[mysqld]server-id       = 3binlog-format=ROWlog-bin=/mydata/data/mysql-binlog-slave-updates=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-threads=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-host=mysql3

　　4.在master節點創建復制用戶：

service mysqld start/usr/local/mysql/bin/mysql-------------------------------------------->GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'jason'@'192.168.19.%' IDENTIFIED BY '123456';FLUSH PRIVILEGES;

　　5.在兩臺slave節點同時操作：

service mysqld start/usr/local/mysql/bin/mysql------------------------------------------->CHANGE MASTER TO MASTER_HOST='mysql1',MASTER_USER='jason',MASTER_PASSWord='123456',MASTER_USE_GTID=slave_pos;START SLAVE;

　　6.在slave節點查看狀態：

SHOW SLAVE STATUS/G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: mysql1                  Master_User: jason                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master-bin.000007          Read_Master_Log_Pos: 321               Relay_Log_File: mysql2-relay-bin.000002                Relay_Log_Pos: 613        Relay_Master_Log_File: master-bin.000007             Slave_IO_Running: Yes            Slave_SQL_Running: Yes                          ...                   Using_Gtid: Slave_Pos

SHOW GLOBAL VARIABLES LIKE '%gtid%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| gtid_binlog_pos        | 0-1-3 || gtid_binlog_state      | 0-1-3 || gtid_current_pos       | 0-1-3 || gtid_domain_id         | 0     || gtid_ignore_duplicates | OFF   || gtid_slave_pos         | 0-1-3 || gtid_strict_mode       | OFF   |+------------------------+-------+

　　7.測試，在master節點創建數據庫，并在其他兩個節點進行查看：

CREATE DATABASE jjj;           //主節點創建數據庫SHOW DATABASES;                //從節點查看

　　　　　可以看到兩臺從服務器都可以正常復制主服務器的數據了，試驗成功。下面進行多源復制的實驗，我將所有主機恢復虛擬快照至原始狀態。

三、多源復制

　　1.我將mysql1和mysql2配置為master主機，mysql3配置為slave主機，編輯配置文件：

　　　　mysql1：

[mysqld]server-id       = 1log-bin=/mydata/data/mysql-bin

　　　　mysql2：

[mysqld]server-id       = 2log-bin=/mydata/data/mysql-bin

　　　　mysql3：

[mysqld]#log-bin=mysql-bin#binlog_format=mixedserver-id       = 3relay_log=/mydata/data/relay-log

　　2.mysql1和mysql2創建復制用戶：

service mysqld start/usr/local/mysql/bin/mysql-------------------------------------------->GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'192.168.19.66' IDENTIFIED BY '123456';FLUSH PRIVILEGES;

　　2.查看兩臺主機的二進制日志位置，這里兩臺位置恰好一致：

SHOW MASTER LOGS;

　　3.mysql3上配置CHANGE MASTER：

service mysqld start/usr/local/mysql/bin/mysql----------------------------------------------->CHANGE MASTER 'm1' TO MASTER_HOST='mysql1',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=647;CHANGE MASTER 'm2' TO MASTER_HOST='mysql2',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=647;START ALL SLAVES;

SHOW ALL SLAVES STATUS/G*************************** 1. row ***************************              Connection_name: m1              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it               Slave_IO_State: Waiting for master to send event                  Master_Host: mysql1                  Master_User: slave                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000004          Read_Master_Log_Pos: 647               Relay_Log_File: relay-log-m1.000002                Relay_Log_Pos: 535        Relay_Master_Log_File: mysql-bin.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes　　　　　　　　　　　　　　　...*************************** 2. row ***************************              Connection_name: m2              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it               Slave_IO_State: Waiting for master to send event                  Master_Host: mysql2                  Master_User: slave                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000004          Read_Master_Log_Pos: 647               Relay_Log_File: relay-log-m2.000002                Relay_Log_Pos: 535        Relay_Master_Log_File: mysql-bin.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes　　　　　　　　　　　　　　　...

　　4.測試：

　　　　mysql1：

CREATE DATABASE mydb1;CREATE TABLE mydb1.jjj (id int);

　　　　mysql2：

CREATE DATABASE mydb2;CREATE TABLE mydb2.jjj (id int);

　　　　mysql3：

SHOW DATABASES;SHOW TABLES FROM mydb1;SHOW TABLES FROM mydb2;

　　至此，多源復制也演示完畢，謝謝！如有問題，請聯系我，QQ：82800452