Mysql双主搭建的方法步骤

目录

• 1. Mysql binlog参数配置
• 2. Mysql binlog查看详细内容
• 3. Mysql双主搭建
• 4. Mysql双主解决数据回环
• 4.1 双主同步测试一
• 4.2 双主同步测试二
• 4.3 双主同步测试三

1. Mysql binlog参数配置

log-bin=mysql-bin

打开二进制日志功能，默认在datadir下

binlog-ignore-db

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog不记录上述表的修改

binlog_format

binlog_format=mixed

值有三个，ROW/STATEMENT/MIXED

expire_logs_days

自动删除binlog的天数，8.0已弃用，由binlog_expire_logs_seconds代替。MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项

binlog_expire_logs_seconds

二进制日志有效期，单位秒

binlog_expire_logs_seconds=259200  自动删除3天前的binlog

binlog_cache_size

binlog_cache_size=1M

默认值32KB。在一个事务中binlog为了记录SQL状态所持有的cache大小，如果经常使用大事务，可以增加此值来获取更大的性能。

max_binlog_size

max_binlog_size=500M

如果二进制日志写入的内容超出给定值，日志就会发生滚动

binlog_do_db

binlog-ignore-db=test

binlog只记录test数据库的修改

log_slave_updates

log_slave_updates=1

从库如果做为其他从库的主库，那么这个参数必须开启，设置为1为开启

因为直接往mysql写入的数据会写入binlog，但是后台IO线程从relaylog日志读取写入的数据在参数关闭的情况下不会写入binlog，如果该库还需要向其他slave同步，这个参数必须开启

2. Mysql binlog查看详细内容

show binary logs

查看binlog日志列表

show binlog events in 'mysql-bin.000003'

查看指定binlog文件

文件格式如下

3. Mysql双主搭建

版本8.0.25

my.cnf配置如下

[mysqld]
port=3308
server_id=123
default_authentication_plugin=mysql_native_password
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
symbolic-links=0
explicit_defaults_for_timestamp=true
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

binlog_cache_size=1M
binlog_format=mixed
binlog_expire_logs_seconds=259200
max_binlog_size=500M
slave_skip_errors=1062
log_slave_updates=1
lower_case_table_names=1
log-bin=mysql-bin
replicate-ignore-db=mysql,information_schema
sync_binlog=0
bind-address=0.0.0.0
skip-name-resolve
skip_ssl

auto_increment_offset=1
auto_increment_increment=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true

max_connections=1000
innodb-force-recovery=0

gtid-mode=on   #GTID开启
enforce-gtid-consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
innodb_flush_log_at_trx_commit=0
relay_log_recovery=1

[client]
port=3308
socket=/var/run/mysqld/mysqld.sock
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

两台mysql配置只有server_id不同，如果两台mysql部署在同一服务器，须再设置端口port不同，配置完成后启动两台mysql

1、登录两台mysql，都创建相应用来主从复制的用户

CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'ws-123456';
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'ws-123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'repl'@'%';
flush privileges;

2、登录第一个节点（3308节点）

show master status; -- 查看当前节点binlog日志信息，主要是获取当前binlog日志名和偏移量

执行下边的命令，将当前mysql设置为3309的从节点

change master to
master_host='lxy-mysql-one',
master_port=3309,
master_user='repl',
master_password='ws-123456',
master_log_file='mysql-bin.000004', -- 上边status命令查出来的日志名
master_log_pos=6534; -- 上边status命令查出来的日志偏移量

--如果是GTID模式，不用查偏移量使用下边进行主从关系设置
change master to
master_host='192.168.149.104',
master_port=3309,
master_user='repl',
master_password='ws-123456',
MASTER_AUTO_POSITION=1;   

启动主从复制

start slave;

检查主从复制状态，如果红圈都为YES则正常

show slave status ;

3、同理登录第二个节点（3309节点），同第2步操作，设置3309为3308的从节点，show slave status正常后即搭建完成

另：移除同步配置如下命令

stop slave; -- 停止主从复制

reset slave all; -- 移除主从设置的所有信息

4. Mysql双主解决数据回环

如何解决数据回环

主从复制模式下，是master写入binlog，slave接收master的binlog，写入relaylog，slave消费relaylog从而达成数据一致，slave只读不写，可以关闭binlog

而双主模式下，双主都可写，也就是都会产生binlog（需要开启binlog和log_slave_updates参数），那么master1插入数据，产生binlog，master2写入relaylog消费后又会写入binlog，推送至master1，如果没有特殊处理，那么这一条sql岂不是开始无限循环？

解决这样无限循环可以有以下几个方式

1、master2写入relaylog消费后不写binlog，那么循环即终止。也就是只有从客户端连接执行的sql写binlog，后台relaylog执行不写binlog。

使用log_slave_updates参数配置即可实现relaylog执行不写binlog，但是这样做如果master1和master2自身有从节点，即集群是双主双从架构，这样会造成master1和对应slave数据不同步

2、master2写入relaylog、写入binlog之后，不推送该条sql到master1。

根据binlog格式可以看出，binlog中记录了该条sql从哪个server来（server_id），这也是为什么双主server_id必须不同的原因，那就只推送server_id是当前节点id的binlog，但是这样也不适合双主双从架构

3、master2写入relaylog、写入binlog之后，推送该条sql到master1，master1端根据某个标识不执行重复sql。

猜测是根据serverid判断是否重复

4.1 双主同步测试一

1）3308节点初始状态

binlog偏移量为253

binlog日志初始为空

同步状态，已读取3309节点最新的binlog偏移量为253，当前节点relaylog偏移量为373

2）3309节点初始状态

binlog偏移量为253

binlog日志初始为空

同步状态，已读取3308节点最新的binlog偏移量为253，当前节点relaylog偏移量为373

3）在3308节点执行插入

4）观察3308节点变化

插入语句产生了binlog，偏移量更新到了573

产生了binlog

5）观察3309节点同步状态变化

已读取到3308节点binlog日志最新偏移量为573，3309的relaylog偏移量也有了更新

3309节点相应的也产生了binlog，更新了偏移量和日志文件，其中插入语句的server_id是123，是3308节点的id

6）观察3308节点同步状态变化

发现3308节点读到了3309节点最新的binlog偏移量，但是relaylog偏移量没有变动

4.1.1 测试总结

3308插入执行后产生了binlog，3309节点的slave状态显示binlog偏移量更新，即读取到了3308最新的binlog，relay日志偏移量变大，这是正常的主从同步完成，3309写入之后自身binlog偏移量更改，3308节点的slave状态显示binlog偏移量更新，即读取到了3309最新的binlog，但是relay日志偏移量不变。

这表示是在3308节点中IO线程读取到了3309节点这个重复的binlog，但是没有写入relaylog，根据binlog格式判断是根据Server_id进行过滤从而忽略了重复sql。

但是这个测试表现还不能确定是server_id起了主要作用，因为默认下GTID模式开启（gtid-mode=on，8.0.25），在binlog中也有gtid存在，也可能mysql是根据gtid进行的重复sql判断，虽然可能不大，但是还是要严谨看测试二

4.2 双主同步测试二

猜想：3308在接收到来自3309节点的重复binlog之后是根据serverId进行过滤，从而不写入relaylog，达到一个打破数据回环的效果

测试场景：首先关闭GTID模式，避免GTID对测试效果产生影响，其次在3308执行语句之后，假如修改3308节点的server_id，那么是不是就不能打破回环，接着会产生数据循环问题

1）两个节点都设置GTID模式关闭

SET @@GLOBAL.GTID_MODE=OFF 或者 my.cnf文件中设置gtid-mode=off
-- 查看参数确定已经关闭
SELECT @@GTID_MODE

2）将测试一中节点状态重置

stop slave;
reset slave all;
reset master;

3）重新搭建双主，略

show master status;

change master to
master_host='192.168.149.104',
master_port=3309,
master_user='repl',
master_password='ws-123456',
master_log_file='mysql-bin.000001', -- 上边status命令查出来的日志名
master_log_pos=156; -- 上边status命令查出来的日志偏移量

start slave;

-- 检查同步状态，测试插入是否同步等
show slave status;

4）在3308节点上执行如下sql，先停止3308节点从3309节点的同步，然后执行插入sql

stop slave;
insert into approve.table1 (id, name) values (999, '999');

3308节点binlog:

show binlog events in 'mysql-bin.000001';

 3309节点binlog:

可以看到这条sql已经写入3308节点binlog，同样也写入了3309节点的relaylog和binlog，但是目前3309到3308的同步是关闭的，3309的binlog还没推送到3308

5）修改3308节点的serverId，开启同步

set global server_id=888;
start slave;
show binlog events in 'mysql-bin.000001';

3308节点binlog:

3309节点binlog:

数据回环的效果已经出现，binlog在疯狂的增加，relaylog也来者不拒，不断地执行这条sql，表里都是重复数据

4.2.1 测试总结

解决数据回环主要就是IO线程在拉取到binlog之后根据server_id进行过滤，如果该binlog的serverId与自己相同，那么不计入relaylog，从而解决回环问题。

那GTID在其中又做了什么呢，开启GTID模式，重新走一遍测试二的流程

4.3 双主同步测试三

1）启动GTID模式

SET @@GLOBAL.GTID_MODE=ON 或者 my.cnf配置gtid-mode=on

2）两个节点重置测试二状态

stop slave;
reset slave all;
delete from approve.table1;
reset master;

-- 还需要把3308的serverId改回原来的id
set global server_id=123;

3）重新搭建双主，略，使用MASTER_AUTO_POSITION=1来使用GTID模式搭建主从

change master to
master_host='192.168.149.104',
master_port=3309,
master_user='repl',
master_password='ws-123456',
MASTER_AUTO_POSITION=1;

start slave;
show slave status;

4）在3308节点上执行如下sql，先停止3308节点从3309节点的同步，然后执行插入sql

stop slave;
insert into approve.table1 (id, name) values (999, '999');

show binlog events in 'mysql-bin.000001';

3308节点状态：

3308节点relaylog日志：

3309节点binlog状态：

5）修改3308节点的serverId，开启同步

set global server_id=888;
start slave;

show binlog events in 'mysql-bin.000001';

发现3308节点的binlog并没有任何变化，相应的3309节点更没有变化

6）再来观察3308节点的slave状态，relaylog日志进行了滚动，之前是00002，变成了00003文件

滚动是因为停止了同步（每次stop slave;start slave; relaylog都会进行滚动）

3308节点的relaylog状态如下，可以看到relaylog没有记录这条sql，但是有一条Rotate记录，也就是说GTID起了作用，使得IO线程跳过了这条sql不记录relaylog，再将relaylog的position更新为了最新的binlog位点

4.3.1 测试总结

在开启了GTID模式的情况下，在写入relaylog时除了根据serverId过滤，还会根据gtid进行过滤，已经执行过的gtid不再记录到relaylog，以此打破回环

到此这篇关于Mysql双主搭建的方法步骤的文章就介绍到这了,更多相关Mysql双主搭建内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！