2014年10月12日 星期日

MySQL Replication

將一台 MySQL Server (Master) 的資料同步到另一台 MySQL Server (Slave)。

[ Master 設定 ]
  1. 修改 MySQL 設定檔，增加 replication 設定，log-bin、server-id、binlog-do-db、sync_binlog、innodb_flush_log_at_trx_commit，設定說明如下，修改後，重新啟動 MySQL。
    [mysqld]
#設定二進位日誌檔的檔名
log-bin = m-bin.log
#server 的 ID，設定一個唯一的數字
server-id = 1
#只保留幾天內的 binary log
expire_logs_days = 30

#設定二進位日誌檔要記錄的資料庫名稱(多個資料庫，則重覆此項設定)
binlog-do-db = test
#設定二進位日誌檔要忽略記錄的資料庫名稱
#binlog-ignore-db = test

#sync_binlog,設定大於等於0的數字,控制每幾次將binary log寫入硬碟,0:系統自己控制,1:最安全但效能較低
sync_binlog = 1
#innodb_flush_log_at_trx_commit,可設定0、1、2,
#0:每秒將 log buffer 寫入 log file,並flush to disk,但在 transaction commit 時不做任何事.
#1:預設值,transaction commit 時將 log buffer 寫入 log file，,並flush to disk.
#2:transaction commit 時將 log buffer 寫入 log file,不過flush to disk是每秒執行一次.
innodb_flush_log_at_trx_commit=1
  2. 在 Master 上，新增一個 Slave 端專用的帳號(例如 rep_user@192.168.0.125，密碼 abcd)，給予 REPLICATION SLAVE 的權限。
    mysql> GRANT REPLICATION SLAVE ON *.* TO rep_user@192.168.0.125 IDENTIFIED BY 'abcd';
mysql> FLUSH PRIVIlEGES;
  3. 刷新所有資料表，並加上 READ LOCK(所有連線可以讀取但不能寫入)，並免設定過程中，資料表資料有異動。
    mysql> FLUSH TABLES WITH READ LOCK;
  4. 查詢 Master 目前二進制日誌的資料。
    mysql> SHOW MASTER STATUS;
+--------------+----------+--------------+------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| m-bin.000002 |   612719 |              |                  |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
    然後將目前 File 的名稱(例如：m-bin.000002 )，和計錄檔的位置 Position(例如：612719)計錄下來。這之後要設定在 Slave 端，Slave 將從這個位置開始進行資料同步。
  5. 在 Master 端將要同步的資料庫匯出。
  6. 解除 Master 的 READ LOCK
    mysql> unlock tables;

[ Slave 設定 ]
  1. 將 Master 匯出的資料匯入 Slave。讓 Slave 資料表初始資料跟 Master 資料一樣。
  2. 修改 Slave 端 MySQL 設定檔，增加 replication 設定。修改後，重新啟動 MySQL。
    #設定 server-id，跟 Master 不重覆的數字。
server-id = 2
#設定中繼檔檔名
relay_log = relay-bin.log
#設定忽略同步的資料表(不一定一開始就要新增，運行過一段時間，之後要排除繼續同步某資料表，亦可隨時增加後重啟服務)
replicate-ignore-table = test.tt
  3. 停止 slave
    mysql> stop slave;
  4. 設定要同步 Master 相關資料
    mysql> CHANGE MASTER TO
MASTER_HOST='192.168.0.101',
MASTER_USER='rep_user',
MASTER_PASSWORD='abcd',
MASTER_LOG_FILE='m-bin.000002',
MASTER_LOG_POS=612719;
  5. 啟動 slave
    mysql> start slave;
  6. 查看 Slave 狀態，看設定是否正確，是否正常執行(Slave_IO_State、Slave_IO_Running、Slave_SQL_Running、Slave_SQL_Running_State)
    mysql> SHOW SLAVE STATUS;


[ log-bin(binary logging)、relay_log ]
  • Master 的 log-bin 預設會一直保留，可設定 expire_logs_days 參數指定保留幾天內的資料。
    當硬碟分割區容量用完，連接資料庫時，出現的錯誤訊息可能會是「Too many connections」。
  • Slave 的 relay_log 預設內容執行完，之後就會自動刪除。
  • 查看 binary log(log-bin) 的檔案列表、大小
    > SHOW BINARY LOGS;
  • 使用 PURGE 語法刪除 binary log (BINARY和MASTER同義)
    PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
  • 使用 PURGE 刪除 binary log(log-bin) 前，先查看 slave 已同步到哪個檔案(位置)，避免刪除到尚未同步的部分。
    (若 slave 正在讀取刪除範圍內的 binary log 進行同步，執行刪除語法不會刪除正在使用的log或該log之後的文件。但若 slave 處於沒連接 master 的狀態，則尚未同步的  binary log 依然會被刪除，為了避免此情況，所以刪除前，須進行確認。)
    > SHOW SLAVE STATUS\G;
  • 刪除 m-bin.000005 編號之前的 binary log
    > PURGE BINARY LOGS TO 'm-bin.000005';
  • 刪除 2014-09-18 06:30:00 之前的 binary log
    > PURGE BINARY LOGS BEFORE '2014-09-18 06:30:00';
  • 關閉並重新打開 binary log(log-bin) 檔案，會產生下一個編號的 binary log(log-bin) 檔案
    > FLUSH BINARY LOGS


其他：
  • 多台 MySQL Server 直線式同步：
    若有三台 Server，要做成直線式的同步，A->B->C，A 是 B 的 Master，B 是 C 的 Master，
    B 除了同時要有 Master、Slave 的設定外，還要在設定檔中新增 log_slave_updates=1 的設定。
    因為預設， Slave 從 Master 取得資料更新後，不會將異動記綠寫到自己的 log-bin 記錄檔中，須開啟 log_slave_updates，才會將相關異動寫入 log-bin，如此 C 才能從 B 的 log-bin 讀取到異動資料。
  • 兩台 MySQL Server 互為主從同步：
    兩台 MySQL Server 可以同時當對方的 Master、Slave，如果其中一台的資料異動了，就會同步到另一台。(似乎可用來備援切換時，資料可自動同步，但如果同時運作，可能就會有問題了。)
  • 停止 Slave 
    STOP SLAVE IO_THREAD;--停止從 Master 的binary log讀取資料到 Slave 的relay log
STOP SLAVE SQL_THREAD;--停止從relay log執行異動資料
START SLAVE;--同時 STOP SLAVE IO_THREAD 與 STOP SLAVE SQL_THREAD
  • 刪除 Slave 同步設定
    reset slave all;
  • 可查看 data/hostname.err 錯誤訊息，進行除錯。
  • Master 重新啟動後，當 m-bin.000002 依序變成 m-bin.000003，slave 的 MASTER_LOG_FILE 也會自動變成  m-bin.000003
  • Master 的 MySQL 版本 5.6，Slave 的 MySQL 版本 5.5，出現以錯誤時：
    Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'm-bin.000003' at 120, the last event read from '.\m-bin.000003' at 120, the last byte read from '.\m-bin.000003' at 120.'
    (參考 Disabling Binlog_checksum for MySQL 5.5/5.6 Master-master Replication)
    修正方式，Master 的 my.cnf 設定檔增加 binlog_checksum=NONE，重新取得 Master 的 Position、File 資訊，重新設定 Salve 的 CHANGE MASTER TO... 設定
  • 略過一個同步錯誤(同步時發生錯誤，無法繼續同步，若錯誤確定是可略過的，可設定略過)
    mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;




參考：

張貼者： 於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)