MySQL 5.7 多主一從實作

故事背景

公司有這樣一個需求，需要将某些資料用 BI 工具 Metabase 展示出來，但是這裡面就涉及到多個資料庫的聯合查詢的問題。然而這些資料庫在不同的機器上面，Metabase 部署基于同一個連接配接聯合查詢會有問題，是以就要想辦法将這些資料庫集中在一個連接配接上面。

當時方案有以下一些：

1. 使用 MySQL Federated 引擎，在特定的表上面加入聯合資料庫的連接配接。（效率很低，很慢）

2. MyCat，連接配接多個資料庫，統一到一個連接配接。（存在問題，不支援聯合）

3. DBLE，MyCat 加強版，能夠實作。（連接配接查詢也沒問題，但是結合 Metabase 的時候掃描資料不出結果）

最終選擇的方式則是通過多主一從的方式将需要的資料庫都統一到一個資料庫裡面，因為這個工具隻是很多展示作用，是以通路人數不多。

具體說說實作過程！

搭建方法

1. 首先我目前的伺服器結構是這樣的：

有三個主從結構的資料庫，分别各有資料庫 A / B / C，我們需要通過各自的 DB Server 2 從庫将各自的資料庫都同步到 DB Server all 這台伺服器上面。

具體主從實作方式可以參考我之前的部落格，這裡就不多做介紹了。

2. 在各自從庫上面配置檔案中加入 binlog 需要記錄哪些資料庫，以 A 為例：

# 記錄的資料庫
binlog-do-db=A

# 忽略的資料庫
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys      

這樣的目的是為例保證同步不會出現沖突，因為系統的那幾個庫所有的都有，多要最好忽略他們。

配置完成後需要重新開機沖庫。

3. 在各台資料庫的從庫上面分别備份指定的庫：

mysqldump -uroot -p -E -R --triggers --master-data=2 --single-transaction --max-allowed-packet=64M -B A > A.sql      

這裡以 A 庫為例，然後檢視備份檔案的指針：

head -50 A.sql      

可以看到類似的binlog 位置和指針：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=2664;      

我們需要記錄下這個：mysql-bin.000006 和 2664。

4. 在 DB Server all 資料庫上面将三個庫 A / B / C 都導入：

為提升導入速度，我們可以在登入的時候增加參數：

mysql -uroot -p --max-allowed-packet=1024M      

登入資料庫後臨時禁用檢查和自動送出，可以提升導入速度：

set foreign_key_checks=0;
set unique_checks=0;
set autocommit=off;      

然後使用 source 将三個資料庫都導入到資料庫裡面即可！

5. 修改 DB Server all 配置檔案，告訴他我們需要同步哪些資料庫：

replicate_do_db=A
replicate_do_db=B
replicate_do_db=C

replicate_ignore_db=information_schema
replicate_ignore_db=mysql
replicate_ignore_db=performance_schema
replicate_ignore_db=sys      

此時就可以重新開機資料庫，然後配置同步：

6. 在 DB Server all 上配置同步，這裡以 A 為例：

CHANGE MASTER TO
MASTER_HOST='192.168.x.x',
MASTER_PORT=3306,
MASTER_USER='xxx',
MASTER_PASSWORD='xxxxxxxxx',
MASTER_LOG_FILE='mysql-bin.0000xxx',
MASTER_LOG_POS=xxxx
FOR CHANNEL '100';       

這裡需要注意的是：

1. 注意之前備份檔案中指針和 binlog 檔案，這裡需要用到。

2. 同步使用者需要實作在所有從庫都存在。

3. 最重要的就是最後的 CHANNEL 配置，不同的資料庫必須使用不同的 CHANNEL，這樣才能分開同步，不會同步失敗。

7. 最終啟動 slave：

start slave;
show slave status\G      

可以看到這個庫的同步狀态！