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

取得 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;

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