主主复制的本质就是2台MySQL服务器互为主从。

但如此配置极易产生问题，如数据不一致导致主键的冲突，以及一些其他的错误。

为了减少主键冲突的情况，可以考虑让两个节点的id分别使用奇数和偶数，这就需要用到两个服务器选项来配置。

1
2
auto_increment_offset       #设置id的开始点
auto_increment_increment    #设置id的步进

主主复制工作中不推荐使用，如确实需要使用，也将其当为主从来使用。

主主复制的搭建

使用2台主机来配置主主复制
|主机|ip|
|:-|:-|
|Master1|192.168.73.110|
|Master2|192.168.73.111|

配置Master1

1.修改配置文件

1
2
3
4
5
6
[root@Master1 ~]# vim /etc/my.cnf
[mysqld]
log-bin
server-id=1
auto_increment_offset=1
auto_increment_increment=2

2.启动MySQL服务

1
[root@Master1 ~]# systemctl start mariadb

3.查看二进制日志位置

1
2
3
4
5
6
[root@Master1 ~]# mysql -e "SHOW MASTER LOGS;"
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+

4.创建一个用来复制数据的用户

1
[root@Master1 ~]# mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.73.%' IDENTIFIED BY 'centos';"

配置Master2为Master1的从节点

1.修改配置文件

1
2
3
4
5
6
[root@Master2 ~]# vim /etc/my.cnf
[mysqld]
log-bin
server-id=2
auto_increment_offset=1
auto_increment_increment=2

2.设置CHANGE MASTER TO

1
2
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.73.110', MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)

3.查看从节点状态，确认无误

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.73.110
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No

4.启动线程

1
2
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

5.再次查看从节点状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.73.110
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 407
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 693
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes          #线程已经全部启动

6.查看二进制日志位置

查看二级制日志位置用于，给Master1作为从节点使用。由于Master2上无数据二进制日志为干净日志，所以可以直接供Master1使用。

1
2
3
4
5
6
7
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+
1 row in set (0.00 sec)

配置Master1为Master2的从节点

1.输入CHANGE MASTER TO的信息

1
CHANGE MASTER TO MASTER_HOST='192.168.73.111', MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=245;

2.查看从状态,确认信息无误

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.73.111
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No

3.启动线程

1
2
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

4.再次查看slave status

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.73.111
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 531
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

主主复制搭建完毕

测试

测试一、查看Master1输入数据，Master2能否复制

1.从Master1上导入hellodb数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[root@Master1 ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@Master1 ~]# mysql < hellodb_innodb.sql
[root@Master1 ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

2.从节点上查看数据库

1
2
3
4
5
6
7
8
9
10
[root@Master2 ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

测试二、Master2插入数据查看Master1是否能复制

1.在Master2中插入条记录

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@Master2 ~]# mysql -e "INSERT hellodb.teachers(name,age) VALUE ('Ye Fan','25');"
[root@Master2 ~]# mysql -e "INSERT hellodb.teachers(name,age) VALUE ('Shi Hao','20');"
[root@Master2 ~]# mysql -e "SELECT * FROM hellodb.teachers"
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Ye Fan        |  25 | NULL   |
|   7 | Shi Hao       |  20 | NULL   |      #此处可以看到插入数据时主键tid是以2为步进递增的。
+-----+---------------+-----+--------+

2.在Master1上查看数据

1
2
3
4
5
6
7
8
9
10
11
[root@Master1 ~]# mysql -e "SELECT * FROM hellodb.teachers;"
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Ye Fan        |  25 | NULL   |
|   7 | Shi Hao       |  20 | NULL   |
+-----+---------------+-----+--------+

测试三、两边同时创建一张相同的表

1.同时对两个主机做出创建表的操作

主主1.png

主主2.png

2.查看Master1的hellodb库

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@Master1 ~]# mysql -e "SHOW TABLES FROM hellodb"
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| test              |
| toc               |
+-------------------+

3.查看Master2的hellodb库

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@Master2 ~]# mysql -e "SHOW TABLES FROM hellodb"
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| test              |
| toc               |
+-------------------+

此处看上好像没问提

测试四、继续插入数据,从看看复制状况

1.在Master1上继续往hellodb.test表中插入数据

1
[root@Master1 ~]# mysql -e "INSERT hellodb.test VALUE(1,'Tang San');"

2.Master2上查看复制状况

1
2
3
4
5
[root@Master2 ~]# mysql
MariaDB [(none)]> SELECT * FROM hellodb.test;
Empty set (0.00 sec)

#没有复制到数据

3.查错

分别查看Master1和Master2主机上的SLAVE STATUS;

Master1状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.73.111
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 871
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 1018
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1050
                   Last_Error: Error 'Table 'test' already exists' on query. Default database: ''. Query: 'CREATE TABLE hellodb.test(id int auto_increment primary key,name  char(20))'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 732
              Relay_Log_Space: 1453
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1050
               Last_SQL_Error: Error 'Table 'test' already exists' on query. Default database: ''. Query: 'CREATE TABLE hellodb.test(id int auto_increment primary key,name  char(20))'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
1 row in set (0.00 sec)

ERROR: No query specified

Master2状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.73.110
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 8360
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 8308
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1050
                   Last_Error: Error 'Table 'test' already exists' on query. Default database: ''. Query: 'CREATE TABLE hellodb.test(id int auto_increment primary key,name  char(20))'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 8022
              Relay_Log_Space: 8942
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1050
               Last_SQL_Error: Error 'Table 'test' already exists' on query. Default database: ''. Query: 'CREATE TABLE hellodb.test(id int auto_increment primary key,name  char(20))'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR: No query specified

显示出来刚在在创建表时已经复制出错，由于两边同时创建了同一张表发生了冲突

排错

分别在主从节点上停止线程

1
MariaDB [(none)]> STOP SLAVE;

分别在主从节点上使用sql_slave_skip_counter忽略错误

1
MariaDB [(none)]> SET GLOBAL sql_slave_skip_counter=1;

分别在主从节点上再次启动线程

1
MariaDB [(none)]> START SLAVE;

再次在从节点上查test表

1
2
3
4
5
6
[root@Master2 ~]# mysql -e "SELECT * FROM hellodb.test;"
+----+----------+
| id | name     |
+----+----------+
|  1 | Tang San |
+----+----------+

此时数据已经能正常复制过去