时间：2021-07-01 10:21:17 帮助过：26人阅读
两台主机 MASTER 192.168.1.5 hostname : master.mingxiao.info
SLAVE 192.168.1.6 hostname : slave.mingxiao.info
GTID简介
GTID（Global Transaction Identifier)是每一个事务提交时创建的一个全局唯一的标示符。
GTID由UUID:TID组成。
MySQL的UUID是怎么获得的呢？当启动MySQL时，尝试读取datadir/auto.cnf中的UUID，若没找到，生成一个新的UUID，并保存至auto.cnf文件中。MySQL5.6版本，复制的时候，主从都直到对应的UUID，可通过SHOW SLAVE HOSTS看到。
TID（transaction_id)是已提交的事务数量，随事务提交而递增。
GTID作用
可以定位事务是在哪个实例上提交的
方便复制时故障转移
若Server A故障，需要将业务切换到Server B上。同时，我们又需要将Server C的复制源改成Server B，修改可使用CHANGE MASTER TO MASTER_HOST=‘XXX‘, MASTER_USER=‘XXX‘, MASTER_USER=‘XXX‘,MASTER_PASSWORD=‘XXX‘,MASTER_LOG_FILE=‘XXX‘,MASTER_LOG_POS=‘XXX‘即可.由于同一个事务在每台机器上所在的binlog名字和位置都不一样，很难找到Server C当前同步停止点对应的MASTER_LOG_FILE和MASTER_LOG_POS是什么。
但是出现GTID后，就变得非常简单，由于同一事务的GTID在所有节点都一致，则根据Server C的当前停止点的GTID就能唯一定位到Server B上的GTID。由于MASTER_AUTO_POSITION功能的出现，我们根本不需要GTID的具体值，直接使用CHANGE MASTER TO MASTER_HOST=‘XXX‘, MASTER_USER=‘XXX‘, MASTER_PASSWORD=‘XXX‘, MASTER_AUTO_POSITION=1;就可以完成故障转移的工作。
具体步骤
具体步骤
1、同步时间
2、两台主机分别初始化MySQL
3、修改配置文件
master上my.cnf
[mysqld] datadir=/mydata/data socket = /tmp/mysql.sock innodb-file-per-table = 1 server_id = 1 log-bin=master-bin gtid-mode=on binlog-format=ROW log-slave-updates=true enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 report-port=3306 port=3306 report-host=master.mingxiao.info sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
slave上my.cnf
[mysqld] log-bin=master-bin binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=11 report-port=3306 port=3306 log-bin=mysql-bin.log datadir=/mydata/data socket=/tmp/mysql.sock report-host=slave.mingxiao.info sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
master上
mysql > GRANT REPLICATION SLAVE ON *.* TO ‘repluser‘@‘192.168.1.6‘ IDENTIFIED BY ‘xiaoming‘; mysql > FLUSH PRIVILEGES;
slave上
mysql > GRANT MASTER TO MASTER_HOST=‘192.168.1.5‘, MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘xiaoming‘,MASTER_AUTO_POSITION=1; mysql > START SLAVE;
即可。
在master上查看从服务器信息。
mysql> SHOW SLAVE HOSTS; +-----------+---------------------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+---------------------+------+-----------+--------------------------------------+ | 11 | slave.mingxiao.info | 3306 | 1 | 20459c95-f484-11e4-82a4-000c29c05a8a | +-----------+---------------------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec)
在slave查看从服务器状态
mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.5 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000004 Read_Master_Log_Pos: 332 Relay_Log_File: slave-relay-bin.000007 Relay_Log_Pos: 451 Relay_Master_Log_File: master-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_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: 0 Exec_Master_Log_Pos: 332 Relay_Log_Space: 955 Until_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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: e168e12e-f5fc-11e4-8c3d-000c29b4e41b Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: e168e12e-f5fc-11e4-8c3d-000c29b4e41b:1-5 Executed_Gtid_Set: Auto_Position: 1 1 row in set (0.00 sec)
本文出自 “在路上” 博客，请务必保留此出处http://mingxiao.blog.51cto.com/8124243/1651379
MySQL-5.6.14基于GTID及多线程的复制
标签：gtid 复制