mysql多源主从配置--双主双从

MySQL在5.7.2上面添加了多源复制（Multi-Source）功能，意味着一个从库可以连多个主库，从而同时进行同步，但是如果是同一个表的话，会存在主键和唯一索引冲突的风险，需要提前做好规划

 前提：本次使用的binlog日志方式。手动指定binlog日志名称以及位置的方式；

1.操作前的准备

关闭防火墙和selinux    配置host解析   四台机器都做     本次配置是双主双从

vim /etc/hosts
192.168.126.141  mysql-master-1
192.168.126.133  mysql-master-2
192.168.126.139  mysql-slave-1
192.168.126.142  mysql-slave-2

2.安装mysql

一定要确认mysql的版本一致     mysql --version  查看版本信息

3.修改每台机器mysql的配置文件

1.master-1

vim /etc/my.cnf

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

2.master-2

vim /etc/my.cnf

log-bin=/var/log/mysql-bin/mylog
server-id=2
master-info-repository=TABLE
relay-log-info-repository=TABLE

3.slave-1

vim /etc/my.cnf

log-bin=/var/log/mysql-bin/mylog
server-id=3
master-info-repository=TABLE
relay-log-info-repository=TABLE

4.slave-2

vim /etc/my.cnf

log-bin=/var/log/mysql-bin/mylog
server-id=4
master-info-repository=TABLE
relay-log-info-repository=TABLE

4.启动配置mysql     

双主进行创建用户即可，两个master 互为主从

启动mysql之后要改密码  跳过了

master-1和master-2分别创建用户yonghu   密码Yonghu@123

mysql> grant replication slave on *.* to yonghu@'%' identified by 'Yonghu@123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

5.双主配置

查看master-2的binlog日志名称和位置

mysql> show master status\G
*************************** 1. row ***************************
             File: mylog.000002
         Position: 699
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

然后在master-1上操作

mysql>\e

change master to
master_host='mysql-master-2',
master_user='yonghu',
master_password='Yonghu@123',
master_log_file='mylog.000002',
master_log_pos=699 for channel 'mysql-master2';

启动slave

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

 查看master-1的binlog日志名称和位置

mysql> show master status\G
*************************** 1. row ***************************
             File: mylog.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

在master-2上操作

mysql> \e
change master to
master_host='mysql-master-1',
master_user='yonghu',
master_password='Yonghu@123',
master_log_file='mylog.000001',
master_log_pos=154 for channel 'mysql-master1';

开启并查看slave

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

设置成功

经常用的操作

# 查看单个channel的状态
mysql> show slave status for channel 'mysql-master2'\G
# 停止单个channel同步
mysql>  stop slave for channel 'mysql-master2';
# 开启单个channel同步
mysql>  start slave for channel 'mysql-master2';
# 重置单个channel
mysql>  reset slave for channel 'mysql-master2';
# 查看所有channel
mysql> show slave status\G
# 开启所有channel
mysql> start slave;

 6.双从配置

1.slave-1配置

1.在slave-1上操作   设置mysql-mastre1为自己的主节点

mysql> \e

change master to
master_host='mysql-master-1',
master_user='yonghu',
master_password='Yonghu@123',
master_log_file='mylog.000001',
master_log_pos=154 for channel 'mysql-master1';

2..在slave-1上操作   设置mysql-mastre2为自己的主节点

mysql> \e

change master to
master_host='mysql-master-2',
master_user='yonghu',
master_password='Yonghu@123',
master_log_file='mylog.000002',
master_log_pos=699 for channel 'mysql-master2';

 3.启动slave   单个启动    一起启动用start slave

mysql> start slave for channel 'mysql-master1';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave for channel 'mysql-master2';
Query OK, 0 rows affected (0.00 sec)

查看状态

2.slave-2配置

1.在slave-2上操作   设置mysql-mastre1为自己的主节点

mysql> \e

change master to
master_host='mysql-master-1',
master_user='yonghu',
master_password='Yonghu@123',
master_log_file='mylog.000001',
master_log_pos=154 for channel 'mysql-master1';

2..在slave-1上操作   设置mysql-mastre2为自己的主节点

mysql> \e

change master to
master_host='mysql-master-2',
master_user='yonghu',
master_password='Yonghu@123',
master_log_file='mylog.000002',
master_log_pos=699 for channel 'mysql-master2';

3.启动slave   单个启动    一起启动用start slave

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G

7.验证集群

在master1上创建一个库ceshi

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

 master-2

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

slave-1

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

slave-2

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

8.高可用性验证

停止master-2节点

那么下面测试一下在mysql-master1上继续创建数据，看2个从节点是否还正常同步

mysql> use ceshi;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.03 sec)
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 查看slave-1和slave-2上

mysql> use ceshi
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> use ceshi;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

完成验证