一、什么是GTID？
GTID（Global Transaction ID）是一个已提交SQL事务的一编号，全局唯一，它由主库在执行SQL事务时产生，在Mysql复制环境中进行传递，不管多少层复制，GTID一直不变。GTID由UUID和TID组成。
GTID = UUID:TID
UUID， 即Server_uuid，是一个Mysql实例的唯一标识，在Mysql首次启动时，它会执行generate_server_uuid命令，产生一个UUID，并保存在auto.cnf文件中，每当Mysql重启时，都会读取auto.cnf文件，把UUID作为服务id。UUID有128位，由32个16进制字符组成，它在Mysql5.6之后，替代了32位的Server_id。
TID表示Mysql实例上已经提交的事务数量，是一个单向递增的序列号，从1开始，1-2代表第二个事务；第1-n代表n个事务。
mysql> show master status; +--------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+----------------------------------------+ | mysql88-bin.000008 | 464 | | | 075e555e-6e9d-11ef-86ac-000c29fc8990:1 | +--------------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql> insert into departments values('n005','li'); Query OK, 1 row affected (0.01 sec) mysql> insert into departments values('n006','chen'); Query OK, 1 row affected (0.00 sec) mysql> show master status; +--------------------+----------+--------------+--------------+-----------------------------------------+ | File | Position| Binlog_Do_DB| Binlog_Ignore_DB| Executed_Gtid_Set | +--------------------+----------+--------------+--------------+-----------------------------------------+ | mysql88-bin.000008| 1076 | | | 075e555e-6e9d-11ef-86ac-000c29fc8990:1-3| +--------------------+----------+--------------+--------------+-----------------------------------====--+ 1 row in set (0.00 sec)
二、GTID工作原理
-
当主库在执行一个SQL事务并提交时，系统会产生一个GTID，并把它一同记录到biglog日志中。
-
从库收到主库的binlog日志，并转存一relay log文件中。
-
从库sql线程从relay log文件中获取GTID，然后对比从库的binlog文件中，是否有记录。
-
如果有记录，说明这个GTID事务从库已执行过了，从库会忽略。
-
如果没有记录，从库就会从relay log中执行该GTID事务，并记录到binlog文件中，读取的同事会检查所有session会话中，有无其它session也在持有这个GTID，防止同时重复执行。
三、GTID应用好处
-
可以简化复制配置，减少DBA工作量。在配置GTID时，不需要再指定传统复制中的master_log_files
和master_log_pos。 -
提高数据一致性。GTID全局唯一，并且在复制节点上只执行一次，不漏事务，不重复执行事务。
-
增强系统可靠性。在一主多从中，切换主库时，我们可以根据GTID值，知道哪台slave是最接近主库数据状态，然后把最新数据的从库提升为主库，其它从库直接使用CHANGE MASTER TO MASTER_HOST=‘xxx’, MASTER_AUTO_POSITION命令就可以直接完成failover的工作。
四、现有传统主从转为GTID主从
4.1、修改数据库my.cnf配置
my.cnf文件中，增加2项配置
主从服务器，都要执行该步骤
enforce-gtid-consistency=true gtid-mode=ON ---my.cnf配置文件参考 alma92-88主库，more my.cnf [mysqld] server_id=88 log-bin=mysql88-bin binlog_format=row relay_log=relay88-bin lower_case_table_names=1 character-set-server = utf8mb4 explicit_defaults_for_timestamp = true sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION log_bin_trust_function_creators=1 datadir=/data/mysqldata/ socket=/data/mysqldata/mysql.sock log-error=/data/mysqldata/mysqld.log pid-file=/data/mysqldata/mysqld.pid #innodb_buffer_pool_size=8192m innodb_log_file_size=2048m innodb_io_capacity=15000 alma92-89从库，more /etc/my.cnf [mysqld] server_id=89 log-bin=mysql89-bin binlog_format=row relay_log=relay89-bin read_only=on skip_slave_start=on log-slave-updates=1 gtid-mode=on enforce-gtid-consistency=on master_info_repository=TABLE relay_log_info_repository=TABLE lower_case_table_names=1 character-set-server = utf8mb4 explicit_defaults_for_timestamp = true sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION log_bin_trust_function_creators=1 datadir=/data/mysqldata/ socket=/data/mysqldata/mysql.sock log-error=/data/mysqldata/mysqld.log pid-file=/data/mysqldata/mysqld.pid #innodb_buffer_pool_size=8192m innodb_log_file_size=2048m innodb_io_capacity=15000
4.2、停止主库业务写库
停止所有业务程序
查看主库的binlog日志是否有变化，”show master status“命令可以执行多次。
在主库操作
mysql> show master status; +--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | mysql88-bin.000008 | 157 | | | | +--------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
查看从库，是否完全应用了relay log日志。”show slave status\G“命令，检查 Seconds_Behind_Master参数是否这0,Slave_SQL_Running_State参数，是否已全部应用所有relay log日志。
在从库操作
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 172.25.250.88 Master_User: myrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql88-bin.000007 Read_Master_Log_Pos: 157 Relay_Log_File: relay89-bin.000014 Relay_Log_Pos: 377 Relay_Master_Log_File: mysql88-bin.000007 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: 157 Relay_Log_Space: 803 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: 88 Master_UUID: 075e555e-6e9d-11ef-86ac-000c29fc8990 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400
4.3、重启主库
# mysqladmin -uroot -p -S /data/mysqldata/mysql.sock shutdown # /usr/local/mysql-8.0.35/bin/mysqld_safe & # mysql -uroot -p -h 127.0.0.1 mysql> show variables like "%GTID%"; +----------------------------------+------------------------------------------+ | Variable_name | Value | +----------------------------------+------------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 075e555e-6e9d-11ef-86ac-000c29fc8990:1 | | gtid_executed_compression_period | 0 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+------------------------------------------+ 9 rows in set (0.00 sec)
4.4、重启从库
# mysqladmin -uroot -p -S /data/mysqldata/mysql.sock shutdown # /usr/local/mysql-8.0.35/bin/mysqld_safe & # mysql -uroot -p -h 127.0.0.1 mysql> show variables like "%GTID%"; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | | | gtid_executed_compression_period | 0 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 9 rows in set (0.01 sec)
4.5、从库转换为GTID同步
因为转换前用的是传统主从复制，故先用”reset slave all”，重置从库，清除从库的主从同步配置。
GTID主从同步，核心命令格式如下
CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_USER='replication_user_name', MASTER_PASSWORD='replication_password', MASTER_AUTO_POSITION = 1; 解说：-- MASTER_HOST，主库的IP地址 MASTER_USER，同步帐户 MASTER_PASSWORD，同步帐户所用的密码 MASTER_AUTO_POSITION = 1，表示slave应该自动定位到master的GTID
mysql> reset slave all; mysql> change master to master_host='172.25.250.88',master_user='repl',master_password='Zhongu@0909',master_auto_position=1; Query OK, 0 rows affected, 6 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 172.25.250.88 Master_User: myrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql88-bin.000008 Read_Master_Log_Pos: 464 Relay_Log_File: relay89-bin.000002 Relay_Log_Pos: 684 Relay_Master_Log_File: mysql88-bin.000008 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: 464 Relay_Log_Space: 890 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: 88 Master_UUID: 075e555e-6e9d-11ef-86ac-000c29fc8990 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: 075e555e-6e9d-11ef-86ac-000c29fc8990:1 Executed_Gtid_Set: 075e555e-6e9d-11ef-86ac-000c29fc8990:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec) ERROR: No query specified
4.6、验证
主库更新数据库，从库验证
主库更新数据操作，略。
从库操作，查看GTID事务，查看slave同步状态。
mysql> show master status; +--------------------+---------+-------------+-----------------+-----------------------------------------+ | File | Position| Binlog_Do_DB| Binlog_Ignore_DB| Executed_Gtid_Set | +--------------------+---------+-------------+-----------------+-----------------------------------------+ | mysql89-bin.000009 | 506| | | 075e555e-6e9d-11ef-86ac-000c29fc8990:1-3| +--------------------+---------+-------------+-----------------+-----------------------------------------+ 1 row in set (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 172.25.250.88 Master_User: myrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql88-bin.000008 Read_Master_Log_Pos: 769 Relay_Log_File: relay89-bin.000002 Relay_Log_Pos: 989 Relay_Master_Log_File: mysql88-bin.000008 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: 769 Relay_Log_Space: 1195 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: 88 Master_UUID: 075e555e-6e9d-11ef-86ac-000c29fc8990 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: 075e555e-6e9d-11ef-86ac-000c29fc8990:1-2 Executed_Gtid_Set: 075e555e-6e9d-11ef-86ac-000c29fc8990:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec) ERROR: No query specified
五、扩展：主从同步初始数据同步
主库备份：
mysqldump -u root -p --all-databases --single-transaction --flush-logs > all_databases_backup.sql 命令解说：--- -u root: 指定主库的用户名 -p： 后面留空，表示在执行mysqldump命令后，再输入密码，这样命令不会记录到命令的history。 --all-databases : 导出所有数据库。 --single-transaction : 确保数据一致性。 --flush-logs : 刷新日志。
备库恢复：
mysql -u root -p < all_databases_backup.sql -- 通过管道输入备份文件 如果是先登录到mysql里，再用source命令导入备份文件的话，最好是重启下mysql库，否则登录帐户密码，还是旧的，不是主库的帐户密码。
声明：欢迎大家光临本站，学习IT运维技术，转载本站内容，请注明内容出处”来源刘国华教育“。如若本站内容侵犯了原著者的合法权益，请联系我们进行处理。
