MySQL多源主从复制-双主双从

一、实验环境准备

1.1准备四台主机服务器

操作系统	主机地址	主机名
CentOS 7.4	192.168.11.111	mysql-master1
CentOS 7.4	192.168.11.112	mysql-master2
CentOS 7.4	192.168.11.113	mysql-slave1
CentOS 7.4	192.168.11.114	mysql-slave2

1.2配置host解析

# vi /etc/hosts

192.168.11.111 mysql-master1
192.168.11.112 mysql-master2
192.168.11.113 mysql-slave1
192.168.11.114 mysql-slave2

1.3安装MySQL5.7版本

4台机器均安装，这里采用yum安装

# wget https://dev.mysql.com/get/mysql80-community-release-el7-4.noarch.rpm
# rpm -ivh mysql80-community-release-el7-4.noarch.rpm
# vi /etc/yum.repos.d/mysql-community.repo   //配置yum源，下载mysql5.7 

​

 # yum -y install mysql-community-server
[root@mysq-master1 ~]# mysql --version   #一定要确定4台机器下载的Mysql版本一致
mysql  Ver 14.14 Distrib 5.7.36, for Linux (x86_64) using  EditLine wrapper

1.4MySQL服务配置文件修改

修改4台mysql的配置文件，打开/etc/my.cnf，在[mysqld]标签下添加内容：

server-id=1
log-bin=mylog
master-info-repository=TABLE
relay-log-info-repository=TABLE

主服务器1配置如下： 

 主服务器2配置如下： 

从服务器1配置如下： 

从服务器2配置如下： 

1.5启动MySQL服务和创建用户 

mysql-master1节点操作

[root@mysq-master1 ~]# systemctl start mysqld
[root@mysq-master1 ~]# grep password /var/log/mysqld.log

[root@mysq-master1 ~]# mysqladmin -uroot -p'WdJ_H?xVk8-k' password 'Youngfit@123456'
[root@mysq-master1 ~]# mysql -uroot -p'Youngfit@123456'
mysql> grant replication slave,replication client on *.* to 'user001'@'%' identified by 'User@001'; 

mysql> flush privileges;

 mysql-master2操作如同mysql-master1一样步骤

二、双主配置 

2.1双主配置1

查看mysql-master2的binlog日志名称和位置：

在mysql-master1上指定操作：

mysql> change master to
master_host='mysql-master2',
master_user='user001',
master_password='User@001',
master_log_file='mylog.000002',
master_log_pos=872 for channel 'mysql-master2';

mysql> start slave;
mysql> show slave status\G

2.2双主配置2

查看mysql-master1的binlog日志名称和位置：

在mysql-master2上指定操作：

mysql> change master to
master_host='mysql-master1',
master_user='user001',
master_password='User@001',
master_log_file='mylog.000002',
master_log_pos=872 for channel 'mysql-master1';

mysql> start slave;
mysql> show slave status\G

2.3双主配置验证

2台分别查看同步的io线程和sql线程是否为YES

mysql> show slave status\G

三、双从配置 

3.1双从配置1

mysql-slave1

[root@mysql-slave1 ~]# systemctl start mysqld
[root@mysql-slave1 ~]# grep password /var/log/mysqld.log 
[root@mysql-slave1 ~]# mysqladmin -uroot -p'oUm?lgeoA8yY' password 'Cloud@2404'
[root@mysql-slave1 ~]# mysql -uroot -p'Cloud@2404'

--设置mysql-mastre1为自己的主节点
mysql> change master to
master_host='mysql-master1',
master_user='user001',
master_password='User@001',
master_log_file='mylog.000002',
master_log_pos=872 for channel 'mysql-master1';

--设置mysql-mastre2为自己的主节点 
mysql> change master to
master_host='mysql-master2',
master_user='user001',
master_password='User@001',
master_log_file='mylog.000002',
master_log_pos=872 for channel 'mysql-master2';


mysql> start slave for channel 'mysql-master1';
mysql> start slave for channel 'mysql-master2';

mysql> show slave status\G

3.2双从配置2

mysql-slave2同样操作

[root@mysql-slave2 ~]# systemctl start mysqld
[root@mysql-slave2 ~]# grep password /var/log/mysqld.log 
[root@mysql-slave2 ~]# mysqladmin -uroot -p'oUm?lgeoA8yY' password 'Cloud@2404'
[root@mysql-slave2 ~]# mysql-uroot -p'Cloud@2404'

--设置mysql-mastre1为自己的主节点
mysql> change master to
master_host='mysql-master1',
master_user='user001',
master_password='User@001',
master_log_file='mylog.000002',
master_log_pos=872 for channel 'mysql-master1';

--设置mysql-mastre2为自己的主节点 
mysql> change master to
master_host='mysql-master2',
master_user='user001',
master_password='User@001',
master_log_file='mylog.000002',
master_log_pos=872 for channel 'mysql-master2';


mysql> start slave for channel 'mysql-master1';
mysql> start slave for channel 'mysql-master2';

mysql> show slave status\G

四、验证集群

在mysql-master1上创建1个库，查看其他节点是否同步

mysql> create database db1;

以上看出没有问题，再来试试mysql-master2插入数据，在mysql-master2节点操作

mysql> create table db1.table1(id int,age int);