MySQL Replication

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

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

在 Master DB 執行下列 SQL statements:

Callstack:
    at User:Alang/Linux_&_UNIX_工作筆記/MySQL_DBA_筆記/MySQL_Replication

MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException: reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown.
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptVar expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptVar.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Evaluate (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state, Boolean evaluateProperties) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptAccess.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptCall expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptCall.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptSequence expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptSequence.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptReturnScope expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 

你可以對每一個 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 - 2³² 。

請關閉 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

設定 Slave DB

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

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

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

shell> service mysqld start

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

取得 Master DB 抄寫點資訊

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

執行下述 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。

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

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

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

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

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

在 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;

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