MySQL中如何进行基于GTID半同步搭建主从

2025-03-17 技术教程

MySQL中如何进行基于GTID半同步搭建主从，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

1. 背景

* GTID: 全局事物ID(Global Transaction ID)，在整个事务架构中每一个事务ID号是全局唯一的，不止是在一个节点上而是整个主从复制架构中每任何两个事务的ID号都不会相同。

* GTID就是由当前节点的UUID（一个128位的随机数）和为当前节点生成的自增数（TID）组成的。

* GTID在分布式架构中可以保证数据的一致性。从而也实现了mysql的高可用性。

* MySQL 5.6开始支持。 GTID在复制中代替原有的binlog file和file posistion复制定位。

2. 环境

* Master 服务器环境

* Slave 服务器环境

* Master my.cnf配置文件

[mysqld]########basicsettings#########主从server-id一定要设置不同server-id=110port=3306user=mysqlbind_address=0.0.0.0character_set_server=utf8mb4skip_name_resolve=1datadir=/data/mysql_datalog_error=error.log#######replicationsettings#########开启gtidgtid_mode=on#强制gtid的一致性enforce-gtid-consistency=truemaster_info_repository=TABLErelay_log_info_repository=TABLE#MySQL复制是基于binlog日志的log_bin=bin.logsync_binlog=1log_slave_updates#MySQLbinlog格式搭建主从时必须设置为rowbinlog_format=rowrelay_log=relay.logrelay_log_recovery=1slave_skip_errors=ddl_exist_errors######semisyncreplicationsettings#########设置插件目录路径plugin_dir=/usr/local/mysql/lib/plugin#加载插件plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"#开启mastersemisyncreplicationloose_rpl_semi_sync_master_enabled=1#开启slavesemisyncreplicationloose_rpl_semi_sync_slave_enabled=1#等待5秒无ack应答自动切换为异步模式loose_rpl_semi_sync_master_timeout=5000

* Slave my.cnf配置文件

[mysqld]########basicsettings########gtid_mode=onenforce-gtid-consistency=trueserver-id=210port=3306user=mysqlbind_address=0.0.0.0character_set_server=utf8mb4skip_name_resolve=1datadir=/data/mysql_datalog_error=error.log#######replicationsettings########master_info_repository=TABLErelay_log_info_repository=TABLElog_bin=bin.logsync_binlog=1log_slave_updatesbinlog_format=row#slave上开启只读，避免应用误写导致主从数据不一致read_only=onrelay_log=relay.logrelay_log_recovery=1binlog_gtid_simple_recovery=1slave_skip_errors=ddl_exist_errors######semisyncreplicationsettings########plugin_dir=/usr/local/mysql/lib/pluginplugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"loose_rpl_semi_sync_master_enabled=1loose_rpl_semi_sync_slave_enabled=1loose_rpl_semi_sync_master_timeout=5000rpl_semi_sync_master_wait_point=AFTER_SYNCrpl_semi_sync_master_wait_for_slave_count=1

3. 搭建基于GTID半同步复制主从

* master 创建复制所使用的用户 [ 此处ip设置为slave服务IP或者% ]

mysql>grantreplicationslaveon*.*to'rpl'@'172.18.4.1'identifiedby'123';QueryOK,0rowsaffected,1warning(0.00sec)

* slave服务器上配置连接master信息

未开启slave服务时，Slave_IO_Running与Slave_SQL_Running状态成No

mysql>showslavestatus;#未开启复制功能时，slave状态是空的Emptyset(0.00sec)mysql>changemastertomaster_host='172.18.0.1',master_user='rpl',master_password='123',master_auto_position=1;QueryOK,0rowsaffected,2warnings(0.02sec)mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:Master_Host:172.18.0.1Master_User:rplMaster_Port:3306Connect_Retry:60Master_Log_File:Read_Master_Log_Pos:4Relay_Log_File:relay.000001Relay_Log_Pos:4Relay_Master_Log_File:Slave_IO_Running:NoSlave_SQL_Running:NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:0Relay_Log_Space:154Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:0Master_UUID:Master_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Master_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:e5b2d96a-7047-11e7-b39c-00163e028c02:1Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)

* 开启slave服务，并查看状态

正常开启slave服务后，Slave_IO_Running与Slave_SQL_Running状态成Yes

mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:172.18.0.1Master_User:rplMaster_Port:3306Connect_Retry:60Master_Log_File:bin.000002Read_Master_Log_Pos:689Relay_Log_File:relay.000002Relay_Log_Pos:890Relay_Master_Log_File:bin.000002Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:689Relay_Log_Space:1087Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:110Master_UUID:aaa45482-7047-11e7-a7b3-00163e0432c5Master_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:aaa45482-7047-11e7-a7b3-00163e0432c5:1-2Executed_Gtid_Set:aaa45482-7047-11e7-a7b3-00163e0432c5:1-2,e5b2d96a-7047-11e7-b39c-00163e028c02:1Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)

* master 查看slave连接信息

mysql>showslavehosts;+-----------+------+------+-----------+--------------------------------------+|Server_id|Host|Port|Master_id|Slave_UUID|+-----------+------+------+-----------+--------------------------------------+|210||3306|110|e5b2d96a-7047-11e7-b39c-00163e028c02|+-----------+------+------+-----------+--------------------------------------+1rowinset(0.00sec)

* Master上操作创建数据库与表，并插入数据

mysql>createdatabasemytestcharactersetutf8mb4;QueryOK,1rowaffected(0.02sec)mysql>usemytest;Databasechangedmysql>createtablea(dataINTPRIMARYKEYNOTNULLAUTO_INCREMENT)ENGINE=INNODBDEFAULTCHARSET=utf8mb4;QueryOK,0rowsaffected(0.02sec)mysql>insertintoaselectnull;QueryOK,1rowaffected(0.01sec)Records:1Duplicates:0Warnings:0mysql>insertintoaselectnull;QueryOK,1rowaffected(0.01sec)Records:1Duplicates:0Warnings:0mysql>insertintoaselectnull;QueryOK,1rowaffected(0.00sec)Records:1Duplicates:0Warnings:0mysql>select*froma;+------+|data|+------+|1||2||3|+------+3rowsinset(0.00sec)

* slave上查看

mysql>select*frommytest.a;+------+|data|+------+|1||2||3|+------+3rowsinset(0.00sec)

4. slave误写，gtid错误解决

* master数据查看

mysql>select*frommytest.a;+------+|data|+------+|1||2||3|+------+3rowsinset(0.00sec)

* slave数据查看

mysql>select*frommytest.a;+------+|data|+------+|1||2||3|+------+3rowsinset(0.00sec)

* slave数据误写

mysql>insertintomytest.aselectnull;QueryOK,1rowaffected(0.00sec)Records:1Duplicates:0Warnings:0mysql>select*frommytest.a;+------+|data|+------+|1||2||3||4|+------+4rowsinset(0.00sec)

* master相同数据插入

mysql>insertintoaselectnull;QueryOK,1rowaffected(0.01sec)Records:1Duplicates:0Warnings:0mysql>select*froma;+------+|data|+------+|1||2||3||4|+------+4rowsinset(0.00sec)

* slave服务器状态查看报1062错误，SQL线程停止工作，由于开启gtid不能设置跳过错误

mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:172.18.0.1Master_User:rplMaster_Port:3306Connect_Retry:60Master_Log_File:bin.000002Read_Master_Log_Pos:5768Relay_Log_File:relay.000005Relay_Log_Pos:1354Relay_Master_Log_File:bin.000002Slave_IO_Running:YesSlave_SQL_Running:NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:1062Last_Error:CouldnotexecuteWrite_rowseventontablemytest.a;Duplicateentry'4'forkey'PRIMARY',Error_code:1062;handlererrorHA_ERR_FOUND_DUPP_KEY;theevent'smasterlogbin.000002,end_log_pos5737Skip_Counter:0Exec_Master_Log_Pos:5512Relay_Log_Space:2601Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:1062Last_SQL_Error:CouldnotexecuteWrite_rowseventontablemytest.a;Duplicateentry'4'forkey'PRIMARY',Error_code:1062;handlererrorHA_ERR_FOUND_DUPP_KEY;theevent'smasterlogbin.000002,end_log_pos5737Replicate_Ignore_Server_Ids:Master_Server_Id:110Master_UUID:aaa45482-7047-11e7-a7b3-00163e0432c5Master_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Master_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:17072417:15:51Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:aaa45482-7047-11e7-a7b3-00163e0432c5:10-23Executed_Gtid_Set:aaa45482-7047-11e7-a7b3-00163e0432c5:1-22,e5b2d96a-7047-11e7-b39c-00163e028c02:1-5Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)

* slave服务器停止复制

mysql>stopslave;QueryOK,0rowsaffected(0.00sec)

* 设置gtid下一次执行通过Retrieved_Gtid_Set和Executed_Gtid_Set得到未执行的gtid

mysql>setgtid_next='aaa45482-7047-11e7-a7b3-00163e0432c5:23';QueryOK,0rowsaffected(0.00sec)

* 执行空事物

mysql>begin;QueryOK,0rowsaffected(0.00sec)mysql>commit;QueryOK,0rowsaffected(0.00sec)

* 设置gtid下一次自动寻找gtid事物

mysql>setgtid_next='automatic';QueryOK,0rowsaffected(0.00sec)

* slave服务器开启复制

mysql>startslave;QueryOK,0rowsaffected(0.01sec)

* slave服务器查看状态 SQL线程正常开启工作

mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:172.18.0.1Master_User:rplMaster_Port:3306Connect_Retry:60Master_Log_File:bin.000002Read_Master_Log_Pos:5768Relay_Log_File:relay.000006Relay_Log_Pos:436Relay_Master_Log_File:bin.000002Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:5768Relay_Log_Space:2089Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:110Master_UUID:aaa45482-7047-11e7-a7b3-00163e0432c5Master_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:aaa45482-7047-11e7-a7b3-00163e0432c5:10-23Executed_Gtid_Set:aaa45482-7047-11e7-a7b3-00163e0432c5:1-23,e5b2d96a-7047-11e7-b39c-00163e028c02:1-5Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)

5. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。

看完上述内容，你们掌握MySQL中如何进行基于GTID半同步搭建主从的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！