DevOps

如何建立 MySQL Replica DB？

到這就設定完成囉！接下來只要 master db 有任何資料變化，都會同步到 slave db 上，大家可以試試看！
2022-11-18 by J No Comments

建立 Replica DB 其實沒有想像中困難，只是步驟比較繁瑣，這邊手把手分享一下作法。為了方便實驗，這邊採用 docker 在本機起兩個 MySQL container 實作。

架設實驗環境

範例原始碼在此下載：github

準備 Master 和 Slave 的 MySQL config file

master 的部分

  • 必須給定 server-id ，建立主從架構時 mysql 必須能辨識每一台 server，這邊給數字 1
  • replication 透過 binary log 串流實現，因此 master 必須開啟 log-bin 功能，並給定 max size
  • 將檔案儲存為 master.cnf
[mysqld]
server-id        = 1
log-bin          = mysql-bin
max_binlog_size  = 100M
user             = mysql
default-authentication-plugin=mysql_native_password

slave 的部分

  • 一樣需要給定 server-id ，這邊給數字 2
  • 一樣需要開啟 log-bin 功能
  • 因為 slave 的角色是 replica db，只能讀不能寫，因此開啟 read_only
  • 將檔案儲存為 slave.cnf
[mysqld]
server-id        = 2
log-bin          = mysql-bin
max_binlog_size  = 100M
user             = mysql
default-authentication-plugin=mysql_native_password

# set READ-ONLY mode
read_only    = 1

起兩個 MySQL container

在同樣目錄下建立 docker-compose.yml 檔案

version: '3'
services:
  master:
    image: mysql/mysql-server:8.0.22
    volumes:
      - ./master.cnf:/etc/my.cnf
    networks:
      db:
    environment:
      MYSQL_ROOT_PASSWORD: 12345
      MYSQL_DATABASE: test
      MYSQL_USER: user
      MYSQL_PASSWORD: 12345

  slave:
    image: mysql/mysql-server:8.0.22
    volumes:
      - ./slave.cnf:/etc/my.cnf
    networks:
      db:
    environment:
      MYSQL_ROOT_PASSWORD: 12345
      MYSQL_USER: user
      MYSQL_PASSWORD: 12345

networks:
  db:
    driver: bridge

啟動 db container

$ docker-compose up -d

等待 db ready 後即可開始設定 replication。

註：如果 master db 已存在，此時想要開啟 replication，只需如上修改 mysql config 檔案後，重啟 db 即可繼續往下。

在 Master DB 建立假資料

為了模擬 slave 是在 master 使用一段時間才建立，我們在 master 的 test db 簡單建立資料

(test db 是透過 docker-compose yaml file 的 environment 設定自動建立）

// 在 host 輸入指令進入 master db container
$ docker exec -it db-master bash

// 進入 master db contaier 後輸入
$ mysql -u root -p12345

進入 mysql shell，輸入以下 sql

USE test;

CREATE TABLE account (
    id int,
    name varchar(255)
);

INSERT INTO account (id, name) VALUES (1, 'JIM');

確認一下是否有資料

mysql> SELECT * FROM account;

+------+------+
| id   | name |
+------+------+
|    1 | JIM  |
+------+------+
1 row in set (0.01 sec)

遷移資料

目前 slave db 裡是空的，在建立 replica 前，需要先把 master db 裡的資料全部倒過來，後續才能啟動 binlog 串流。

一步驟法，同時 dump 並 restore

因為兩個 db container 可以直接連線，我們進入 slave db container 的 bash shell，用 mysqldump 指令合併 mysql 指令同時 dump 並 restore。

首先讓 account user 可以從外部讀取 master 所有資料庫

// 在 master db
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';

接著讓 master db 暫時 lock 成 read only，避免倒資料過程中發生寫入而不 match

// 在 master db
mysql> FLUSH TABLES WITH READ LOCK;

切換到 slave db container，透過 pipe | ，將 mysqldump 讀出來的 sql 直接送入後面的 mysql 指令作寫入還原。

因為兩個 container 是在同一個 docker network 下，我們可以使用 domain name master 直接連進 master db container。

// 在 host 輸入指令進入 slave db container
$ docker exec -it db-slave bash

// 進入 slave db container 後輸入
$ mysqldump -h master -u user -p12345 --all-databases | mysql -u root -p12345

還原完畢，進入 slave db 確認是否有成功建立資料

// 在 slave db container
$ mysql -u root -p12345

// 在 slave db
mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> USE test;
mysql> SELECT * FROM account;

+------+------+
| id   | name |
+------+------+
|    1 | JIM  |
+------+------+
1 row in set (0.01 sec)

到此還原工作完畢，記得回到 master db 解除 read lock

// 在 master db
mysql> UNLOCK TABLES;

二步驟法，先 dump db 成 sql file， 再 restore db

如果安全性問題，不能讓高權限的帳號從外部連入時，可以使用兩步驟建立，這邊簡單說明。

一樣先讓 master db 成 read only 狀態

// 在 master db
mysql> FLUSH TABLES WITH READ LOCK;

接著在 master db 機器的 bash shell 跑此指令，將 db dump 成 sql file

// 在 master db 機器
$ mysqldump -u root -p12345 --all-databases > master.sql

解除 read only

// 在 master db
mysql> UNLOCK TABLES;

將 sql file 複製到 slave db 機器上，並透過此指令還原

// 在 slave db 機器
$ mysql -u root -p12345 < master.sql

啟動 Slave Replication

資料搬移完畢，緊接著設定 MySQL slave。

首先確認目前 master binlog 狀態

// 在 master db
mysql> SHOW MASTER STATUS \G

*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 891
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

接著切換到 slave db 填入以上資訊作設定

// 在 slave db
mysql> CHANGE MASTER TO MASTER_HOST='master', 
          MASTER_USER='user', 
          MASTER_PASSWORD='12345',
          MASTER_PORT=3306,
          MASTER_LOG_FILE='mysql-bin.000003', 
          MASTER_LOG_POS=891, 
          MASTER_CONNECT_RETRY=30;

啟動 slave

// 在 slave db
mysql> START SLAVE;

檢查 slave 狀態，確認是否成功連線

如果 Slave_IO_Running 和 Slave_SQL_Running 都是 yes ，表示 replication 正常運作了！

// 在 slave db
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 891
               Relay_Log_File: 08cd79344797-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
             Master_Server_Id: 1
                  Master_UUID: 7a46f5b2-666a-11ed-9c00-0242c0a80003
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
...

也回到 master db 確認下連線狀況

// 在 mysql db
mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | 7a47f93d-666a-11ed-ab1f-0242c0a80002 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

到這就設定完成囉！接下來只要 master db 有任何資料變化，都會同步到 slave db 上，大家可以試試看！

範例原始碼在此下載：github

疑難雜症

重設 Slave

如果過程中搞爛了，想要清掉 slave 設定重來，可以先停止跟隨，並且 reset 設定

mysql> STOP SLAVE;
mysql> RESET SLAVE;

修正後再使用前述指令重新設定 slave 並啟動 replication 即可。

Authentication requires secure connection

如果 SHOW SLAVE STATUS \G 發現有以下錯誤

...
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
...
Last_IO_Error: error connecting to master 'user@master:3306' - retry-time: 30 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

這是因為 mysql 8 後啟用新的密碼機制，可以用以下指令解決

// 在 master db
mysql> ALTER USER 'user' IDENTIFIED WITH mysql_native_password;

或是在 mysql config 中指定使用 mysql_native_password，但必須重啟 db，並只會套用在未來新增的帳號上

default-authentication-plugin=mysql_native_password

Access denied

如果 SHOW SLAVE STATUS \G 發現有以下錯誤

...
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
...
Last_IO_Error: error connecting to master 'user@master:3306' - retry-time: 30 retries: 1 message: Access denied for user 'user'@'db-slave.db_db' (using password: YES)

檢查帳號密碼是否錯誤，確認該帳號是否可以從外部連入

Slave IO Running always No

如果 SHOW SLAVE STATUS \G 發現

...
Slave_IO_Running: No
Slave_SQL_Running: Yes

可能是 binlog file 或 position 設定錯誤

// 到 master db 裡檢查
mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 891
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
// 到 slave db 裡檢查
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: master
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000001   <-- 設定與 master 不一致
          Read_Master_Log_Pos: 891

此時 stop slave 跟隨，重新設定即可

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=891;
mysql> START SLAVE;

Last_SQL_Error

如果發現以下錯誤

Last_SQL_Error: Could not execute Delete_rows event on table

表示 master 要刪除的資料在 slave 找不到，可以參考這裡的解法

參考資料

Authentication plugin ‘caching_sha2_password’ cannot be loaded
MySQL Master Slave Replication 主從式架構設定教學
MySQL資料同步，出現Slave_SQL_Running：no和slave_io_running：no問題的解決方法
MySQL主从同步报错故障处理记录

Written by J
雖然大學唸的是生物，但持著興趣與熱情自學，畢業後轉戰硬體工程師，與宅宅工程師們一起過著沒日沒夜的生活，做著台灣最薄的 intel 筆電，要與 macbook air 比拼。 離開後，憑著一股傻勁與朋友創業，再度轉戰軟體工程師，一手扛起前後端、雙平台 app 開發，過程中雖跌跌撞撞，卻也累計不少經驗。 可惜不是那 1% 的成功人士，於是加入其他成功人士的新創公司，專職開發後端。沒想到卻在採前人坑的過程中，拓寬了眼界，得到了深層的領悟。