docker mysql 主主
环境 centos7
mysql版本 8.0.29
master1:192.168.59.102
master2:192.168.59.103
1、启动master1
#docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
2、编辑master1的配置文件
vi my.cnf
添加以下信息
【
#开启binlog日志
log-bin = mysql-bin
#基于混合模式
binlog_format = mixed
#ID号为1
server-id = 1
#启中继日志
relay-log = relay-bin
#中继日志的索引文件
relay-log-index = slave-relay-bin.index
#整个架构中的服务器台数
auto-increment-increment = 2
#用来设定数据库中自动增长的起点(即初始值）
auto-increment-offset = 1
#只同步test数据库(可选配置)
#binlog-do-db=test
】
3、配置master1的数据库配置
配置root的密码和navicat访问权限
>use mysql;
>UPDATE user SET HOST = '%' WHERE USER ='root';
失败，查看user表
>select host,user,plugin from user;
root的host已经是%，跳过
>ALTER user 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
>ALTER user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
>FLUSH PRIVILEGES;
创建用户slave并分配权限
mysql> create user 'slave'@'192.168.59.103' identified by '123456';
mysql>ALTER user 'slave'@'192.168.59.103' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
mysql>ALTER user 'slave'@'192.168.59.103' IDENTIFIED WITH mysql_native_password BY '123456';
#给slave1分配权限，所有数据库.所有表
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.59.103';
#查看slave1的权限
mysql>show grants for 'slave'@'192.168.59.103';
重启mysql容器
再登录数据库查看master1状态
mysql> show master status;
注意：mysql-bin.000001和157，slave配置用到
4、启动master2
#docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.29
5、编辑master2的配置文件
vi my.cnf
添加以下信息
【
#开启binlog日志
log-bin = mysql-bin
#基于混合模式
binlog_format = mixed
#ID号为2
server-id = 2
#启中继日志
relay-log = relay-bin
#中继日志的索引文件
relay-log-index = slave-relay-bin.index
#整个架构中的服务器台数
auto-increment-increment = 2
#用来设定数据库中自动增长的起点(即初始值）
auto-increment-offset = 2
】
6、配置master2的数据库配置
配置root的密码和navicat访问权限
>use mysql;
>UPDATE user SET HOST = '%' WHERE USER ='root';
失败，查看user表
>select host,user,plugin from user;
root的host已经是%，跳过
>ALTER user 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
>ALTER user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
>FLUSH PRIVILEGES;
创建用户slave并分配权限
mysql> create user 'slave'@'192.168.59.102' identified by '123456';
mysql>ALTER user 'slave'@'192.168.59.102' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
mysql>ALTER user 'slave'@'192.168.59.102' IDENTIFIED WITH mysql_native_password BY '123456';
#给slave1分配权限，所有数据库.所有表
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.59.102';
#查看slave1的权限
mysql>show grants for 'slave'@'192.168.59.102';
重启mysql容器
再登录数据库查看master2状态
mysql> show master status;
注意：mysql.000001和157，slave配置用到
7、配置master1的slave
登录master1的数据库，注意：mysql.000001和157是master2的（show master status;）信息
>CHANGE MASTER TO MASTER_HOST='192.168.59.103',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=157;
启动slave
>start slave;
查看slave状态
>show slave status\G;
8、配置master2的slave
登录master2的数据库，注意：mysql.000001和157是master1的（show master status;）信息
CHANGE MASTER TO MASTER_HOST='192.168.59.102',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=157;
启动slave
>start slave;
查看slave状态
>show slave status\G;
验证
在master1上创建数据库test1，创建表t1
刷新master2后，有同步到数据库和表
master2创建表t2，master1刷新后同步创建了t2
