MySQL/MariaDB主主数据同步配置

我们前面有文章介绍了Mysql主从复制备份的配置,那么在一些高可用的场景中需要配置主主互备,即双主数据同步。MySQL的主主同步和主从同步的原理一样,只是主主同步的双方都是主从角色。本文以MariaDB来演示主主配置细节。

在阅读本文之前,我们假设你事先已经阅读了并实际操作了本站以下文章内容:

1.准备环境

操作系统:CentOS7.4 64位

MariaDB版本:10.2.12

节点DB1:192.168.11.31

节点DB2:192.168.11.32

开了防火墙策略的需要开放端口(默认3306)。

2.配置DB1的配置文件(my.cnf)

在/etc/my.cnf中添加以下选项:

server-id  = 1
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=1
auto-increment-increment=2
binlog_format=mixed
expire_logs_days = 10
知识兔

server-id = 1服务器节点ID,必须唯一。

log-bin=mysql-bin开启二进制日志功能,mysql-bin是命名格式,会生成文件名为mysql-bin.000001、mysql-bin.000002等日志文件。

relay-log = mysql-relay-bin作为从服务器时的中继日志。

replicate-wild-ignore-table=mysql.%是复制过滤选项,可以过滤不需要复制同步的数据库或表,如“mysql.%”指不复制MySQL库下的所有表,依此类推,多个数据库就多写几行。注意不建议在主库或从库上使用binlog-do-dbbinlog-ignore-db,也不要再从库上使用replicate-do-dbreplicate-do-db选项,因为这样可能会产生跨库更新失败的问题。我们推荐使用replicate-wild-do-tablereplicate-wild-ignore-table解决复制过滤问题。

log-slave-updates=onslave 将复制事件写进自己的二进制日志。

slave-skip-errors=all跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。

auto-increment-offset=1主键自增规则,自增因子(每次加2)。

auto-increment-increment=2主键自增规则,自增偏移(从1开始),单数。

binlog_format=mixed主从复制的格式(mixed,statement,row,默认格式是 statement)。

expire_logs_days = 10日志保存天数:10天。

3.配置DB2的配置文件(my.cnf)

在/etc/my.cnf中添加以下选项:

server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=2
auto-increment-increment=2
binlog_format=mixed
expire_logs_days = 10
知识兔

DB2的my.cnf的配置基本一致,主主模式下区别有两个:

server-id = 2服务器节点ID,一定要保证唯一值,不能和DB1冲突。

auto-increment-offset=2设置主键自增规则,自增偏移2,即双数。DB1是单数,这样DB1和DB2就形成了奇偶ID不会重复的情况,就能保证表中的主键不冲突。

我们这里两台服务器DB1和DB2中的MariaDB是新安装的,没有多余的表。假如你的数据库中已经有数据了,那最好保证DB1和DB2的数据一致,在数据同步之前,先可关闭数据库的写权限。

4.重启MariaDB服务

配置好MariaDB的配置文件后,保存并重启服务,使配置生效。

/etc/init.d/mariadb restart
知识兔
注意,这里我已经按照文章《Linux下使用二进制格式安装MariaDB》安装好MariaDB,假如你使用的是yum方式安装的MariaDB,可以尝试命令:systemctl restart mariadb

5.DB1作为Master授权给DB2

在DB1中进入到MySQL终端:

[root@localhost ~]# /usr/local/mariadb/bin/mysql -uroot -p
知识兔

然后允许用户hello32,使用密码:123456x,可以从IP:192.168.11.32(DB2)访问DB1,并授予复制同步数据的权限。

MariaDB [(none)]> grant replication slave, replication client on *.* to 'hello32'@'192.168.11.32' identified by '123456x';
mysql> flush privileges;
知识兔

授权完毕后记得刷新权限表。

接着马上查看binlog文件的position(偏移)和File(日志文件)的值,从机上(DB2)需要用到:

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      358 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
知识兔

6.DB2作为Master授权给DB1

同样的,进入DB2的MySQL终端,授权给DB1的用户:

MariaDB [(none)]> grant replication slave, replication client on *.* to 'hello31'@'192.168.11.31' identified by '123456x';
mysql> flush privileges;
知识兔

接着马上查看binlog文件的position(偏移)和File(日志文件)的值,从机上(DB1)需要用到:

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000013 |      358 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
知识兔

7.配置DB1的Slave

DB1作为Slave从库,需要访问主库DB2,使用以下命令:

MariaDB [(none)]> change master to master_host='192.168.11.32',master_user='hello31', master_password='123456x', master_port=3306, master_log_file='mysql-bin.000013', master_log_pos=358, master_connect_retry=30;
知识兔

注意日志文件和偏移值是第6步DB2中show master status查看到的。master_connect_retry=30是尝试连接时间。

8.配置DB2的Slave

现在DB2作为Slave从库,需要连接主库DB1:

MariaDB [(none)]> change master to master_host='192.168.11.31',master_user='hello32', master_password='123456x', master_port=3306, master_log_file='mysql-bin.000007', master_log_pos=358, master_connect_retry=30;
知识兔

这里的日志文件和偏移值是第5步DB1中查看到的。

9.启动Slave

现在可以启动同步复制了,在两台服务器上分别使用命令:

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

在DB1上执行:

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.32
                  Master_User: hello31
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 358
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000013
             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: mysql.%,information_schema.%
知识兔

在DB2上执行:

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.31
                  Master_User: hello32
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 358
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000007
             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: mysql.%,information_schema.%
知识兔

我们看到两台服务器的数据同步信息中Slave_IO_RunningSlave_SQL_Running 都是Yes,说明我们配置成功了!

10.验证

我们在DB1上新建一个数据库:myzhishitu,然后在myzhishitu库中新建一个表article,再往article表中插入一条数据。

MariaDB [(none)]> create database if not exists myzhishitu default character set utf8 collate utf8_general_ci;
MariaDB [(none)]> use myzhishitu;
Database changed
MariaDB [myzhishitu]> insert into article (id,title) values (null, 'Helloweba');
Query OK, 1 row affected (0.01 sec)
MariaDB [myzhishitu]> insert into article (id,title) values (null, 'Helloweba');
Query OK, 1 row affected (0.01 sec)
知识兔

我们在到DB2查看数据表:

MariaDB [myzhishitu]> select * from article;
+----+-----------+
| id | title     |
+----+-----------+
|  1 | Helloweba |
+----+-----------+
1 row in set (0.00 sec)
知识兔

发现DB2的article表数据也有了。反复操作几次,看下两台数据库中的数据完全一致,说明我们的主主配置是成功的。

总结

一定要按照文中这十步依次按顺序来操作,网上有很多介绍主主配置的,但大部分写的都比较乱,自己都会看花眼。此外,后面还会有文章介绍主主高可用方案,敬请关注。

计算机