mysql 双主多从配置指导

一、背景

互联网项目为了数据的可靠性和架构的可拓展性经常会用到双主多从的数据库，来实现数据的备份、负载均衡和突发状况时数据库切换。

二、思路

• 配置两台数据库a、b互为主从，然后配置以a为主的从数据库c，和以b为主的从数据库d

• 在a和b的配置中添加：

  log-slave-updates=1 

  将所有的操作写入到binary log。使得a的更改不止同步到b和c，还会同步到d。同理b也会同步到c。

三、开发环境

ubuntu16.04.5lts/i5/8g/500g/64位/mysql5.7.23/

四、配置步骤

1、安装三个数据库实例b、c、d，端口号分别为3307、3308、3309。

​ 请参考公众号【cto每日参考】 mysql5.7 多实例配置指导

2、主数据库a、b配置同步账号，创建同步数据库​

​ 分别进入a和b数据库，执行以下操作：

create user 'tongbu'@'localhost' identified by '123456';      //本机测试所以只需开localhost
​
create database mmdb default charset utf8 collate utf8_general_ci;    //创建同步数据库
​
grant replication slave,reload,super on *.* to tongbu@'localhost' identified by '123456';     //给同步账户授予同步相关权限
​
flush privileges;    //刷新权限

3、修改数据库配置文件

3-1 配置a [mysqld]添加以下：

log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
server-id=1  #服务器标识
binlog-do-db=mmdb  #需要同步的数据库名
binlog-ignore_db=mysql  #不需要同步的数据库名
replicate-do-db=mmdb  #需要同步的数据库名
replicate_ignore_db=mysql   #不需要同步的数据库名
​
#sync-binlog = 1   #开启会降低性能，但是数据更加安全
​
log-slave-updates=1  //将所有的操作写入到binary log
​
#避开并发时主键相同出错
auto_increment_offset = 2   //自增长初始值
auto_increment_increment = 2  //自增长值
​

3-2 配置b大致与配置a相同，差异如下：

server-id=2  #服务器标识
auto_increment_offset = 1   //自增长初始值

3-3 配置c:

server-id=3
relay-log=relay-bin
relay-log-index=relay-bin-index
replicate-do-db=mmdb
replicate_ignore_db=mysql

3-4 配置d大致与配置c相同，差异如下：

server-id=4

 

4、主从设置

思路：分别获取库a和库b的 file和position值，利用change master命令分别使得a为b的从库,b为a的从库，c为a的从库,d为b的从库。

具体请参考公众号【cto每日参考】 mysql 主从复制配置指导及 php 读写分离源码分析

 

5、测试

① 建表测试

在库a服务器的mmdb数据库中新建表mmtb

create table mmtb(id int not null auto_increment,name varchar(32) not null default '',primary key (id));

在其他服务器会发现bcd都多了该表：

show databases;
use mmdb;
show tables;

+----------------+
| tables_in_mmdb |
+----------------+
| mmtb           |
+----------------+
1 row in set (0.00 sec)

②插入操作测试

在库a中查询是否有姓名“nmx存在”：

mysql> select * from mmtb where name = 'nmx';
empty set (0.00 sec)

在b中插入数据：

mysql> insert into mmtb (name) values ('nmx');
query ok, 1 row affected (0.06 sec)

插入成功查询b库：

mysql> select * from mmtb where name = 'nmx';
+----+------+
| id | name |
+----+------+
| 17 | nmx  |
+----+------+
1 row in set (0.00 sec)

此时a库查询：

mysql> select * from mmtb where name = 'nmx';
+----+------+
| id | name |
+----+------+
| 17 | nmx  |
+----+------+
1 row in set (0.00 sec)

同时，c库和d库：

mysql> select * from mmtb where name = 'nmx';
+----+------+
| id | name |
+----+------+
| 17 | nmx  |
+----+------+
1 row in set (0.00 sec)

验证成功！

五、温馨提示

1. 实际操作过程中，请使用相同版本数据库（本文mysql多开实例，不存在数据库版本问题）。

2. 主库配置需设置log-slave-updates=1使得数据库将所有的操作写入到binary log，否则a库的操作不会同步到d库，同理b库操作不会同步到c库。

3. 主库配置auto_increment_offset 和auto_increment_increment 两个参数，使得互为主从的a和b不会在高并发条件下由于主键相同导致同步失败。