云主机云服务器

复制模式

半同步复制

全同步复制

搭建半同步复制

##半同步复制临时配置

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

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

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME"semisync_master.so"; #安装主插件

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT

-> plugin_name,plugin_status

-> FROM

-> information_schema.plugins

-> WHERE

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

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

| plugin_name | plugin_status |

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

| rpl_semi_sync_master | ACTIVE |

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

1 row in set (0.00 sec)

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

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

| Variable_name | Value |

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

| rpl_semi_sync_master_enabled | OFF |

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

1 row in set (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

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

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

| Variable_name | Value |

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

| rpl_semi_sync_master_enabled | ON |

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

1 row in set (0.00 sec)

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

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

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME"semisync_slave.so"; #安装从插件

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT plugin_name,plugin_status

-> FROM information_schema.plugins

-> WHERE plugin_name LIKE "%semi%"; #确认插件安装

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

| plugin_name | plugin_status |

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

| rpl_semi_sync_slave | ACTIVE |

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

1 row in set (0.00 sec)

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

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

| Variable_name | Value |

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

| rpl_semi_sync_slave_enabled | OFF |

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

1 row in set (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

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

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

| Variable_name | Value |

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

| rpl_semi_sync_slave_enabled | ON |

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

1 row in set (0.00 sec)

#测试半同步复制

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

mysql> STOP SLAVE IO_THREAD; #停止主从IO线程

Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE IO_THREAD; #启动主从IO线程

Query OK, 0 rows affected (0.00 sec)

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

2023-05-14T17:20:08.231852Z 1295 [Note] While initializing dumpthread for slave with UUID <427d7d33-f0ff-11ed-afc4-000c29dc53a2>,found a zombie dump thread with the same UUID. Master is killing thezombie dump thread(1291).

2023-05-14T17:20:08.231915Z 1291 [Note] Stop asynchronous binlog_dumpto slave (server_id: 52)

2023-05-14T17:20:08.232043Z 1295 [Note] Start binlog_dump tomaster_thread_id(1295) slave_server(52), pos(db1.000003, 28901736)

2023-05-14T17:20:08.232065Z 1295 [Note] Start semi-sync binlog_dumpto slave (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 ~]# systemctl restart mysqld #重启服务

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

> -e "SHOW VARIABLES LIKE '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 ~]# systemctl restart mysqld #重启服务

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

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

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

| Variable_name | Value |

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

| rpl_semi_sync_master_enabled | ON |

| rpl_semi_sync_slave_enabled | ON |

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