字體:  

如何在 MySQL 5.5 設定MySQL Replication 同步複寫機制

dannyliao 發表於: 2016-8-08 10:25 來源: ADJ網路控股集團


MySQL 的 Master-Slave Replication (同步) 是當 Master 資料庫有變動時, 自動同步到 Slave 資料庫。Master-Slave 架構就是要讓 MySQL 資料庫系統有著備援的保障.

基本運作方式就是,MySQL Master 這台上只要有新增刪除修改,就會記錄在 binlog 檔裡,這時 Slave 就可以透過 Master 授權的帳號去同步資料(Replication),這是單向的。

重點備註事項

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

設定之前的注意事項:

(1)每台 MySQL 資料庫實體 (Instance) 都要設定唯一的 server-id
(2)必須在 Master 資料庫實體新增一組複寫專用的 MySQL 使用者 (給 Slave 抓取交易紀錄之用)

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

1. 設定 Master 資料庫實體的 /etc/my.cnf 設定檔,在 [mysqld] 區段裡加上以下設定,如果在你的資料庫實體裡只想複寫部分資料庫的話,你可以多加上 binlog-do-db 參數:

QUOTE:


[mysqld]
# Replication Setting
server-id=1
log-bin=mysql-bin
binlog-do-db=adj


儲存設定之後,請重新啟動 Master 的  MySQL 伺服器:
# service mysqld restart

2. 在 Master 建立給 Slave 連到 Master 進行複寫資料時的專用 MySQL 使用者帳號
以下 3 行 SQL 指令在執行時,必須先替換成你想設定的帳號、密碼與 Slave 主機的 IP 地址:

repluser
請替換成 MySQL 複寫專用的使用者帳號名稱 (Username)
10.0.0.52
請替換成 Slave 主機的 IP 地址
YOUR_PASSWORD
請替換成 MySQL 複寫專用的使用者帳號密碼 (Password)

QUOTE:


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

備註:如果你不想複寫所有資料庫,也可以僅輸出部分資料庫即可,如adj資料庫:
# mysqldump adj -u root -p --master-data  > adj.sql

4. 將剛剛的備份檔 ( adj.sql ) 還原到 Slave 資料庫伺服器
請用以下指令匯入資料庫

# mysql -u root -p --default-character-set=utf8 < adj.sql

5. 設定 Slave 資料庫實體的 /etc/my.cnf 設定檔,在 [mysqld] 區段裡加上以下設定:

QUOTE:


[mysqld]
# Replication Setting
server-id=2
replicate-do-db=adj
relay_log = relay-bin.log


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

# service mysqld restart

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

6. 設定 Slave 資料庫實體,指定 Master 資料庫的登入資訊,並且正式啟動複寫機制
登入 mysql 並執行以下 SQL 指令,執行之前必須先替換成你想設定的帳號、密碼與 Master 主機的 IP 地址:

QUOTE:


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 指令即可顯示出完整的資料複寫狀態:

QUOTE:


SHOW SLAVE STATUS \G;


最後..如果每次要啟用或者查狀態都需要登入 mysql 下指令覺得太麻煩..也可以直接在Slave的主機直接下指令,當然前提也需要在 slave主機建個 repluser的帳號,密碼我先隨便打:

QUOTE:


*** MySQL 同步指令 ***
# mysql -u repluser -p'123456' -e"CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3306, MASTER_USER='repluser',MASTER_PASSWORD='YOUR_PASSWORD';"

*** 看 MySQL Slave 狀態 ***
# mysql -u repluser -p'123456' -e"show slave status \G;"

*** 啟動關閉同步 ***
# mysql -u repluser -p'123456' -e"STOP SLAVE;"
# mysql -u repluser -p'123456' -e"START SLAVE;"

*** 刪除 Slave 同步設定 ***
# mysql -u repluser -p'123456' -e"reset slave all;"



大功告成! ^_^

參考資料:
http://blog.miniasp.com/post/2012/07/04/How-to-setup-MySQL-55-One-way-replication-Master-Slave-mode.aspx