mysql主主双向主从M-M(互为主从)

M-M 双主互备 (互为主从)

这种的搭建就是在主从的基础上从新配置一次就可以，很简单
10.10.100.28主
10.10.100.22从
主从已经是搭建好，现在把从变成主，把主变成从
搭建好的效果就是两边都可以写，
开始配置
授权

mysql> grant replication slave on *.* to slave@'10.10.100.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
如果不符合密码要求修改如下（5.7版本）
mysql> set global validate_password_policy=0;  #定义复杂度
mysql> set global validate_password_length=1;  #定义长度 默认是8
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

修改配置文件，启动二进制日志

[root@localhost ~]# vim /etc/my.cnf
server-id=2
log-bin=mysql-bin-slave
binlog-do-db=test
binlog-ignore-db=mysql

重启服务

[root@localhost ~]# systemctl restart mysqld

查看master状态

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

测试连接

[root@localhost ~]# mysql -uslave -p123456 -h10.10.100.28
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

mysql> stop slave;   #停止从服务
Query OK, 0 rows affected, 1 warning (0.00 sec)

将远程连接用户写到数据库里面
mysql> change master to master_host='10.10.100.28',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> flush privileges;      #刷新权限表
mysql> start slave;      启动从服务
Query OK, 0 rows affected (0.01 sec)

查看从状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.100.28
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-slave.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 379
        Relay_Master_Log_File: mysql-bin-slave.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

看见两个yes,就搭建成功了

插入数据测试数据同步

mysql> insert into test values (4,'test23');
Query OK, 1 row affected (0.00 sec)

查看

mysql> select * from test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | dz     |
|    1 | ddz    |
|   44 | ddz    |
|   33 | slave  |
|    4 | test23 |
+------+--------+
5 rows in set (0.00 sec)