mariadb 主主同步

环境：两台虚拟机，ip分别为A:192.168.1.5， B:192.168.1.9 ，centos7， mariadb5.5.6.0（本实验两台机器是复制得到的，所以hostname一样）（一）说明：主从结构中，主机master中的insert等操作会同步到从机slave中，但是slave中的操作不会同步到master中。在主主架构中，两台主机互为主从，并且都能向外提供服务。（二）...

Man_In_The_Night

1560人浏览 · 2018-12-28 19:00:23

Man_In_The_Night · 2018-12-28 19:00:23 发布

环境：两台虚拟机，ip分别为A:192.168.1.5， B:192.168.1.9 ，centos7， mariadb5.5.6.0

（本实验两台机器是复制得到的，所以hostname一样）

（一）说明：主从结构中，主机master中的insert等操作会同步到从机slave中，但是slave中的操作不会同步到master中。在主主架构中，两台主机互为主从，并且都能向外提供服务。

（二）主主复制过程中server-id说明

因为主主复制架构中是两台服务器互为主从，所以两台服务器必须都有填制日志和中继日志，而且他们的server-id必须不能一样。

server-id说明参考

（三）主主同步实现

关掉A和B的mariadb

[root@node2 /]# systemctl stop mariadb

1、修改A虚拟机的配置文件/etc/my.cnf （操作对象：A）

在[mysqld]下面增加6行

log-bin=/data/binlogs/master-bin #二进制日志
relay-log=/data/relaylogs/relay-mysql #中继日志
binlog_format=mixed #二进制日志模式
server-id = 1 #server-id 唯一
auto-increment-offset = 1 #设置起始值从1开始
auto-increment-increment = 2 #步长为2

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

log-bin=/data/binlogs/master-bin #二进制日志
relay-log=/data/relaylogs/relay-mysql #中继日志
binlog_format=mixed  #二进制日志模式
server-id = 1 #server-id 唯一
auto-increment-offset = 1 #设置起始值从1开始
auto-increment-increment = 2 #步长为2

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

2、同理修改B虚拟机的配置文件/etc/my.cnf （操作对象：B）

server-id和auto-increment-offset 与A虚拟机不同

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
log-bin=/data/binlogs/master-bin #二进制日志
relay-log=/data/relaylogs/relay-mysql #中继日志
binlog_format=mixed #二进制日志模式
server-id = 2 #server-id 唯一
auto-increment-offset = 2 #设置起始值从1开始
auto-increment-increment = 2 #步长为2


[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

3、创建存放二进制和中继日志的文件目录（操作对象：AB）

[root@node2 ~]# mkdir -p /data/binlogs/master-bin
[root@node2 ~]# mkdir -p /data/relaylogs/relay-mysql
[root@node2 ~]# chown -R mysql:mysql /data/

修改security context

[root@node2 ~]# semanage fcontext -a -t mysqld_db_t "/data(/.*)"
[root@node2 ~]# restorecon -R -v /data/

启动mariadb

[root@node2 /]# systemctl start mariadb

创建同步账号synchro，密码123456

MariaDB [(none)]> create user synchro identified by '123456';
Query OK, 0 rows affected (0.00 sec)

4、A虚拟机授权一个有复制权限的账号synchro给B虚拟机（操作对象：A）

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

5、B虚拟机授权一个有复制权限的账号synchro给A虚拟机（操作对象：B）

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

6、显示A虚拟机状态信息，且去连接B虚拟机（操作对象：A）

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

MariaDB [(none)]> change master to master_host='192.168.1.9',master_user='synchro',master_password='123456',master_log_file='master-bin.000001',master_log_pos=868;
Query OK, 0 rows affected (0.01 sec)

master_log_file='master-bin.000001',master_log_pos=868来自B虚拟机的状态

7、显示B虚拟机状态信息，且去连接A虚拟机（操作对象：B）

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

MariaDB [(none)]> change master to master_host='192.168.1.5',master_user='synchro',master_password='123456',master_log_file='master-bin.000001',master_log_pos=530;
Query OK, 0 rows affected (0.00 sec)

master_log_file='master-bin.000001',master_log_pos=530来自A虚拟机的状态

8、在A虚拟机上启动复制线程，并查看是否连接B虚拟机成功（操作对象：A）

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

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting to reconnect after a failed registration on master
                  Master_Host: 192.168.1.9
                  Master_User: synchro
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 868
               Relay_Log_File: relay-mysql.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes

ps: 其实我这个地方出了点错，所以显示Slave_IO_State: Waiting to reconnect after a failed registration on master，正常应该为Slave_IO_State: Waiting for master to send event。原因是我第5步的时候把192.168.1.5写成192.168.1.6了。解决方法：重新执行第五步就行，执行完刷新flush privileges

9、在B虚拟机上启动复制线程，并查看是否连接A虚拟机成功（操作对象：B）

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.1.5
                  Master_User: synchro
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 620
               Relay_Log_File: relay-mysql.000002
                Relay_Log_Pos: 620
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

参考博客

补充：当设置自动增长id时，会出现1，3，5，此时需要修改/etc/my.cnf，将步长改为1，然后重启服务

auto-increment-increment = 1#步长为1