MYSQL的双主模型（主主复制)

我们用两个节点来做实验：
node1节点：172.16.251.132
node2节点：172.15.252.32

node1节点配置：

# systemctl stop mariadb.service 先停止服务
# rm -rf /var/lib/mysql/*  （这里是将mysql恢复初始状态，便于实验）
# vim /etc/my.cnf.d/server.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=1
log-bin=master-log 二进制日志
relay_log=relay-log 中继日志
auto_increment_offset=1 定义节点使用奇数ID
auto_increment_increment=2 定义节点ID号一次加几
#systemctl start mariadb.service 配置完启动服务

node2节点配置：

# systemctl stop mariadb.service 先停止服务
# rm -rf /var/lib/mysql/*  （这里是将mysql恢复初始状态，便于实验）
# vim /etc/my.cnf.d/server.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=12
log-bin=master-log 二进制日志
relay_log=relay-log 中继日志
auto_increment_offset=2 定义节点使用偶数ID
auto_increment_increment=2 定义节点ID号一次加几
#systemctl start mariadb.service 配置完启动服务

接下来对数据库进行操作：

node1节点：

#mysql
>GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.252.%' IDENTIFIED BY 'replpass';
>FLUSH PRIVILEGES;
>SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 |      508 |              |                  |
+-------------------+----------+--------------+------------------+

node2节点：

#mysql
>GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.251.%' IDENTIFIED BY 'replpass';
>FLUSH PRIVILEGES;
>SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 |      508 |              |                  |
+-------------------+----------+--------------+------------------+

node1节点：

#mysql
>CHANGE MASTER TO MASTER_HOST='172.16.252.32',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_POST=3306，MASTER_LOG_FILE='master_log.000001',MASTER_LOG_POS=508;
>START SLAVE;
>SHOW SLAVE STATUS;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.252.32
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000001
          Read_Master_Log_Pos: 508
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-log.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: 508
              Relay_Log_Space: 818
              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

node2节点：

#mysql
>CHANGE MASTER TO MASTER_HOST='172.16.251.132',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_POST=3306，MASTER_LOG_FILE='master_log.000001',MASTER_LOG_POS=508;
>START SLAVE;
>SHOW SLAVE STATUS;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.251.132
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000001
          Read_Master_Log_Pos: 508
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-log.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: 508
              Relay_Log_Space: 818
              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

接下来可以测试了：
node1：

#mysql
>CREATE DATABASE mydb;

node2:

#mysql
>USE mydb;
>CREATE TABLE tbl1 (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,Name CHAR(30));

node1:

>USE mydb;
>DESC tbl1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | char(30)         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

实验成功，两个数据库都是主，都有写和读的权限。

实验理论总结：
主主复制：
互为主从：两个节点各自都要开启binlog和relay log；
1、数据不一致；
2、自动增长id；
定义一个节点使用奇数id
auto_increment_offset=1 从几开始
auto_increment_increment=2 一次加几
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
配置：
1、server_id必须要使用不同值；
2、均启用binlog和relay log；
3、存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；
服务启动后执行如下两步：
4、都授权有复制权限的用户账号；
5、各把对方指定为主节点；