双主mysql搭建

两个数据库新建用户sys 密码sys 用户密码自己取
A库10.9.44.12:3306(docker网络172.17.0.2)
B库10.9.44.12:3307(docker网络172.17.0.3)
show variables like ‘%log_bin%’;
查看是否开启binlog

mysql配置文件

[mysql]

# 设置mysql客户端默认字符集

default-character-set=utf8

[mysqld]

# 设置3306端口

port = 3306
lower_case_table_names=1
server_id = 1
log-bin= mysql-bin
binlog_format = mixed

read-only=0
#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=1
auto-increment-increment=2

#主主复制的从库设置（新增）
#replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
log-slave-updates=on

[mysql]

# 设置mysql客户端默认字符集

default-character-set=utf8

[mysqld]

# 设置3306端口

port = 3307
lower_case_table_names=1
server_id = 2
log-bin= mysql-bin
binlog_format = mixed

read-only=0
#replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
log-slave-updates=on

#主主复制的主库设置（新增）
#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=2
auto-increment-increment=2

两个mysql创建用户
GRANT REPLICATION SLAVE ON . to ‘mysync’@’%’ identified by ‘mysync’;
A库执行（master_log_file、master_log_pos 需要从B库执行show master status;来查看填写）
stop slave;
flush logs;
change master to
master_host=‘172.17.0.3’,
master_user=‘mysync’,
master_password=‘mysync’,
master_port=3307,
master_log_file=‘mysql-bin.000008’,
master_log_pos=1325, master_connect_retry=30;
start slave;

B库执行（master_log_file、master_log_pos 需要从A库执行show master status;来查看填写）

stop slave;
change master to
master_host=‘172.17.0.2’,
master_user=‘mysync’,
master_password=‘mysync’,
master_port=3306,
master_log_file=‘mysql-bin.000011’,
master_log_pos=154;
start slave;

-----------------------------------------分割线----------------------
用到的语句 没有整理只做记录

GRANT REPLICATION SLAVE ON . to ‘mysync’@’%’ identified by ‘mysync’;
show master status;
stop slave;
flush logs;
change master to
master_host=‘172.17.0.3’,
master_user=‘mysync’,
master_password=‘mysync’,
master_port=3307,
master_log_file=‘mysql-bin.000008’,
master_log_pos=1325, master_connect_retry=30;
start slave;
slave reset

CHANGE MASTER TO MASTER_LOG_FILE=‘mysqld-bin.000010’,MASTER_LOG_POS=154;

CHANGE MASTER TO MASTER_HOST=‘172.17.0.3.145’, MASTER_USER=‘mysync’, MASTER_PASSWORD=‘mysync’, MASTER_LOG_FILE=‘master-bin.000008’, MASTER_LOG_POS=635;

START SLAVE;

show slave status;

show slave hosts;

RESET SLAVE

GRANT REPLICATION SLAVE ON . to ‘mysync’@’%’ identified by ‘mysync’;
show master status;
flush logs;
stop slave;
show grants for ‘mysync’@’%’

change master to
master_host=‘172.17.0.2’,
master_user=‘mysync’,
master_password=‘mysync’,
master_port=3306,
master_log_file=‘mysql-bin.000011’,
master_log_pos=154;
start slave;
show variables like ‘relay%’;
slave reset;
show slave hosts;
RESET SLAVE