Mysql主从复制（一从多主）_mysql主从复制多对一

–拉取镜像
docker pull mysql:5.7.30
–启动镜像
--启动master1主1

docker run -d -p 3397:3306 -v /home/mysql/master1/config:/etc/mysql/ -v/home/mysql/master1/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=root --name master1 mysql:5.7.30
1

–启动master2主2

docker run -d -p 3398:3306 -v /home/mysql/master2/config:/etc/mysql/ -v/home/mysql/master2/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=root --name master2 mysql:5.7.30
1

–启动slave1从1

docker run -d -p 3399:3306 -v /home/mysql/slave1/config:/etc/mysql/ -v/home/mysql/slave1/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=root --name slave1 mysql:5.7.30
1

–配置文件
master1

[mysqld]
#修改配置文件：vim /etc/my.cnf
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库 需要复制的主数据库名字
binlog-do-db=testdb
#设置logbin格式
binlog_format=STATEMENT

master-info-repository=TABLE
relay-log-info-repository=TABLE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

master2

[mysqld]
#修改配置文件：vim /etc/my.cnf
#主服务器唯一ID
server-id=2
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库 需要复制的主数据库名字
binlog-do-db=testdb
#设置logbin格式
binlog_format=STATEMENT

master-info-repository=TABLE
relay-log-info-repository=TABLE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

slave1

[mysqld]
#修改配置文件：vim /etc/my.cnf
#主服务器唯一ID
server-id=3
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库 需要复制的主数据库名字
binlog-do-db=testdb
#设置logbin格式
binlog_format=STATEMENT

master-info-repository=TABLE
relay-log-info-repository=TABLE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

重新启动mysql

docker restart master1 master2 master3
1

进入mysql

[root@mysql-master1 ~] mysql -uroot -proot
1

master1 节点操作

mysql> grant replication slave,replication client on *.* to 'master1'@'%' identified by '1'; 
1

mysql> flush privileges;  #刷新MySQL的系统权限相关表-
1

master2 节点操作

mysql> grant replication slave,replication client on *.* to 'master2'@'%' identified by '1'; 
1

mysql> flush privileges;  #刷新MySQL的系统权限相关表-
1

–多主一从配置
–查询容器的IP

docker inspect --format='{{.NetworkSettings.IPAddress}}' 容器名称 | 容器id
1

master1  172.17.0.2
master2  172.17.0.3
slave1 172.17.0.4
1
2
3

–进入到salve1中

--设置mysql-mastre1为自己的主节点
mysql> change master to		
master_host='172.17.0.2',  #master1自己的容器IP
master_user='master1',      #之前设置的用户名和密码
master_password='1',
master_log_file='mysql-bin.000019',   #在master1中使用show master status\G命令
master_log_pos=154 for channel 'zhu1';

--设置mysql-mastre2为自己的主节点
mysql> change master to		
master_host='172.17.0.3',
master_user='master2',
master_password='1',
master_log_file='mysql-bin.000021',   
master_log_pos=154 for channel 'zhu2';

--开启从服务
mysql> start slave for channel 'zhu1';
mysql> start slave for channel 'zhu2';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

之后利用navicat连接主从服务器即可达到，在master1和master2中分别增加几条数据。都会汇聚到从服务器slave1中。

PS：之前还遇到过一些错误问题。现在也po出来，以免再次遇到

如果出现错误：ERROR 3077 (HY000): To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.
请使用命令设置
mysql> SET GLOBAL master_info_repository = 'TABLE';

mysql> SET GLOBAL relay_log_info_repository = 'TABLE';
完毕！
1
2
3
4
5
6

如果出现错误：ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
请使用命令：use mysql
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;
source /usr/share/mysql/mysql_system_tables.sql
完毕！
1
2
3
4
5
6
7
8
9

如果出现错误：‘Could not find first log file name in binary log index file‘
--切换到主数据库
mysql> show master status;
mysql> flush logs;
mysql> show master status;
--切换到从数据库
mysql> CHANGE MASTER TO MASTER_LOG_FILE='最新获取到的文件名',MASTER_LOG_POS='最新获取到的位置';
mysql> start slave;
完毕！
1
2
3
4
5
6
7
8
9