MyCat企业级应用架构实战-双主双从高可用读写分离架构-第3章

mysql主从，双主配置

=================================================================

1 同步参数配置
master1和master2 双主同步需要增加配置，涉及到数据插入不冲突和同步配置

log_slave_updates = 1
#做为从数据库的时候，有写入操作也要更新二进制文件 默认为0，设置为1
设置完该参数后，数据库的架构就可以设置成master1和master2为主主同步，
slave1通过master1进行主从同步；
slave2通过master2进行主从同步；

#控制列中的值的增量值，也就是步长,取值范围 1...65535
auto-increment-increment = 2

# auto_increment_offset确定AUTO_INCREMENT列值的起点，也就是初始值，取值范围 1...65535
auto-increment-offset = 1

master1->slave1
===============
master1
# 主从配置
server-id=11 #服务器id (主从必须不一样)
log-bin=mysql-bin # 打开日志(主机需要打开)，这个mysql-bin 可自定义，也可加上路径
binlog-ignore-db=mysql #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-do-db=testdb #要给从机同步的库
expire_logs_days=14 # 自动清理 14 天前的log文件 可根据需要修改
binlog_format = row 
binlog_cache_size = 8M
max_binlog_size = 512M

#双主同步
auto-increment-offset = 1
auto-increment-increment = 2
log_slave_updates = 1 

slave1

server-id=12
relay_log = relay-log

master2->slave2
===============
master2
# 主从配置
server-id=13 #服务器id (主从必须不一样)
log-bin=mysql-bin # 打开日志(主机需要打开)，这个mysql-bin 可自定义，也可加上路径
binlog-ignore-db=mysql #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-do-db=testdb #要给从机同步的库
expire_logs_days=14 # 自动清理 14 天前的log文件 可根据需要修改
binlog_format = row 
binlog_cache_size = 8M
max_binlog_size = 512M

#双主同步
auto-increment-offset = 2
auto-increment-increment = 2
log_slave_updates = 1 

slave2

server-id=14
relay_log = relay-log

#重启让配置生效
docker restart 8a6522785694
docker restart 8dfbf9cd4163
docker restart 9feb154d0425
docker restart ee714241b100

2 同步用户和权限配置顺序

master1-> slave1
master2-> slave2
master1-> master2
master2-> master1
重启slave1和slave2

#配置master1->slave1
=====================
master1
show master status
CREATE USER 'slave'@'172.20.0.%' IDENTIFIED BY '123456';
grant replication slave on *.* to 'slave'@'172.20.0.%';
select user,host from mysql.user;
flush privileges;

slave1
stop slave
change master to master_host='172.20.0.11',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=154;
start slave
show slave status

#配置master2->slave2
=====================
master2
show master status
CREATE USER 'slave'@'172.20.0.%' IDENTIFIED BY '123456';
grant replication slave on *.* to 'slave'@'172.20.0.%';
select user,host from mysql.user;
flush privileges;

slave2
stop slave
change master to master_host='172.20.0.13',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=154;
start slave
show slave status

#配置master1->master2
=====================
master1

reset master
show master status

master2
show slave status
change master to master_host='172.20.0.11',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=154;
start slave
show slave status

#配置master2->master1
=====================
master2

reset master
show master status

master2
show slave status
change master to master_host='172.20.0.13',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=154;
start slave
show slave status

重启slave服务，设置master_log_pos

slave1

stop slave
change master to master_host='172.20.0.11',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=1728;
start slave
show slave status

slave2
stop slave
change master to master_host='172.20.0.13',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=1728;
start slave
show slave status

3 创建数据库测试

master1创建数据库testdb后，其他3个节点自动同步数据库，如图