二十一、Mysql之GTID
GTID（global transaction identifier）是一个在主数据库上对每个已经提交到数据库的事务的唯一编号，这个标识不仅在主数据库上是唯一的，而且在整个复制架构中的所有数据库中都是唯一的。
一个GTID由一对坐标表示，用冒号（：）分隔，代码如下所示：
GTID = source_id ：transaction_id
在上面的定义中，每一个GTID均代表一个数据库的事务，等号右边的source_id表示执行事务的源服务器主库的uuid（也就是server_uuid），而transaction_id是一个从1开始的自增的序列号，表示在这个主库上执行的第n个事务。只要保证每台数据库的server_uuid全局唯一，以及每台数据库生成的transaction_id自身唯一，就能保证GTID的全局唯一性。
GTID是事务在数据库中提交时创建分配的唯一标识符，所有事务均与GTID一一映射。
下面是一个GTID的具体形式： 2E11FA47-61CA-11E1-9E33-C70AA9429562:28
什么是sever_uuid？
从MySQL 5.6开始用128位的server_uuid代替了原本32位的server_id的大部分功能。原因很简单，server_id依赖于my.cnf的手工配置，有可能会产生冲突，而自动产生128位uuid的算法可以保证所有的MySQL uuid都不会发生冲突。 在进行首次启动时，MySQL会自动生成一个server_uuid，并且保存到数据库目录下的auto.cnf文件里，这个文件目前存在的唯一目的就是保存server_uuid。在MySQL再次启动时其会读取auto.cnf文件，继续使用上次生成的server_uuid。
二、GTID复制的工作原理简介
1）当主数据库进行数据更新时，会在事务前产生GTID号，一同记录到binlog日志中。
2）从数据库端的I/O线程将变更的binlog数据，写入到本地的中继日志（relay log）中。
3）从数据库端的SQL线程从中继日志中获取GTID号，然后对比本地的Binlog查看其是否有记录。如果有记录，则说明该GTID的事务已经执行，此时从数据库会忽略。
4）如果没有记录，则从数据库就会从中继日志中获取数据并执行该GTID的事务，并记录到binlog中。
master 更新数据时，会在事务前产生 GTID，一同记录到 binlog 日志中。 slave 端的 i/o 线程将变更的 binlog，写入到本地的 relay log 中,读取值是根据gitd_next变量，告诉我们slave下一个执行哪个GTID。 sql 线程从 relay log 中获取 GTID，然后对比 slave 端的 binlog 是否有记录。 如果有记录，说明该 GTID 的事务已经执行，slave 会忽略。 如果没有记录，slave 就会从 relay log 中执行该 GTID 的事务，并记录到 binlog。 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有二级索引就用全部扫描。
gtid-mode=on --启用gtid类型，否则就是普通的复制架构 enforce-gtid-consistency=true --强制GTID的一致性 log-slave-updates=1 --slave更新是否记入日志
1、实验环境
master 10.0.0.101 db01 slave1 10.0.0.102 db02 slavew 10.0.0.103 db03 OS System: CentOS Linux release 7.6.1810 (Core) mysql5.7 version: mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz mysql5.7二进制安装部署省略
省略
3、mysql的配置文件及启动脚本
msyql的配置文件
master:db01 cat >>/etc/my.cnf<<EOF [mysqld] basedir=/app/mysql/ datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=101 port=3306 secure-file-priv=/tmp autocommit=0 log_bin=/data/mysql/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [mysql] prompt=db01 [\d]> EOF slave1:db02 cat >>/etc/my.cnf<<EOF [mysqld] basedir=/app/mysql/ datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=102 port=3306 secure-file-priv=/tmp autocommit=0 log_bin=/data/mysql/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [mysql] prompt=db02 [\d]> EOF 从库db03 cat >>/etc/my.cnf<<EOF [mysqld] basedir=/app/mysql/ datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=103 port=3306 secure-file-priv=/tmp autocommit=0 log_bin=/data/mysql/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [mysql] prompt=db03 [\d]> EOF
cat >>/etc/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/app/mysql/bin/mysqld --defaults-file=/etc/my.cnf LimitNOFILE = 5000 EOF
mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data
systemctl start mysqld
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=auto >/tmp/full_db01.sql #恢复主库使用：mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/tmp/full_db01.sql #恢复从库使用：mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=auto >/tmp/full_db01.sql
slave1 db02 [root@db02 ~]#mysql -uroot -p password: db02 [none]>set sql_log_bin=0; db02 [none]>source /tmp/full_db01.sql; db02 [none]>set sql_log_bin=1; slave2 db03 [root@db03 ~]#mysql -uroot -p password: db03 [none]>set sql_log_bin=0; db03 [none]>source /tmp/full_db01.sql; db03 [none]>set sql_log_bin=1;
master: db01 [root@db01 ~]# mysql -uroot -p Enter password: db01 [(none)]>grant replication slave on *.* to repl@'10.0.0.%' identified by '123'; slave1:db02 slave2:sb03 change master to master_host='10.0.0.101', master_user='repl', master_password='123' , MASTER_AUTO_POSITION=1; start slave;
slave1 db02; slave2 db03
db02 [(none)]>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 484 Relay_Log_File: db02-relay-bin.000003 Relay_Log_Pos: 657 Relay_Master_Log_File: mysql-bin.000003 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: 484 Relay_Log_Space: 863 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: 101 Master_UUID: 90bd0c5d-3611-11eb-87b0-000c294bdbec Master_Info_File: /data/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 90bd0c5d-3611-11eb-87b0-000c294bdbec:2 Executed_Gtid_Set: 90bd0c5d-3611-11eb-87b0-000c294bdbec:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
测试前的主从数据信息
master db01 db01 [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.15 sec) slave1:db02 db02 [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.15 sec) slave3:db03 db03 [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.15 sec)
主库：db01 db01 [(none)]>create database ywx charset=utf8; Query OK, 1 row affected (0.00 sec) db01 [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | ywx | +--------------------+ 5 rows in set (0.00 sec) 从库：db02 db03 db02 [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | ywx | +--------------------+ 5 rows in set (0.02 sec) db03 [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | ywx | +--------------------+ 5 rows in set (0.02 sec) #主从复制正常
使用master db01, slave1 db02来做测试
1、在从库slave1 db02上创建一个数据库king
db02 [(none)]>create database king; Query OK, 1 row affected (0.00 sec) db02 [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | king | | mysql | | performance_schema | | sys | | ywx | +--------------------+ 6 rows in set (0.00 sec)
db01 [(none)]>create database king; Query OK, 1 row affected (0.00 sec) db01 [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | king | | mysql | | performance_schema | | sys | | ywx | +--------------------+ 6 rows in set (0.00 sec)
db02 [(none)]>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 812 Relay_Log_File: db02-relay-bin.000003 Relay_Log_Pos: 826 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1007 Last_Error: Error 'Can't create database 'king'; database exists' on query. Default database: 'king'. Query: 'create database king' Skip_Counter: 0 Exec_Master_Log_Pos: 653 Relay_Log_Space: 1191 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1007 Last_SQL_Error: Error 'Can't create database 'king'; database exists' on query. Default database: 'king'. Query: 'create database king' Replicate_Ignore_Server_Ids: Master_Server_Id: 101 Master_UUID: 90bd0c5d-3611-11eb-87b0-000c294bdbec Master_Info_File: /data/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 201204 18:23:06 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 90bd0c5d-3611-11eb-87b0-000c294bdbec:2-4 Executed_Gtid_Set: 89b505ff-3619-11eb-9c0e-000c29a73db1:1, 90bd0c5d-3611-11eb-87b0-000c294bdbec:1-3 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
Last_Errno: 1007 Last_Error: Error 'Can't create database 'king'; database exists' on query. Default database: 'king'. Query: 'create database king' Retrieved_Gtid_Set: 90bd0c5d-3611-11eb-87b0-000c294bdbec:2-4 #接受主库的2-4号语句 Executed_Gtid_Set: 89b505ff-3619-11eb-9c0e-000c29a73db1:1, 90bd0c5d-3611-11eb-87b0-000c294bdbec:1-3 #只运行了主库的第1-3号语句 #Master_UUID: 90bd0c5d-3611-11eb-87b0-000c294bdbec #Slave_UUID: 89b505ff-3619-11eb-9c0e-000c29a73db1
注入空事务，跳过主库的4号语句，不建议这样就以从库为标准了，后期会有问题 在问题从库slave2 db02 db02 [(none)]>stop slave; db02 [(none)]>set gtid_next='90bd0c5d-3611-11eb-87b0-000c294bdbec:4'; db02 [(none)]>begin;commit; db02 [(none)]>set gtid_next='AUTOMATIC'; db02 [(none)]>start slave;
db02 [(none)]>show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 812 Relay_Log_File: db02-relay-bin.000004 Relay_Log_Pos: 454 Relay_Master_Log_File: mysql-bin.000003 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: 812 Relay_Log_Space: 1491 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: 101 Master_UUID: 90bd0c5d-3611-11eb-87b0-000c294bdbec Master_Info_File: /data/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 90bd0c5d-3611-11eb-87b0-000c294bdbec:2-4 Executed_Gtid_Set: 89b505ff-3619-11eb-9c0e-000c29a73db1:1, 90bd0c5d-3611-11eb-87b0-000c294bdbec:1-4 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
#主从正常 Slave_IO_Running: Yes Slave_SQL_Running: Yes # 接受和执行信息有错误 Retrieved_Gtid_Set: 90bd0c5d-3611-11eb-87b0-000c294bdbec:2-4 Executed_Gtid_Set: 89b505ff-3619-11eb-9c0e-000c29a73db1:1, 90bd0c5d-3611-11eb-87b0-000c294bdbec:1-4 #主库出现问题，该从库成为主库后，会把从库以前执行的建库语句和从原主库上复制的建库语句一起发送个其它从库，造成语句重复。
建议重新建议主从关系，避免方案一中的问题。
六、GTID主从恢复数据注意事宜
1、在恢复主库或者非主从环境下，备份数据库--set-gtid-purged=OFF，原因为这样备份的文件中不会有SET @@GLOBAL.GTID_PURGED='025fd638-89ea-11e9-a749-40f2e9cf3aaa:10-13'值，恢复时可以重新记录binlog日志；否则，原数据库上binlog已有该@@GLOBAL.GTID_PURGED信息，恢复时会跳过该sql语句，恢复不成功。 2、在GTID主从环境下，备份数据库--set-gtid-purged=auto/on或者不添加，因为主从关系下，从库以主库为准，会读取relaylog中@@GLOBAL.GTID_PURGED来向主库获取新的信息。 3、在gtid主从情况下拿来进行恢复，是在master上恢复，还是slave上恢复。 如果是在master上进行恢复，那么就需要生成对应的gtid,所以需要使用set-gtid-purged=off 如果是在slave上进行恢复，那么不需要生成对应的gtid，所以需要使用set-gtid-purged=on
change master to 语句的区别 普通主从复制 CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=444, MASTER_CONNECT_RETRY=10; GTID主从复制 change master to master_host='10.0.0.51', master_user='repl', master_password='123' , MASTER_AUTO_POSITION=1; start slave; （1）在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便Failover （2）额外功能参数（3个） gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 （3）change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1; （4）在复制过程中，从库不再依赖master.info文件，而是直接读取最后一个relaylog的 GTID号 （5） mysqldump备份时，默认会将备份中包含的事务操作，以以下方式 SET @@GLOBAL.GTID_PURGED='90bd0c5d-3611-11eb-87b0-000c294bdbec:1'; 告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。
I have a dream so I study hard!!!
