环境：
- 192.168.205.17: as master server
- 192.168.205.27: as middle server
版本：
- OS: centos 7 1810 with mini install
- mariadb-5.5.60
目地：
测试主主复制，两个主机互为主，为了解决记录冲突，可以将主键设成起始设置为不同（如1，3，5，另一个2，4，6），增长设置为相同为2，这样主键不冲突就可以实现复制，但还是无法实现建表建库相同的冲突。
步骤：
1. 配置主服务器
2. 配置第二个主服务器
3. 测试
装服务器
- 安装两台服务器
[root@master1 ~]#yum install mariadb-server
[root@master1 ~]#mkdir /data/{mysql,logs}
[root@master1 ~]#chown mysql:mysql /data/{mysql,logs}
master1服务器配置
- 修改master1服务，并启动服务
[root@master1 ~]#vi /etc/my.cnf
[mysqld]
server-id=17
datadir=/data/mysql
log-bin=/data/logs/bin
auto_increment_offset=1
auto_increment_increment=2
[root@master1 ~]#systemctl start mariadb
```
3. 初始化数据，测试使用，并创建复制帐号
[root@master1 ~]#mysql < hellodb_innodb.sql
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos';
4. 备份数据库，并复制文件到maseter2
[root@master1 ~]#mysqldump -A --single-transaction --master-data=1 > /data/all.sql [root@master1 ~]#scp /data/all.sql 192.168.205.27:/data/
#### master2服务器配置
5. 修改配置文件
[root@master2 ~]#vi /etc/my.cnf
[mysqld]
server-id=27
datadir=/data/mysql
log-bin=/data/logs/bin
auto_increment_offset=2
auto_increment_increment=2
[root@master2 ~]#systemctl start mariadb
6. 修改备份数据，添加change master to 信息，位置是备份的，所以不用修改
[root@master2 ~]#vi /data/all.sql CHANGE MASTER TO MASTER_HOST='192.168.205.17', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, [root@master2 ~]#mysql < /data/all.sql
7. 启动线程并查看复制状态
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.205.17
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000003
Read_Master_Log_Pos: 8988
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 523
Relay_Master_Log_File: bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
8. 在master2记录复制的位置信息，在master1上也要同步
MariaDB [(none)]> show master logs;
+------------+-----------+
| Log_name | File_size |
+------------+-----------+
| bin.000001 | 30833 |
| bin.000002 | 1069459 |
| bin.000003 | 522771 |
+------------+-----------+
3 rows in set (0.00 sec)
#### 在master1配置同步master2
9. 在master1中修改change master to, 查起动slave，查看状态
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.205.27', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='bin.000003', MASTER_LOG_POS=522771; MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.27 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000003 Read_Master_Log_Pos: 523513 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes
#### 测试以下操作同时在两台服务器上
10. 两个服务器中同时执行如下命令
MariaDB [(none)]> user hellodb; MariaDB [(none)]> create table test (id int auto_increment primary key, name char(10)); MariaDB [(none)]> desc test; MariaDB [(none)]> insert test (name) values ("leo")
11. 可以看到两个服务器都能插入成功，复制没有出错，一个id为1一个id为2
MariaDB [hellodb]> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | leo |
| 2 | leo |
+----+------+
2 rows in set (0.00 sec)
- 同时插入两条记录，显示成功，同步状态无错误
MariaDB [hellodb]> insert test (name) values('zhao'),('song');
MariaDB [hellodb]> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | leo |
| 2 | leo |
| 3 | zhao |
| 4 | zhao |
| 5 | song |
| 6 | song |
+----+------+
6 rows in set (0.00 sec)
- 我们来个新表试试，同时执行创建表，显示成功，但查看复制状态
MariaDB [hellodb]> create bable test2(id int);
MariaDB [hellodb]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.205.27
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000003
Read_Master_Log_Pos: 523513
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 1168
Relay_Master_Log_File: bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1050
Last_Error: Error 'Table 'test2' already exists' on query. Default database: 'hellodb'. Query: 'create table test2(id int)'
- 设置跳过错误，停止再启动slave，成功复制
MariaDB [hellodb]> set global sql_slave_skip_counter=1;
MariaDB [hellodb]> stop slave;
MariaDB [hellodb]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.205.27
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000003
Read_Master_Log_Pos: 523513
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 523
Relay_Master_Log_File: bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
