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不會在高併發條件下由於主鍵相同導致同步失敗。