半同步复制

全同步复制

##半同步复制临时配置

#server51启用半同步主插件，启用主相关控制参数

[root@server51~]#mysql-hlocalhost-uroot-p'123qqq...A'#登录MySQL服务

mysql>INSTALLPLUGINrpl_semi_sync_masterSONAME"semisync_master.so";#安装主插件

QueryOK,0rowsaffected(0.01sec)

mysql>SELECT

->plugin_name,plugin_status

->FROM

->information_schema.plugins

->WHERE

->plugin_nameLIKE"%semi%";#确认插件安装

+----------------------+---------------+

|plugin_name|plugin_status|

+----------------------+---------------+

|rpl_semi_sync_master|ACTIVE|

+----------------------+---------------+

1rowinset(0.00sec)

mysql>SHOWVARIABLESLIKE"rpl_semi_sync%enabled";#查看半同步主控制参数(默认OFF)

+------------------------------+-------+

|Variable_name|Value|

+------------------------------+-------+

|rpl_semi_sync_master_enabled|OFF|

+------------------------------+-------+

1rowinset(0.00sec)

mysql>SETGLOBALrpl_semi_sync_master_enabled=1;#临时修改主控制参数为ON

QueryOK,0rowsaffected(0.00sec)

mysql>SHOWVARIABLESLIKE"rpl_semi_sync%enabled";#确认半同步主控制参数为ON

+------------------------------+-------+

|Variable_name|Value|

+------------------------------+-------+

|rpl_semi_sync_master_enabled|ON|

+------------------------------+-------+

1rowinset(0.00sec)

#server52启用半同步从插件，启用从相关控制参数

[root@server52~]#mysql-hlocalhost-uroot-p'123qqq...A'#登录MySQL服务

mysql>INSTALLPLUGINrpl_semi_sync_slaveSONAME"semisync_slave.so";#安装从插件

QueryOK,0rowsaffected(0.01sec)

mysql>SELECTplugin_name,plugin_status

->FROMinformation_schema.plugins

->WHEREplugin_nameLIKE"%semi%";#确认插件安装

+---------------------+---------------+

|plugin_name|plugin_status|

+---------------------+---------------+

|rpl_semi_sync_slave|ACTIVE|

+---------------------+---------------+

1rowinset(0.00sec)

mysql>SHOWVARIABLESLIKE"rpl_semi_sync%enabled";#查看半同步从控制参数(默认OFF)

+-----------------------------+-------+

|Variable_name|Value|

+-----------------------------+-------+

|rpl_semi_sync_slave_enabled|OFF|

+-----------------------------+-------+

1rowinset(0.00sec)

mysql>SETGLOBALrpl_semi_sync_slave_enabled=1;#临时修改从控参数为ON

QueryOK,0rowsaffected(0.00sec)

mysql>SHOWVARIABLESLIKE"rpl_semi_sync%enabled";#确认半同步主控制参数为ON

+-----------------------------+-------+

|Variable_name|Value|

+-----------------------------+-------+

|rpl_semi_sync_slave_enabled|ON|

+-----------------------------+-------+

1rowinset(0.00sec)

#测试半同步复制

[root@server52~]#mysql-hlocalhost-uroot-p'123qqq...A'

mysql>STOPSLAVEIO_THREAD;#停止主从IO线程

QueryOK,0rowsaffected(0.00sec)

mysql>STARTSLAVEIO_THREAD;#启动主从IO线程

QueryOK,0rowsaffected(0.00sec)

[root@server51~]#tail-4/var/log/mysqld.log#查看主服务器日志变化内容

2023-05-14T17:20:08.231852Z1295[Note]WhileinitializingdumpthreadforslavewithUUID<427d7d33-f0ff-11ed-afc4-000c29dc53a2>,foundazombiedumpthreadwiththesameUUID.Masteriskillingthezombiedumpthread(1291).

2023-05-14T17:20:08.231915Z1291[Note]Stopasynchronousbinlog_dumptoslave(server_id:52)

2023-05-14T17:20:08.232043Z1295[Note]Startbinlog_dumptomaster_thread_id(1295)slave_server(52),pos(db1.000003,28901736)

2023-05-14T17:20:08.232065Z1295[Note]Startsemi-syncbinlog_dumptoslave(server_id:52),pos(db1.000003,28901736)

[root@server51~]#

##半同步复制持久化配置

#server51配置

[root@server51~]#vim/etc/my.cnf#编辑主配置文件

[root@server51~]#sed-rn'4,10p'/etc/my.cnf

[mysqld]

log_bin=/mylog/db1

server_id=51

expire_logs_days=7

plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

rpl_semi_sync_master_enabled=1#将半同步主控参数设置为ON

rpl_semi_sync_slave_enabled=1#将半同步从控参数设置为ON

[root@server51~]#systemctlrestartmysqld#重启服务

[root@server51~]#mysql-hlocalhost-uroot-p'123qqq...A'\

>-e"SHOWVARIABLESLIKE'rpl_semi_sync%enabled'";#确认半同步主从控制参数为ON

+------------------------------+-------+

|Variable_name|Value|

+------------------------------+-------+

|rpl_semi_sync_master_enabled|ON|

|rpl_semi_sync_slave_enabled|ON|

+------------------------------+-------+

[root@server51~]#

#server52配置

[root@server52~]#vim/etc/my.cnf#编辑主配置文件

[root@server52~]#sed-rn'4,10p'/etc/my.cnf

[mysqld]

server_id=52

log_bin=server52

log_slave_updates

plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

rpl_semi_sync_master_enabled=1#将半同步主控参数设置为ON

rpl_semi_sync_slave_enabled=1#将半同步从控参数设置为ON

[root@server52~]#systemctlrestartmysqld#重启服务

[root@server52~]#mysql-hlocalhost-uroot-p'123qqq...A'\

>-e"SHOWVARIABLESLIKE'rpl_semi_sync%enabled'";#确认半同步主从控制参数为ON

+------------------------------+-------+

|Variable_name|Value|

+------------------------------+-------+

|rpl_semi_sync_master_enabled|ON|

|rpl_semi_sync_slave_enabled|ON|

+------------------------------+-------+