一、环境准备

双主架构即MySQL两台服务器互为主从，都可以进行读和写操作。双主架构就是在主从结构的基础上反过来在进行一次主从同步信息的配置和开启，同时有少量参数需要额外进行配置。

本次环境配置是基于前两歩搭建的基础，可以参考【MySQL高可用集群二】。

主机 /etc/hosts 配置：

#IP             域名|主机名
10.53.207.20    10.53.207.20
10.53.207.21    10.53.207.21

同时注意关闭ubuntu防火墙。

二、配置 /etc/mysql/my.cnf 文件：

1、节点 1（10.53.207.20），红色表示必须要开启的配置（节点1 和节点2 都需要开启）

[mysqld]
server-id = 20

# 开启gtid
gtid_mode = ON
enforce_gtid_consistency = 1

# 设置自增ID初始值为2，每次自增量为2。即都是偶数2,4,6,8,...
auto_increment_offset = 2
auto_increment_increment = 2

# 开启bin_log（默认是开启的，直接使用默认），模式为ROW，允许最大日志为1G
#log_bin = mysql-bin
#binlog_format = ROW
#max_binlog_size = 1024M

# 主节点master不需要写binlog的数据库
binlog_ignore_db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
binlog_ignore_db = sys
# 从节点slave不进行数据同步的数据库
replicate_ignore_db = mysql
replicate_ignore_db = information_schema
replicate_ignore_db = performance_schema
replicate_ignore_db = sys

# 中继日志（默认是开启的，直接使用默认）
#relay_log = mysql-relay-bin

# mysql5.7以后的版本不需要配置log-slave-updates=1
# log-slave-updates = 1

重启服务：service mysql restart

2、节点 2 （10.53.207.21），绿色表示与节点1 不同的配置

[mysqld]
server-id = 21

# 开启gtid
gtid_mode = ON
enforce_gtid_consistency = 1

# 设置自增ID初始值为1，每次自增量为2。即都是偶数1,3,5,7,...
auto_increment_offset = 1
auto_increment_increment = 2

# 开启bin_log（默认是开启的，直接使用默认），模式为ROW，允许最大日志为1G
#log_bin = mysql-bin
#binlog_format = ROW
#max_binlog_size = 1024M

# 主节点Master不需要写binlog的数据库
binlog_ignore_db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
binlog_ignore_db = sys
# 从节点slave不进行数据同步的数据库
replicate_ignore_db = mysql
replicate_ignore_db = information_schema
replicate_ignore_db = performance_schema
replicate_ignore_db = sys

# 中继日志（默认是开启的，直接使用默认）
#relay_log = mysql-relay-bin

# mysql5.7以后的版本不需要配置log-slave-updates=1
# log-slave-updates = 1

配置完成后，请务必重启数据库服务（service mysql restart），以免配置未生效造成异常。

三、创建数据同步账户

分别在两个节点创建数据同步账户，在MySQL命令行执行以下命令（个人建议账户名称不要相同，我这里为了便于操作创建相同的账户名称）：

use mysql;
create user 'repl'@'10.53.207.%' identified by 'P@repl';
grant replication slave on *.* to 'repl'@'10.53.207.%';
flush privileges;

然后退出MySQL命令行，在ubuntu命令行重启MySQL服务：service mysql restart

四、分别在每个节点配置主从同步信息，从而成为双主架构：

1、节点 1（10.53.207.20）

在MySQL命令行执行，配置主从信息：

change master to
    master_host='10.53.207.21',
    master_port=3306,
    master_user='repl',
    master_password='P@repl',
    master_auto_position=1,
    get_master_public_key=1;

启动主从同步：start slave；

查看状态：show replica status; 数据同步和回放全部正常。

2、节点 2 （10.53.207.21）

在MySQL命令行执行：

change master to
    master_host='10.53.207.20',
    master_port=3306,
    master_user='repl',
    master_password='P@repl',
    master_auto_position=1,
    get_master_public_key=1;

启动主从同步：start slave；

查看状态：show replica status; 数据同步正常，SQL回放失败。

异常排查：

查看异常日志位置：show variables like '%log_err%'; 确定异常日志为：/var/log/mysql/error.log

查看日志发现异常：Replica SQL for channel '': Worker 1 failed executing transaction '7dad9e38-31c7-11ee-85dc-5254004bdc94:1' at source log binlog.000013, end_log_pos 484; Error 'Operation CREATE USER failed for 'repl'@'10.53.207.%'' on query. Default database: ''. Query: 'CREATE USER 'repl'@'10.53.207.%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$ pNI{ERLhyWubqmgFy1RM9VoXgC8mVWhI72CETaYTd5BARm.fbTOYH2UP8l/'', Error_code: MY-001396

这个异常大概就是从库（节点2）同步主库（节点1）的时候将创建用户的语句同步过来了，但是由于在从库上已经有相同的用户，造成冲突，所以SQL回放报错了。

查看主库的相关信息：

已执行的GTID信息：7dad9e38-31c7-11ee-85dc-5254004bdc94:1-5，表示主库（节点1）已执行 5个GTID事务。

SELECT @@GLOBAL.GTID_EXECUTED;

主库报错的binlog信息查询，发现主库报错的GTID事务 7dad9e38-31c7-11ee-85dc-5254004bdc94:1 果然是创建用户的语句。

show binlog events in 'binlog.000013';

解决方式：

进入从库，在MySQL执行命令跳过异常GTID事务：

stop slave;
set GTID_NEXT='7dad9e38-31c7-11ee-85dc-5254004bdc94:1';
begin;commit;
set GTID_NEXT='AUTOMATIC';
start slave;

然后查看同步状态：show slave status；IO thread 和 SQL thread 都正常运行了。

至此，基于GTID方式复制的双主架构搭建完成！

注意：

binlog_ignore_db：用来配置 master 节点不需要产生binlog的数据库。

replicate_ignore_db：用来配置 slave 节点不进行数据同步的数据库。

　　这两个参数在夸库操作时会失效。例如，你在当前连接在主节点 master 的 sys 库中，然后执行创建用户命令（创建用户是创建到 mysql 库中），那么创建的用户会被同步到从节点 slave 的 mysql 库中。正确的操作是需要使用 “ use mysql; ” 命令，将当前数据库切换到 mysql 数据库下，这样再执行创建用户命令，数据将不会同步至从节点 slave 中。

　　要让两个参数生效，就必须在当前数据库下操作当前数据库内容，不能夸库，比如操作mysql 数据库下的用户信息，就需要使用 “ use mysql; ” 命令切换至 mysql 库下再进行操作。这样产生的更新就不会同步到从节点 slave 中了。

原创文章，引用请注明出处：https://www.cnblogs.com/hewei-blogs/articles/17632092.html

posted @ 2023-08-16 14:29 蓝迷梦阅读(810) 评论(0) 收藏举报

刷新页面返回顶部

蓝迷梦

【MySQL高可用集群三】MySQL双主搭建

公告

蓝迷梦

【MySQL高可用集群 三】MySQL双主搭建

公告

【MySQL高可用集群三】MySQL双主搭建