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
    
    #設定二進位日誌檔要記錄的資料庫名稱(多個資料庫,則重覆此項設定)
    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
  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;


其他:
  • 多台 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... 設定



參考:

沒有留言:

張貼留言