mysql8双主配置

MySQL 8.4 版本

不同版本配置不同！

server 1

server-id=1
log-bin=mysql-bin
binlog-format=ROW
max_binlog_size=1024M
slow_query_log=1
binlog_expire_logs_seconds =864000
# 异步落盘
sync-binlog=0
replica_skip_errors=all
# 自增ID偏移量设置
auto-increment-increment=2
auto-increment-offset=1
# 半同步复制
rpl_semi_sync_source_enabled=1
rpl_semi_sync_replica_enabled=1
# 开启GTID
gtid_mode=ON
enforce-gtid-consistency=ON

mysql_native_password=ON

server 2

server-id=2
log-bin=mysql-bin
binlog-format=ROW
max_binlog_size=1024M
slow_query_log=1
binlog_expire_logs_seconds =864000
# 异步落盘
sync-binlog=0
replica_skip_errors=all
# 自增ID偏移量设置
auto-increment-increment=2
auto-increment-offset=2
# 半同步复制
rpl_semi_sync_source_enabled=1
rpl_semi_sync_replica_enabled=1
# 开启GTID
gtid_mode=ON
enforce-gtid-consistency=ON

mysql_native_password=ON

启动半同步复制

-- 要求 MySQL 服务器支持动态加载
show variables like '%have_dynamic_loading%';

INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';

-- 确认安装是否成功：
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

-- 查看半同步监控 
SHOW STATUS LIKE 'Rpl_semi_sync%';

创建复制用户

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

https://dev.mysql.com/doc/refman/8.4/en/replication-howto-repuser.html

这个用户直接配置复制会报错。Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. 原因是用户默认密码加密方式是 caching_sha2_password，需要改成 mysql_native_password.

ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

通过GTID启动复制(2台MySQL分别执行)

-- 注意修改IP、端口等信息
CHANGE REPLICATION SOURCE TO SOURCE_HOST = '172.16.20.153',SOURCE_PORT = 3306,SOURCE_USER = 'repl',SOURCE_PASSWORD = 'password',SOURCE_AUTO_POSITION = 1;

启动

START REPLICA;

查看复制状态

SHOW REPLICA STATUS\G

https://dev.mysql.com/doc/refman/8.4/en/replication.html

双主模式下的读写分离

创建一个普通用户

CREATE USER 'test'@'%' IDENTIFIED BY 'Test@123456';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'test'@'%';
FLUSH PRIVILEGES;

超级用户可以在另一台MySQL动态设置只读
这里需要开发一个动态设置的读VIP所在MySQL只读的工具.

-- 普通用户只能读，超级用户依然可以写
set global read_only = 1;

-- 检查设置的状态
show global variables like '%read_only%';

额外说明：super_read_only和read_only不一样，super_read_only是所有用户都不能写。