MySQL5.7配置半同步复制实现双向 数据同步/主主复制 达到双机负载均衡的方案

 

一、说明

这里准备了两台机器。机器1：192.168.224.128，机器2：192.168.224.129

MySQL版本：5.7.30，这里不提供安装教程，理论上只要版本对，以下步骤能适用任何安装方式。

这里不提供原理的相关资料，有需要请自行查找资料。

二、目的

同步复制耗时长，异步复制存在数据不一致的风险，由于业务追求高效，又不能容忍数据风险，于是乎，只能上半同步复制。

三、完整支持UTF-8字符集的配置

在my.ini文件中，在合适的位置添加如下配置，这不是必要的配置，可按需选择。两台机器下做同样的配置。

[client]
#完整支持UTF-8字符集的配置
default-character-set=utf8mb4

[mysql]
#完整支持UTF-8字符集的配置
default-character-set=utf8mb4

[mysqld]
#完整支持UTF-8字符集的配置
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake

四、基本配置

配置半同步复制前，需要先配置好基本复制配置。

1、机器1的my.ini文件的基本配置

#主从基础配置
#配置bin log位置，会在MySQL的目录下保存，如下，请先创建bin-log文件夹，否则服务无法启动
log-bin=../bin-log/bin-log-128
#一般做成IP地址最后一段
server-id=128 

#配置需要复制的库
binlog-do-db=test_db
binlog-do-db=test

2、机器2的my.ini文件的基本配置

#主从基础配置
#配置bin log位置，会在MySQL的目录下保存，如下，请先创建bin-log文件夹，否则服务无法启动
log-bin=../bin-log/bin-log-129
#一般做成IP地址最后一段
server-id=129

#配置需要复制的库
binlog-do-db=test_db
binlog-do-db=test

3、重启MySQL服务

两台机器都要重启。如果没意外，可在bin-log文件夹下看到log文件。

五、Slave用户的创建

打开MySQL命令行界面，用root登录执行如下命令。机器1和机器2都执行。可替换自己需要的账号和密码。

CREATE USER 'test'@'192.168.%' IDENTIFIED BY '123456';#创建用户, 用户名：test，允许内部IP上登录
GRANT REPLICATION SLAVE ON *.* TO 'test'@'192.168.%';#分配权限
flush privileges;   #刷新缓存

六、主从双向关联

1、机器2做Slave，机器1做Master

在机器1上用root登录并执行如下脚本，获取Master相关信息。

show master status;

在机器2上用root登录并执行如下脚本，配置机器1为Master。

change master to master_host='192.168.224.128';
change master to master_user='test';
change master to master_password='123456';
change master to master_log_file='bin-log-128.000001'; #替换为上图对应的值
change master to master_log_pos=154; #替换为上图对应的值
start slave;

2、机器1做Slave，机器2做Master

在机器2上用root登录并执行如下脚本，获取Master相关信息。

show master status;

在机器1上用root登录并执行如下脚本，配置机器2为Master。

change master to master_host='192.168.224.129';
change master to master_user='test';
change master to master_password='123456';
change master to master_log_file='bin-log-129.000001'; #替换为上图对应的值
change master to master_log_pos=763; #替换为上图对应的值
start slave;

 3、验证配置是否成功

在机器1和机器2上执行如下命令，看打印出来的内容。

show slave status \G;

如果Slave_IO_Running和Slave_SQL_Running都为Yes，证明配置成功。

5、验证双向复制是否成功

在机器1和机器2上创建数据库，表，新增数据，验证数据是否能成功复制到另一台机器上。

七、半同步复制配置

1、插件安装

分别在机器1和机器2上执行如下脚本。

#安装半同步复制的插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.dll'; #Linux系统请将.dll改为.so
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.dll'; #Linux系统请将.dll改为.so

执行完后可以查询插件列表是否安装成功。

#查询插件
SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE '%semi%';

2、插件配置

分别在机器1和机器2的my.ini文件上追加如下配置。

#半同步复制的配置
plugin-load="rpl_semi_sync_master=semisync_master.dll;rpl_semi_sync_slave=semisync_slave.dll;" #MySQL启动时加载插件
rpl-semi-sync-master-enabled = 1 #启用Master半同步复制插件
rpl_semi_sync_master_timeout=5000 #超时时间：5秒
rpl-semi-sync-slave-enabled = 1 #启用Slave半同步复制插件

然后分别重启MySQL服务。

3、验证插件安装是否成功

重启完后可以查看插件配置：

#查询变量
show variables like '%semi%';

看到ON和AFTER_SYNC，表示达到目的。

查看插件状态：

#查询状态
show status like '%semi%';

亲切的ON。

八、业务应用

当两边同时更新或插入数据时，可能会存在冲突，所以在并发状态下，不要两边同时更新同一数据，特别是自增ID，会出现问题。最好是写数据只在一台服务器进行。当出现master宕机后，切换到备机。

参考文档：https://dev.mysql.com/doc/refman/5.7/en/replication-semisync-installation.html