# 如何在 MySQL 5.5 設定單向資料庫複寫機制 (Replication)

最近重灌一台 [Ubuntu 12.04 LTS](http://www.ubuntu.com/download/server) 準備用來架設 MySQL 資料庫複寫機制 (Replication)，上網找了些如何設定的文章當參考，結果發現怎樣都設定不起來，查看了官網的文件 (不小心查到舊版文件)，結果也設定不起來，後來才發現原來是 MySQL 到 5.5 版之後，其資料庫複寫機制的設定方式變了，也因此安裝流程跟以往不一樣，所以必須調整原本寫好的標準作業流程(SOP)，也順便將設定的過程整理成文章。

**簡單描述 MySQL 複寫機制中的主要角色：**

-   **主要資料庫伺服器 ( Master )**

-   接收 AP 傳來的查詢指令 ( SELECT )
-   接收 AP 傳來的資料變更要求 (INSERT、UPDATE、DELETE)

-   **次要資料庫伺服器 ( Slave )**

-   負責接收 AP 傳來的查詢指令 ( SELECT )
-   負責取得 Master 資料庫伺服器上所有已設定複寫的資料庫內容更新  
    **注意**：在 MySQL 複寫機制中，是由 Slave 主動向 Master 要求資料庫的內容更新紀錄!
-   Slave 次要資料庫伺服器可以很多台，也就是可以複寫很多份 Master 資料的意思

**重點備註事項**

-   MySQL 複寫機制預設就是用非同步的方式進行複寫
-   MySQL 複寫機制是由 Slave 主動向 Master 要求資料庫的內容更新紀錄
-   Slave 不需要一直連接 Master 才能運作，即便長時間中斷連線都還是能回復資料庫同步的狀態

**使用 MySQL 資料庫複寫的主要用途：**

-   延展資料庫的能力 ( Scale-out solutions )

-   提供更大的要求承載量，也可設計用多台 MySQL 做負載平衡之用

-   資料安全性提升 ( Data security )

-   由於資料會複寫到 Slave 電腦，且 Slave 允許暫時中斷複寫作業，因此真正要做資料庫備份時可以完全在 Slave 完成，如此一來就不會導致備份過程中有資料表被鎖定或影響 Master 主要資料庫，如此一來可以減少 AP 讀寫 Master 資料庫時發生問題！

-   資料庫分析 ( Analytics )

-   由於 Master 資料庫主要負責資料的更新作業 (INSERT、UPDATE、DELETE)，因此在執行資料庫分析報表時可能需要鎖定資料庫的狀況，這類報表與分析用途的 AP 我們就可以改向 Slave 資料庫進行查詢，如此一來就不會影響 Master 資料庫頻繁的更新動作。

-   長距離的資料散佈 ( Long-distance data distribution )

-   若資料庫必須實作異地備份或是企業在世界各地有不同的辦公室，都可以透過 MySQL 資料庫複寫機制將資料傳送到各地，提升各地的資料庫讀寫效率，而且這些複寫的 Slave 伺服器不用一直與 Master 保持連線。

**簡介 MySQL 資料庫複寫的部署架構：**

-   單向資料庫複寫 ( One-way ) ( 又稱 Master-Slave 複寫模式 )

-   也就是主要由 Master 接收資料更新，並單純的將 Master 複寫資料到 Slave
-   這種模式一樣是由 Slave 主動向 Master 要求資料庫的內容更新紀錄
-   這種模式採非同步的方式更新資料

-   多向資料庫複寫 ( Multi-way )

-   此複寫機制與單向資料庫複寫 ( One-way ) 很像，只是設定多份 Slave 資料庫而已

-   還有更多元的部署架構，全看你怎樣規劃，詳細資訊請參考 [16.3. Replication Solutions](http://dev.mysql.com/doc/refman/5.0/en/replication-solutions.html)

     **備註**：如果要做到多台 MySQL 都要能同時接受寫入的話，必須選用 [MySQL Cluster](http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster.html) 叢集架構。

接著就要進入文章主題，也就是如何逐步設定 MySQL 單向資料庫複寫與其注意事項：

**設定之前的注意事項：**

-   每台 MySQL 資料庫實體 (Instance) 都要設定唯一的 **server-id**  
    **註**：所謂 **資料庫實體** 就是 **資料庫伺服器** 的意思，一台主機可以安裝好幾個**資料庫實體**，就好像一台主機同時可以安裝 MySQL 5.0 , MySQL 5.1 , MySQL 5.5 這樣的意思一樣。
-   必須在 Master 資料庫實體新增一組複寫專用的 MySQL 使用者 (給 Slave 抓取交易紀錄之用)

**以下是設定 MySQL 單向資料庫複寫的標準作業流程：**

**1\. 設定 Master 資料庫實體的 /etc/mysql/my.cnf 設定檔，在 \[mysqld\] 區段裡加上以下設定：**

```
[mysqld]
#bind-address           = 127.0.0.1server-id=1
log-bin=mysql-bin
```

   **備註 1**：在 Linux 平台預設 MySQL 只會繫結(Bind)本機網路介面，所以必須註解 **bind-address** 參數  
   **備註 2**：其中 **log-bin** 參數是宣告讓 MySQL 開始紀錄所有資料庫的**交易紀錄**

   如果在你的資料庫實體裡只想複寫部分資料庫的話，你可以多加上 **binlog-do-db** 參數，範例如下：

```
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db = maindbname1
binlog-do-db = maindbname2
```

   儲存設定之後，請重新啟動 Master 的  MySQL 伺服器：

```
/etc/init.d/mysql restart
```

   此時登入 mysql 執行以下 SQL 指令，可以查詢 MASTER 資料庫運作的狀態：

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/b8c08ed11ae4da15588c-image_1586.png)

**備註**：雖然上圖所示 MASTER 伺服器的 MySQL 版本為 5.1 版，但到了 5.5 版設定步驟並沒有改變。

**2\. 在 Master 建立給 Slave 連到 Master 進行複寫資料時的專用 MySQL 使用者帳號**

以下 3 行 SQL 指令在執行時，必須先替換成你想設定的帳號、密碼與 Slave 主機的 IP 地址：

-   **repluser**

-   請替換成 MySQL 複寫專用的使用者帳號名稱 (Username)

-   **10.0.0.52**

-   請替換成 Slave 主機的 IP 地址

-   **YOUR\_PASSWORD**

-   請替換成 MySQL 複寫專用的使用者帳號密碼 (Password)

```
CREATE USER 'repluser'@'10.0.0.52' IDENTIFIED BY 'YOUR_PASSWORD';GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'10.0.0.52';FLUSH PRIVILEGES;
```

**3\. 在 Master 建立一次完整備份(資料快照)，並將備份檔移至 Slave 主機**

以下 **mysqldump** 指令紅色部分是專門為了從 Master 輸出資料給 Slave 專用的，必須加上：

```
mysqldump -u root -p --master-data --all-databases > all_mysql_db.sql
```

**備註**：如果你不想複寫所有資料庫，也可以僅輸出部分資料庫即可，相關指令請參考 [4.5.4. mysqldump — A Database Backup Program](http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html)。

**4\. 將剛剛的備份檔 ( all\_mysql\_db.sql ) 還原到 Slave 資料庫伺服器**

請用以下指令匯入資料庫

```
mysql -u root -p --default-character-set=utf8 < all_mysql_db.sql
```

**5\. 設定 Slave 資料庫實體的 /etc/mysql/my.cnf 設定檔，在 \[mysqld\] 區段裡加上以下設定：**

```
[mysqld]
server-id=2
```

**備註 1**：在 MySQL 5.1 可以在設定檔定義 master-host, master-user, master-password, master-port, … 等參數，這些參數到了 MySQL 5.5 已經被移掉，如果設定上去會導致 MySQL 實體無法啟動。

**備註 2**：在 Slave 也可以設定 **log-bin** 參數，如此一來 Slave 也能當成 Master 提供資料複寫給其他台。

儲存設定之後，請重新啟動 Slave 的 MySQL 伺服器：

```
/etc/init.d/mysql restart
```

**注意**：此時 MySQL 資料庫複寫機制還沒有啟動。

**6\. 設定 Slave 資料庫實體，指定 Master 資料庫的登入資訊，並且正式啟動複寫機制**

登入 mysql 並執行以下 SQL 指令，執行之前必須先替換成你想設定的帳號、密碼與 **Master** 主機的 IP 地址：

-   **repluser**

-   請替換成 MySQL 複寫專用的使用者帳號名稱 (Username)

-   **10.0.0.51**

-   請替換成 Master 主機的 IP 地址

-   **YOUR\_PASSWORD**

-   請替換成 MySQL 複寫專用的使用者帳號密碼 (Password)

```
CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3306, MASTER_USER='repluser',  MASTER_PASSWORD='YOUR_PASSWORD';
```

緊接著執行以下 SQL 指令以啟動複寫機制運作：

```
START SLAVE;
```

**7\. 檢查 Slave 資料庫實體的複寫機制是否正常運作**

請再 Slave 主機的 mysql 執行以下 SQL 指令即可顯示出完整的資料複寫狀態：

```
SHOW SLAVE STATUS \G;
```

請注意：以下執行結果紅框處，如果有任何錯誤發生的話，那代表你 MySQL 資料庫複寫機制出現問題，如果不處理的話複寫機制將無法運作。

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/6c1d5c5fe689de05c1ce-image_1587.png)

如果沒有任何錯誤訊息，接著我們就能測試複寫機制是否正常運作，以下有兩種測試情境：

**a. 複寫所有資料庫**

如果你部署的 MySQL 複寫機制是針對「**所有**資料庫」進行複寫的話，那麼當你在 Master 資料庫實體**建立資料庫**時，基本上應該會**即時**在 Slave 資料庫實體看見。當然新增表格、資料也都會立刻反映到另一台 Slave 資料庫實體上。

**b. 複寫特定資料庫**

如果你部署的 MySQL 複寫機制是針對「**特定**資料庫」進行複寫的話，那麼只會有指定的資料庫內容才會複寫到另一台 Slave 資料庫實體上，此時你在這些啟用複寫的資料庫建立表格、新增刪除修改資料，會立刻反映到另一台 Slave 資料庫實體上。

**大功告成！ ^\_^**

**相關連結**

-   [**MySQL 5.5** Reference Manual :: **16 Replication**](http://dev.mysql.com/doc/refman/5.5/en/replication.html)

-   [16.1.1. How to Set Up Replication](http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html)
-   [16.1.1.8. Setting Up Replication with Existing Data](http://dev.mysql.com/doc/refman/5.5/en/replication-howto-existingdata.html)

-   [13.4. Replication Statements](http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-replication.html)

-   [13.4.1. SQL Statements for Controlling Master Servers](http://dev.mysql.com/doc/refman/5.5/en/replication-master-sql.html)
-   [13.4.2. SQL Statements for Controlling Slave Servers](http://dev.mysql.com/doc/refman/5.5/en/replication-slave-sql.html)

-   [Ubuntu Server Guide](https://help.ubuntu.com/12.04/serverguide/index.html) » [Databases](https://help.ubuntu.com/12.04/serverguide/databases.html) » [**MySQL**](https://help.ubuntu.com/12.04/serverguide/mysql.html)
-   [Setting Up Master-Master Replication On Four Nodes With MySQL 5 On Debian Etch](http://www.howtoforge.com/setting-up-master-master-replication-on-four-nodes-with-mysql-5-on-debian-etch)
-   [Setting up Master-Slave Replication with MySQL](https://www.ovaistariq.net/setting-up-master-slave-replication-with-mysql/)
-   Why MySQL says the server is not configured as a slave
-   [\[MySQL\] 實做 MySQL Master-Master Replication 同步](http://blog.wu-boy.com/2008/12/mysql-%E5%AF%A6%E5%81%9A-mysql-master-master-replication-%E5%90%8C%E6%AD%A5/)
-   [MySQL Master-Master Replication Manager(1) - 簡介](http://blog.longwin.com.tw/2008/10/mysql-master-replication-manager-mmm-intro-2008/)
-   [MySQL Master-Master Replication Manager(2) - 環境建置、架設](http://blog.longwin.com.tw/2008/10/mysql-master-replication-manager-mmm-build-2008/)
-   [MySQL Master-Master Replication Manager(完) - 問答](http://blog.longwin.com.tw/2008/10/mysql-master-replication-manager-mmm-q-and-a-2008/)
-   [MySQL 設定 Replication (Master - Slave)](http://blog.longwin.com.tw/2008/03/mysql_replication_master_slave_set_2008/)