亲宝软件园·资讯

展开

Mysql双主搭建的方法步骤

李有乾 人气:0

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,以此打破回环

加载全部内容

相关教程
猜你喜欢
用户评论