mysql 两主两从配置

环境配置：
192.168.136.165 master1
192.168.136.202 master2
192.168.136.195 slave1
192.168.136.178 slave2
这里用的是mysql5.7版本的
安装mysql5.7见https://blog.youkuaiyun.com/qq_47905850/article/details/106273431

1，修改mysql配置文件
master

[root@bogon ~]# vim /etc/my.cnf

server-id=1
log-bin=mysql-bin
relay-log=mysql-relay
auto-increment-increment=2
auto-increment-offset=1
log-slave-updates = true

slave

[root@bogon ~]# vim /etc/my.cnf

server-id=3
relay-log=mysql-relay
log-slave-updates = true

log-bin：二进制文件的位置

server-id：服务id,每台库的server-id值都要唯一

log-slave-updates：设置两台主服务器，彼此间复制的数据要不要往其下面的从服务器复

制。有这个字段表示往下面从服务器复制。

relay-log：二进制日志

2，重启MySQL

[root@bogon ~]# systemctl restart mysqld

3，分别在两台主服务器上创建数据的账号并且授权：

mysql> grant all on *.* to 'tom'@'%' identified by '758521';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.14 sec)

4，两台主服务器上设置互为主从：

1）查看两台主库的状态：
master 1

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      438 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.28 sec)

master2

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      595 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

2）执行change master

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to 
    -> master_host='192.168.136.165',
    -> master_user='tom',
    -> master_password='758521',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=438;
Query OK, 0 rows affected, 2 warnings (0.10 sec)

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

mysql> 

3）查看状态

mysql> show slave status \G;

两台上面的差不多一样，就是将IP和二进制日志，pos端口改变

5，在两台从服务器上和主上设置主从
save1
1）执行change master

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> change master to 
    -> master_host='192.168.136.165',
    -> master_user='tom',
    -> master_password='758521',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=438;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

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

mysql> 

2）查看状态：

mysql> show slave status \G;

slave2
1）执行change master

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to 
    -> master_host='192.168.136.202',
    -> master_user='tom',
    -> master_password='758521',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=595;
Query OK, 0 rows affected, 2 warnings (0.13 sec)

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

mysql> 

2）查看状态

mysql> show slave status \G;