主主复制模型

所谓主主复制就是两台主机互为主从，每台主机都可写，都可读。两台主机互为主从。
注意：当客户端有写的请求的话，主主复制模型都写入，而不是只写一台主机。两台主机用不同的ID标识来区别不同的数据库服务器。

一：配置步骤

（1）各节点使用一个唯一的sever-id标识
（2）都启动binarylog和relaylog
（3）创建拥有复制权限的用户账号
（4）定义自动增长ID字段的数值范围为奇数偶数
（5）均把对方指定为主节点，并启动复制线程

二：配置过程：
两个节点

主节点	主节点
10.5.100.208	10.5.100.207
node1	node2

NODE1配置：

[root@node1 ~]# vim /etc/my.cnf
relay-log=relay-log
server-id=2
skip_name_resolve=ON
innodb_file_per_table=ON
log-bin=mysql-bin
auto_increment_offset=1     #设置字段id是奇数从1开始，以2个字段增长
auto_increment_increment=2

[root@node1 ~]# systemctl restart mariadb
[root@node1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.41-MariaDB-log MariaDB Server

Copyright (c) 2000, 2014, 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 'repluser'@'10.5.100.208' identified by 'replpass';
Query OK, 0 rows affected (0.00 sec)

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

查看NODE2节点的日志文件及位置；
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      508 |              |                  |
+------------------+----------+--------------+------------------+

MariaDB [(none)]> change master to master_host='10.5.100.208', master_user='repluser', master_password='replpass', master_log_file='mysql-bin.000003', master_log_pos=508;
Query OK, 0 rows affected (0.00 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: 10.5.100.208
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 508
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000003
             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: 817
              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)

NODE2节点配置：

[root@node1 ~]# vim /etc/my.cnf
relay-log=relay-log
server-id=2
skip_name_resolve=ON
innodb_file_per_table=ON
log-bin=mysql-bin
auto_increment_offset=2     #设置字段id是偶数从2开始，以2个字段增长
auto_increment_increment=2

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

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

查看NODE1节点的二进制日志文件及位置；
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      508 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> change master to
    -> master_host='10.5.100.207',
    -> master_user='repluser',
    -> master_password='replpass',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=508;
Query OK, 0 rows affected (0.00 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: 10.5.100.207
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 508
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000003
             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: 825
              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: 2
1 row in set (0.00 sec)

进行验证：
NODE1节点

MariaDB [(none)]> create database yan
    -> ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| yan                |           
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      594 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

NODE2节点进行查看

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| yan                |
+--------------------+
5 rows in set (0.00 sec)

总结一点：主主复制模型，客户端写入时两个数据库都写入，而不是复制。