Mysql多源主从复制

实验环境

准备四台主机

操作系统	主机地址	主机名
CentOS 7.9	192.168.80.135	mysql-master1
CentOS 7.9	192.168.80.138	mysql-master2
CentOS 7.9	192.168.80.136	mysql-slave1
CentOS 7.9	192.168.80.130	mysql-slave2

分别配置host文件

vim /etc/hosts
192.168.80.135  mysql-master1
192.168.80.138  mysql-master2
192.168.80.136  mysql-slave1
192.168.80.130  mysql-slave2

安装mysql-server 或者已安装mysql的清理下环境

1、关闭系统防火墙和内核防火墙

[root@mysql-slave2 ~]# systemctl stop firewalld 
[root@mysql-slave2 ~]# setenforce 0

2、清理环境

[root@mysql-master ~]# rm -rf /var/lib/mysql/*  /var/log/mysqld.log

3、配置mysql配置文件 vim /etc/my.cnf

server-id=1               # 四台主机ID不能相同
log-bin=mylog
master-info-repository=TABLE
relay-log-info-repository=TABLE

4、启动mysql服务过滤并修改密码

[root@mysql-master ~]# systemctl start mysqld
[root@mysql-master ~]# grep password /var/log/mysqld.log 
2024-08-20T05:58:37.988588Z 1 [Note] A temporary password is generated for root@localhost: ge=T7_2z?p7i
[root@mysql-master ~]# mysqladmin -uroot -p'ge=T7_2z?p7i'  password 'Zjf@2024'

5、创建远程有访问binlog日志文件和远程登录权限的用户（两台master都需要创建）

mysql>grant replication slave on *.* to 'zjf'@'192.168.80.%' identified by 'Zjf@2024';
Query OK, 0 rows affected, 1 warning (0.00 sec)

6、配置两台主节点（master）

分别查看两台主机的binlog日志名字和位置

master1

master2

master1配置

mysql>\e 
change master to 
master_host='mysql-master2',
master_user='zjf',
master_password='Zjf@2024',
master_log_file='mylog.000002',
master_log_pos=414 for channel 'mysql-master2';
->;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

开启slave 并查看状态是否双yes

master2配置

mysql>\e 
change master to 
master_host='mysql-master1',
master_user='zjf',
master_password='Zjf@2024',
master_log_file='mylog.000002',
master_log_pos=859 for channel 'mysql-master1';
->;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

开启slave 并查看状态是否双yes

7、配置两台从节点slave（两台从节点配置一样）

mysql>\e 
change master to 
master_host='mysql-master1',
master_user='zjf',
master_password='Zjf@2024',
master_log_file='mylog.000002',
master_log_pos=859 for channel 'mysql-master1';
->;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql>\e 
change master to 
master_host='mysql-master2',
master_user='zjf',
master_password='Zjf@2024',
master_log_file='mylog.000002',
master_log_pos=414 for channel 'mysql-master2';
->;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

8、配置完成后开启slave 并验证是否双yes

配置完成，可以进行创建库、表进行验证。