The Will Will Web

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

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

最近重灌一台 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 資料庫實體上。

大功告成! ^_^

 

相關連結