从== MySQL 5.6.5 开始新增了==一种基于 GTID 的复制方式。
1）为什么借助gtid进行复制：
1、使用二进制复制，从库要告诉主库要从哪个偏移量开始同步，如果指定错误会造成数据的遗漏，最终数据不一致。 2、借助GTID，在发生主备切换的情况下，MySQL的其它从库可以自动在新主库上找到正确的复制位置。 3、基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险2）什么是GTID：
GTID (Global Transaction ID) 是对于一个已提交事务的编号，并且是一个全局唯一的编号。 **TID 实际上 是由UUID+TID 组成的。 其中 UUID 是一个 MySQL 实例的唯一标识。 TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。3）GTID的作用：
基于position的主从同步：首先从服务器上在一个特定的偏移量位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。
基于Gtid的主从同步：支持以全局统一事务ID (GTID)为基础的复制。
GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。 如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。4）gtid的工作原理
当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。如果有记录，说明该GTID的事务已经执行，slave会忽略。如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。实验准备：
这是使用之前的position方式的主服务器。1）确保当前的mysql服务已经开启：
[root@master ~]# netstat -antlup Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 978/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2050/master tcp 0 0 172.25.5.10:22 172.25.5.250:58562 ESTABLISHED 2138/sshd: root@pts tcp6 0 0 :::3306 :::* LISTEN 1391/mysqld tcp6 0 0 :::22 :::* LISTEN 978/sshd tcp6 0 0 ::1:25 :::* LISTEN 2050/master2）在mysql的配置文件中加入开启gtid的信息：
[root@master ~]# vim /etc/my.cnf # 文件的最后写入开启gitd服务的信息 gtid_mode=ON enforce-gtid-consistency=true3）进入mysql查看gtid执行表：执行表为空。
YES) [root@master ~]# mysql -uroot -pDaisy+147 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changedYES) [root@master ~]# mysql -uroot -pDaisy+147 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT * FROM gtid_executed; Empty set (0.01 sec)4）查看一个二进制日志，可以看出一个事件需要很多步：也可以看出一个binlog其实就是一个事务。
[root@master ~]# cd /var/lib/mysql [root@master mysql]# mysqlbinlog mysql-bin.000002 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200903 15:08:44 server id 1 end_log_pos 123 CRC32 0xb51dddd0 Start: binlog v 4, server v 5.7.24-log created 200903 15:08:44 at startup ROLLBACK/*!*/; BINLOG ' fJZQXw8BAAAAdwAAAHsAAAAAAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAB8llBfEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AdDdHbU= '/*!*/; # at 123 #200903 15:08:44 server id 1 end_log_pos 154 CRC32 0x0d516c8f Previous-GTIDs # [empty] # at 154 #200903 15:16:23 server id 1 end_log_pos 219 CRC32 0x6dff3c65 Anonymous_GTIDlast_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; [root@master mysql]# ls auto.cnf ibdata1 mysql-bin.000002 private_key.pem ca-key.pem ib_logfile0 mysql-bin.000003 public_key.pem ca.pem ib_logfile1 mysql-bin.index server-cert.pem client-cert.pem ibtmp1 mysql.sock server-key.pem client-key.pem mysql mysql.sock.lock song ib_buffer_pool mysql-bin.000001 performance_schema sys如果是基于position的主从复制：将一个事件拆开来复制，如果一个事件进行的过程中出现问题，那么复制也会出现问题 如果是基于gtid的主从复制：一个以事件为单位进行复制，如果一个事件进行的过程中出现问题，那么复制也不会出现问题
5）查看节点的UUID：
[root@master mysql]# ls auto.cnf ibdata1 mysql-bin.000002 private_key.pem ca-key.pem ib_logfile0 mysql-bin.000003 public_key.pem ca.pem ib_logfile1 mysql-bin.index server-cert.pem client-cert.pem ibtmp1 mysql.sock server-key.pem client-key.pem mysql mysql.sock.lock song ib_buffer_pool mysql-bin.000001 performance_schema sys [root@master mysql]# cat auto.cnf [auto] server-uuid=4b8887a6-edb4-11ea-9044-52540051eb0f6）重新启动mysql服务：
[root@master mysql]# systemctl restart mysqld [root@master mysql]#1）配置文件中写入开启gtid信息：
[root@slave ~]# vim /etc/my.cnf gtid_mode=ON enforce-gtid-consistency=true :wq2）重启mysql服务：
[root@slave ~]# systemctl restart mysqld [root@slave ~]#3）进入mysql数据库从新修改自己的主服务器链接方式：
[root@slave ~]# mysql -uroot -pDaisy+147 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.24 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> STOP SLAVE; # 首先停止从服务 Query OK, 0 rows affected (0.01 sec) mysql> CHANGE MASTER TO master_host='172.25.5.10', -> master_user='repl', -> master_password='Repl+147', -> master_auto_position=1;4）查看是否修改成功：
mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec) mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.25.5.10 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 154 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes # 开启 Slave_SQL_Running: Yes # 开启 ……………… Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 # position为主server-id=1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified1）主服务器新建一个数据库testgtid：
mysql> CREATE DATABASE testgtid; Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | song | | sys | | testgtid | +--------------------+ 6 rows in set (0.01 sec)2）从服务器查看gtid信息：
mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event # 等待发送事务 Master_Host: 172.25.5.10 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 325 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 538 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes …………………… Retrieved_Gtid_Set: 4b8887a6-edb4-11ea-9044-52540051eb0f:1 # master的uuid和id Executed_Gtid_Set: 4b8887a6-edb4-11ea-9044-52540051eb0f:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:3）查看主服务器的gtid信息：为空
mysql> USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT * FROM gtid_executed; Empty set (0.00 sec)4）查看从服务器的gtid事务：执行了主服务器的gtid。
mysql> USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT * FROM gtid_executed; +--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | +--------------------------------------+----------------+--------------+ | 4b8887a6-edb4-11ea-9044-52540051eb0f | 1 | 1 | +--------------------------------------+----------------+--------------+ 1 row in set (0.00 sec)以上就是完成基于gtid主从复制的整个过程。
