有點難又不會太難的 MySQL 複寫配置

複寫是資料庫的一種備份機制，簡單的說就是同步兩台（或Ｎ台）資料庫，當然除了備份之外，如果是雙向複寫的話也是一種高可用架構。

本篇介紹的是最單純的單向複寫，單向複寫除了可以視為熱備份之外，在應用層也可以藉此做到讀寫分離，例如報表服務，因為只有讀取，又可能有很複雜的查詢，就很適合把報表服務指向副資料庫，而不會把全部的查詢都重壓在主資料庫身上。

另外一種只有大量讀取而沒有寫入的作業就是 dump 啦，雖然有複寫就有了熱備份，但還是需要定期 dump 個冷備份出來，dump 也很適合在副資料庫上操作，同樣避免了主資料庫資源被吃光的問題。

既然是同步，那就可以進一步衍申主副資料庫的量子狀態，它們之間的確具有所謂的量子糾纏，示意圖如下：

MySQL 複寫之取名的藝術

原本複寫的主資料庫、副資料庫的英文叫 master / slave，但因為政治正確的關係改叫 main / replica 或 source / replica，但有些參數還是沿用舊名，總之：

• master = main = source
• slave = replica

下文會混用，請自行腦補。

前置作業

既然 master / slave 是兩個機台，那它們就必須接受外部連線，預設剛裝完的 MySQL 是不接受外部連線的，請到 /etc/mysql/mysql.conf.d/mysqld.conf，找到以下區塊：

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1

其中 bind-address 表示只接受 127.0.0.1 的連線，也就是本機，請改為 0.0.0.0 表示接受任何位址的連線，或者設為某個 IP 只接受它的連線也可以。

後面的 mysqlx-bind-address 可以無視，它為 MySQL X plugin 連線參數，一般用不到。

其他前置作業，包括安裝、初始化配置、建帳號、設定 UTF-8 等，請見另一篇〈安裝 MySQL 與設定真・UTF-8 編碼〉，特別是 UTF-8，務必在做複寫前設好。

前置作業好了後，開始分別設定 master 與 slave。

Master

設定配置

編輯 /etc/mysql/mysql.conf.d/mysqld.conf，把一些項目改為如下：

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id                  = 1
log_bin                    = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 2592000
max_binlog_size            = 100M
# binlog_do_db              = include_database_name
binlog_ignore_db           = information_schema
binlog_ignore_db           = mysql
binlog_ignore_db           = performance_schema
binlog_ignore_db           = sys

必填的設定：

• server-id
  每台 MySQL 的唯一碼，不論 master 或 slave 皆不可重複，此處設為 1。
• log_bin
  Master 的 binlog 路徑。

這裡的 binlog / logbin 是 MySQL 的交易（transaction）紀錄，複寫機制就是利用傳輸 binlog 實現的，至於具體的結構格式、交握機制，對於只想順順用複寫、無心鑽研的你我來說，不是重點。

下面幾項依需求設定：

• binlog_expire_logs_seconds
  Binlog 檔案保留的秒數，超過即刪除，避免塞爆硬碟，預設值為 2,592,000 秒，相當於 30 天。（並非真的逐秒檢查，而是有資料異動時檢查）
• max_binlog_size
  每個 binlog 檔案的大小，超過就會產生下一個 binlog 檔案，預設值 100 MB。
• binlog_do_db
  要複寫的資料庫，如果有多個就建立多筆 binlog_do_db 的設定。
• binlog_ignore_db 不要複寫的資料庫，涵義與上面相反，在此我們忽略 MySQL 內建的四個系統資料庫。

可以看到我這邊是以黑名單原則設定，把四個系統資料庫設為黑名單，其餘都複寫。（此處的黑白與人種膚色無關，黃種人的命也是命。）

設完之後，照慣例，重啟 MySQL：

$ sudo systemctl restart mysql

看一下狀態：

$ systemctl status mysql

如果是綠色的就沒問題囉！

開立 slave 帳號

進 MySQL。

建帳號：

CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'replica-password';

開權限：

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

這裡給兩個權限：

• REPLICATION SLAVE
  讓 slave 可以讀到 master 的 binlog。
• REPLICATION CLIENT
  讓 slave 有執行 SHOW MASTER STATUS、SHOW REPLICA STATUS 和 SHOW BINARY LOGS這三個命令的權限，它們用於獲得 replication 機制當前運作狀態。

確認狀態

確認一下當前 master 狀態：

SHOW MASTER STATUS;

查詢結果：

+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 | 882      |              | information_schema,mysql,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+

可以看到，當前 binlog 檔案為 mysql-bin.000001、position 為 882。

前面我們把 binlog 路徑設在 /var/log/mysql/，實際去看確實也會有這個檔案。

Master 的部份至此告一段落。

Slave

設定配置

一樣先從 /etc/mysql/mysql.conf.d/mysqld.conf 開始，把一些項目改為如下：

# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir             = /tmp
replica_load_tmpdir = /var/tmp


# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id = 10
# log_bin  = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
max_binlog_size   = 100M
# binlog_do_db     = include_database_name
# binlog_ignore_db = include_database_name

read_only = 1
relay_log = XXXXX-relay-bin

必填的項目如下：

• server-id
  每台 MySQL 的唯一碼，不論 master 或 slave 皆不可重複，此處設為 10，表示１號 master 的０號 slave。
• read_only
  Slave 除了複寫外，不開放其他寫入。
• relay_log Slave 的 relay log 的名稱，該值前面的 XXXXX 為此台主機名稱（hostname），例如這台 hostname 為 awesome-db-replica，那此處依照慣例會填入 awesome-db-replica-relay-bin。

這個 relay log 是 slave 在複寫機制下必須要有的，它是 binlog 在 slave 這邊的紀錄，用於讓 slave 知道當前複寫的狀態，具體細節在此不深究，它的真身在 /var/lib/mysql/。

選填的項目：

• replica_load_tmpdir
  複寫的暫存目錄，根據註解內連結的建議，擺到不會被刪的位置較佳。

改完後一樣重啟服務：

$ sudo systemctl restart mysql

看一下狀態：

$ systemctl status mysql

如果是綠色的就沒問題囉！

啟動複寫

登入 slave MySQL，把前面建的帳密、binlog 資訊逐一填入：

STOP REPLICA;
 
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '200.0.0.228',
  SOURCE_USER = 'replica',
  SOURCE_PASSWORD = 'replica-password',
 
START REPLICA;

確認狀態：

SHOW REPLICA STATUS\G;

會有很多資訊：

***************************[ 1. row ]***************************
Slave_IO_State                | Waiting for source to send event
Master_Host                   | 200.0.0.228
Master_User                   | replica
Master_Port                   | 3306
Connect_Retry                 | 60
Master_Log_File               | mysql-bin.000003
Read_Master_Log_Pos           | 609
Relay_Log_File                | wms-db-replica-relay-bin.000004
Relay_Log_Pos                 | 825
Relay_Master_Log_File         | mysql-bin.000003
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   | 
Last_Errno                    | 0
Last_Error                    | 
Skip_Counter                  | 0
Exec_Master_Log_Pos           | 609
Relay_Log_Space               | 1742
Until_Condition               | None
Until_Log_File                | 
Until_Log_Pos                 | 0
Master_SSL_Allowed            | No
Master_SSL_CA_File            | 
Master_SSL_CA_Path            | 
Master_SSL_Cert               | 
Master_SSL_Cipher             | 
Master_SSL_Key                | 
Seconds_Behind_Master         | 0
Master_SSL_Verify_Server_Cert | No
Last_IO_Errno                 | 0
Last_IO_Error                 | 
Last_SQL_Errno                | 0
Last_SQL_Error                | 
Replicate_Ignore_Server_Ids   | 
Master_Server_Id              | 1
Master_UUID                   | 3d63c6ef-492f-11ed-802a-00155d731308
Master_Info_File              | mysql.slave_master_info
SQL_Delay                     | 0
SQL_Remaining_Delay           | <null>
Slave_SQL_Running_State       | Replica has read all relay log; waiting for more updates
Master_Retry_Count            | 86400
Master_Bind                   | 
Last_IO_Error_Timestamp       | 
Last_SQL_Error_Timestamp      | 
Master_SSL_Crl                | 
Master_SSL_Crlpath            | 
Retrieved_Gtid_Set            | 
Executed_Gtid_Set             | 
Auto_Position                 | 0
Replicate_Rewrite_DB          | 
Channel_Name                  | 
Master_TLS_Version            | 
Master_public_key_path        | 
Get_master_public_key         | 0
Network_Namespace             |

資訊量很大，主要看下面幾項：

• Slave_IO_Running
  沒問題應該要是 Yes。
• Slave_SQL_Running
  沒問題應該要是 Yes。
• Last_IO_Error
  如果有問題會在此顯示問題資訊。

檢測複寫機制

前面已經看過 slave 的狀態，沒問題的話，回到 master 看狀態：

SHOW REPLICAS;

結果如下：

+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Source_id | Replica_UUID                         |
+-----------+------+------+-----------+--------------------------------------+
| 10        |      | 3306 | 1         | 6607513e-4941-11ed-b88c-00155d731309 |
+-----------+------+------+-----------+--------------------------------------+

這台 server id 為 10 的就是我們剛建的 slave 啦！

在 master 建個資料庫試一下：

CREATE DATABASE replication_test;

在 slave 看看有沒有複寫過來：

SHOW DATABASES;

有複寫成功就 OK 囉！

Share