MySQL的双主模型

1、双主模型的概述
为了减少主库的写操作压力，有时MySQL数据库会使用双主模型，即两个数据库服务器互为主从，以保证数据的冗余性和负载用户的写操作压力。
主主模式可能发生的问题：
1）数据不一致
2）自动增长id；如果用户写入的id发生了冲突，那么两个数据库的数据便有可能不一致，只有使一个服务器使用奇数ID，另一个服务器使用偶数ID，这样数据在写入的时候就不会导致数据冲突。

2、配置步骤：
（1）各节点使用一个唯一server_id；
（2）都启动binary log和relay log；
（3）创建拥有复制权限的用户账号；
（4）定义自动增长id字段的数值范围为奇偶；
（5）均把对方指定为主节点，并启动复制线程；

3、实验配置：

1）修改主配置文件

[root@master1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_bin=master1-bin      //开启二进制日志功能；
relay_log=relay1-log	   //开启中继日志功能；
server_id=1                  //指明数据库的server id；
auto_increment_offset=1     //设置自动增长列；
auto_increment_increment=2

2）启动服务

[root@master1 ~]# systemctl start mariadb.service
[root@master1 ~]# 

3）修改另外一台主库的配置文件

[root@master2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_bin=master2-bin
relay_log=relay2-log
server_id=3
auto_increment_offset=2
auto_increment_increment=2

4）启动服务

[root@master2 ~]# systemctl start mariadb.service
[root@master2 ~]# 

5）在两台数据库上创建具有复制权限的用户
第一个数据库：

[root@master1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> grant  replication slave,replication  client on *.* to 'haha'@'192.168.126.134' identified by 'haha';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush  privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| master1-bin.000001 |       264 |
| master1-bin.000002 |       506 |
+--------------------+-----------+
2 rows in set (0.00 sec)

第二个数据库：

[root@master2 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> grant replication slave,replication client on *.* to 'xixi'@'192.168.126.132' identified by 'xixi';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| master2-bin.000001 |       503 |
+--------------------+-----------+
1 row in set (0.00 sec)

6）两端进行账号认证

MariaDB [(none)]> change master to  master_host='192.168.126.134',master_user='xixi',master_password='xixi',master_log_file='master2-bin.000001',master_log_pos=503;
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.126.134
                  Master_User: xixi
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master2-bin.000001
          Read_Master_Log_Pos: 503
               Relay_Log_File: relay1-log.000002
                Relay_Log_Pos: 531
        Relay_Master_Log_File: master2-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 503
              Relay_Log_Space: 820
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 3
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]> change master to master_host='192.168.126.132',master_user='haha',master_password='haha',master_log_file='master1-bin.000002',master_log_pos=506;
Query OK, 0 rows affected (0.06 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.126.132
                  Master_User: haha
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1-bin.000002
          Read_Master_Log_Pos: 506
               Relay_Log_File: relay2-log.000002
                Relay_Log_Pos: 531
        Relay_Master_Log_File: master1-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 506
              Relay_Log_Space: 820
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR: No query specified

7）测试
在第一个数据库上创建库和表并插入id为奇数的数据

MariaDB [aaa]> create database  bbb;
Query OK, 1 row affected (0.00 sec)
MariaDB [bbb]> create table  tb1(id int(10),name char(20));
Query OK, 0 rows affected (0.00 sec)

MariaDB [bbb]> insert into  tb1(id,name)  values(1,'aaa'),(3,'bbb'),(5,'ccc');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [bbb]> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    3 | bbb  |
|    5 | ccc  |
+------+------+
3 rows in set (0.00 sec)

MariaDB [bbb]> 

在第二个数据库上查看数据是否同步并插入偶数id数据

MariaDB [bbb]> insert into  tb1(id,name)  values(2,'ddd'),(4,'eee'),(6,'fff');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
MariaDB [bbb]> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    3 | bbb  |
|    5 | ccc  |
|    2 | ddd  |
|    4 | eee  |
|    6 | fff  |
+------+------+
6 rows in set (0.00 sec)

MariaDB [bbb]>