The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

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

📅 2012/07/04 19:43 📁 MySQL, 系統管理

最近重灌一台 Ubuntu 12.04 LTS 準備用來架設 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

     備註：如果要做到多台 MySQL 都要能同時接受寫入的話，必須選用 MySQL Cluster 叢集架構。

 

接著就要進入文章主題，也就是如何逐步設定 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.1
server-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 資料庫運作的狀態：

備註：雖然上圖所示 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。

 

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 資料庫複寫機制出現問題，如果不處理的話複寫機制將無法運作。

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

a. 複寫所有資料庫

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

b. 複寫特定資料庫

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

大功告成！ ^_^

 

相關連結

留言評論