已登入: Logged in as: 登入
搜尋:
Page last modified 09:48, 7 Dec 2016 by alang

MySQL Replication

    版本為 21:37, 20 Nov 2024

    到這個版本。

    返回到 版本存檔.

    查閱目前版本

    文/A.Lang 於 2009/2/28 (alang<dot>hsu<at>gmail<dot>com) *轉載請註明原作者*

    MySQL Replication - MySQL 資料庫抄寫。

    為何要作Replication(抄寫)

    • 報表(Reporting) - 對於需產出大量報表的資料庫而言，為不影響 Master DB 的效能，建議另外建置一個專屬的報表資料庫。
    • 資料安全 - 資料庫抄寫至 Slave DB，必要時可隨時關閉抄寫程序。對 Slave DB 作資料庫備份也可避免對 Master DB 的線上運作影響。
    • 異地備份 - 對於分公司資料庫的複製或異地備份需求。

    不同版本間抄寫的相容性

    官方手冊 - http://dev.mysql.com/doc/refman/5.0/...atibility.html

    基本上不同版本之間的資料庫抄寫，建議是從舊版本抄寫至下一個新版本，例如你可以從 MySQL 4.1.x 抄寫至 MySQL 5.0.x，或從 MySQL 5.0.x 抄寫至 MySQL 5.1.x，不過為求系統穩定性，還是建議盡量以同個版本來建置兩個資料庫間的抄寫。

    系統環境

    Master DB)
    Purpose:     Production
    IP address: 10.10.10.112
    DB version: mysql-server-5.0.22-2.2.el5_1.1

    Slave DB)
    Purpose:     Backup or Reporting
    IP address: 10.10.10.134
    DB version: mysql-server-5.0.45-7.el5

    如何建置兩個資料庫的抄寫

    新增一個資料庫用戶用以執行抄寫

    這個用戶是在 Master DB，用來讓 Slave DB 連接用，其權限僅需要開啟 REPLICATION SLAVE，例如新增一個用戶為 repl，密碼為 replpass。

    在 Master DB 執行下列 SQL statements:

    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)

    你可以對每一個 Slave DB 設定不同連接帳號，也可以用同一個帳號。

    Tip:
    上述的新增帳戶方式是以 Slave DB 的 IP 來限制連接的來源，如果實際的操作環境有使用 domain 也可以改成 domain name 方式來作，詳細方法請參考官方的線上手冊。
    要驗證帳號是否可以運作，可以在 Slave DB 嘗試使用該帳號連接，請在 Slave DB 執行下述指令
    shell> mysql -u repl --password='replpass' -h 10.10.10.112

    設定 Master DB

    要作抄寫的 Master DB 必須開啟 binary logging，並且在每一個 DB 主機上，需設定不同的 server-id 值，這個值是用來辨識群組內的不同主機識別用，其值是介於 1 - 232 。

    請關閉 MySQL 並修改 /etc/my.cnf 內容如下：

    shell> service mysqld stop
    shell> vi /etc/my.cnf

    [mysqld]
# Replication on Master DB
log-bin=mysql-bin
server-id=1

    shell> service mysqld start

    取得 Master DB 抄寫點資訊

    為了要使 Slave DB 作抄寫，必須先在 Master DB 上確定目前的資料點資訊，以使 Slave DB 可以從目前的資料點開始作資料抄寫，如此可確保兩端的資料是同步的。
    要取得 Master DB 目前資料點，必須先鎖住要作作抄寫資料庫的所有 tables 的寫入操作。

    在 Master DB 執行下述 SQL statements:

    mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     5752 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

    請記住這兩項資訊，後續設定 Slave DB 時會用到：
    File         - binary log 檔名
    Position   - 資料點位置

    注意：
    對於 InnoDB tables，當執行 FLUSH TABLES WITH READ LOCK 時會鎖定所有 table 的 COMMIT 操作。
    如果未開啟 binary logging，這兩欄位 File & Position 則為顯示空白及4。 

    設定 Slave DB編輯區段

    關閉 MySQL 並修改 /etc/my.cnf 內容如下：

    shell> service mysqld stop
    shell> vi /etc/my.cnf

    [mysqld]
# Replication on Slave DB
server-id=2

    Tips:
    如果有多部 Slave DB，每一個 DB 必須設不同的 server-id 值，這就類似主機 IP 位址一樣，如果DB未指定 server-id 時，系統預設為 0。
    在 Slave DB 不需要開啟 binary logging，不過，如果有開啟此參數時，可以作資料庫備份以及資料庫異常的回復作業，同時對其他不同的 Slave DB，可以切換為 Master DB。

    現有資料庫從 Master 至 Slave 的複製

    如果 Master DB 已存有資料，請在鎖住 tables 後繼續使用下述方法將資料庫複製到 Slave DB。
    停止 MySQL 然後 tar 資料庫目錄，在 Master DB 執行下述：

    shell> service mysqld stop
shell> cd /var/lib/mysql
shell> tar -czf ~/my_db.tar.gz <my_db_name>
shell> service mysqld start

shell> mysql -u root -p
mysql> UNLOCK TABLES;

    先關閉 Slave DB 的 MySQL 將上述的 tar 檔複製到 Slave DB 後解壓縮，最後重新以 --skip-slave 啟動 MySQL，在 Slave DB 執行下述：

    shell> service mysqld stop
shell> cd /var/lib/mysql
shell> tar -xzf ~/my_db.tar.gz
shell> mysqld_safe --skip-slave &

    記得，資料庫完成複製後，就可以恢復 Master DB 的資料庫存取操作，在 Master DB 使用下述指令，解除資料表鎖定：

    mysql> UNLOCK TABLES;

    在 Slave DB 設定 Master 連接參數

    在 Slave DB 執行下述 statements:

    mysql> CHANGE MASTER TO
    ->    MASTER_HOST='10.10.10.112',
    ->    MASTER_USER='repl',
    ->    MASTER_PASSWORD='replpass',
    ->    MASTER_LOG_FILE='mysql-bin.000001',
    ->    MASTER_LOG_POS=5752;
mysql> START SLAVE;

    以上請輸入來自 Master DB 的抄寫點資料。

    到這裡，Slave DB 已經完成與 Master DB 的抄寫設置，第一次作抄寫時，你可以發現在 Slave DB 的 MySQL 資料庫目錄 /var/lib/mysql，有兩個檔案 master.info 及 relay-log.info，這兩個紀錄有關與 Master DB 連接的資訊。

    如何檢查資料庫抄寫狀態

    官方手冊：

     

    在 Slave DB 執行下述 statement:

    mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.10.10.112
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000002
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000003
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000002
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

    \G - 這參數可以調整為較友善的顯示方式。

    在 Master DB 檢查 Slave DB 狀態，執行下述 statement，搜尋有無 Binlog Dump。

    mysql> show processlist \G;
*************************** 2. row ***************************
     Id: 3
   User: repl
   Host: 10.10.10.134:2065
     db: NULL
Command: Binlog Dump
   Time: 1381
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
2 rows in set (0.01 sec)
    Powered by MindTouch Core