學一點 mysql 雙機異地熱備份----快速理解mysql主從，主主備份原理及實踐

geeyeek發表於2016-02-24

雙機熱備的概念簡單說一下，就是要保持兩個資料庫的狀態自動同步。對任何一個資料庫的操作都自動應用到另外一個資料庫，始終保持兩個資料庫資料一致。這樣做的好處多。 1. 可以做災備，其中一個壞了可以切換到另一個。 2. 可以做負載均衡，可以將請求分攤到其中任何一臺上，提高網站吞吐量。對於異地熱備，尤其適合災備。廢話不多說了。我們直接進入主題。我們會主要介紹兩部分內容：

一， mysql 備份工作原理

二，備份實戰

我們開始。

我使用的是mysql 5.5.34，

一， mysql 備份工作原理

簡單的說就是把一個伺服器上執行過的sql語句在別的伺服器上也重複執行一遍，這樣只要兩個資料庫的初態是一樣的，那麼它們就能一直同步。

當然這種複製和重複都是mysql自動實現的，我們只需要配置即可。

我們進一步詳細介紹原理的細節，這有一張圖：

上圖中有兩個伺服器，演示了從一個主伺服器（master）把資料同步到從伺服器（slave）的過程。

這是一個主-從複製的例子。主-主互相複製只是把上面的例子反過來再做一遍。所以我們以這個例子介紹原理。

對於一個mysql伺服器，一般有兩個執行緒來負責複製和被複制。當開啟複製之後。

1. 作為主伺服器Master，會把自己的每一次改動都記錄到二進位制日誌 Binarylog 中。（從伺服器會負責來讀取這個log，然後在自己那裡再執行一遍。）

2. 作為從伺服器Slave，會用master上的賬號登陸到 master上，讀取master的Binarylog, 寫入到自己的中繼日誌 Relaylog，然後自己的sql執行緒會負責讀取這個中繼日誌，並執行一遍。到這裡主伺服器上的更改就同步到從伺服器上了。

在mysql上可以檢視當前伺服器的主，從狀態。其實就是當前伺服器的 Binary（作為主伺服器角色）狀態和位置。以及其RelayLog（作為從伺服器）的複製進度。

例如我們在主伺服器上檢視主狀態：

mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000014
        Position: 107
    Binlog_Do_DB: 
Binlog_Ignore_DB: mysql,information_schema,performance_schema,amh
1 row <span class="kwrd">in</span> set (0.00 sec)

稍微解釋一下這幾行的意思：

1. 第一行表明當前正在記錄的 binarylog檔名是： mysql-bin.000014.

我們可以在mysql資料目錄下，找到這個檔案：

2. 第二行， 107. 表示當前的檔案偏移量，就是寫入在mysql-bin.000014 檔案的記錄位置。

這兩點就構成了主伺服器的狀態。配置從伺服器的時候，需要用到這兩個值。告訴從伺服器從哪讀取主伺服器的資料。（從伺服器會登入之後，找到這個日誌檔案，並從這個偏移量之後開始複製。）

3. 第三行，和第四行，表示需要記錄的資料庫和需要忽略的資料庫。只有需要記錄的資料庫，其變化才會被寫入到mysql-bin.000014日誌檔案中。後面會再次介紹這兩個引數。

我們還可以在從伺服器上，檢視從伺服器的複製狀態。

<span class="lnum">   1:  </span>mysql> show slave status\G

   2:  *************************** 1. row ***************************

<span class="lnum">   3:  </span>               Slave_IO_State: Waiting <span class="kwrd">for</span> master to send <span class="kwrd">event</span>

   4:                    Master_Host: 198.**.***.***

<span class="lnum">   5:  </span>                  Master_User: r*******

   6:                    Master_Port: 3306

<span class="lnum">   7:  </span>                Connect_Retry: 60

   8:                Master_Log_File: mysql-bin.000014

<span class="lnum">   9:  </span>          Read_Master_Log_Pos: 107

  10:                 Relay_Log_File: mysqld-relay-bin.000013

<span class="lnum">  11:  </span>                Relay_Log_Pos: 253

  12:          Relay_Master_Log_File: mysql-bin.000014

<span class="lnum">  13:  </span>             Slave_IO_Running: Yes

  14:              Slave_SQL_Running: Yes

<span class="lnum">  15:  </span>              Replicate_Do_DB:

  16:            Replicate_Ignore_DB: mysql,information_schema,amh,performance_schema

<span class="lnum">  17:  </span>           Replicate_Do_Table:

  18:         Replicate_Ignore_Table:

<span class="lnum">  19:  </span>      Replicate_Wild_Do_Table:

  20:    Replicate_Wild_Ignore_Table:

<span class="lnum">  21:  </span>                   Last_Errno: 0

  22:                     Last_Error:

<span class="lnum">  23:  </span>                 Skip_Counter: 0

  24:            Exec_Master_Log_Pos: 107

<span class="lnum">  25:  </span>              Relay_Log_Space: 556

  26:                Until_Condition: None

<span class="lnum">  27:  </span>               Until_Log_File:

  28:                  Until_Log_Pos: 0

<span class="lnum">  29:  </span>           Master_SSL_Allowed: No

我們還是來重點解釋途中的紅圈的部分：

1. Master_host 指的是主伺服器的地址。

2. Master_user 指的是主伺服器上用來複制的使用者。從伺服器會用此賬號來登入主服務。進行復制。

3. Master_log_file 就是前面提到的，主伺服器上的日誌檔名.

4. Read_Master_log_pos 就是前面提到的主伺服器的日誌記錄位置，從伺服器根據這兩個條件來選擇複製的檔案和位置。

5. Slave_IO_Running: 指的就是從伺服器上負責讀取主伺服器的執行緒工作狀態。從伺服器用這個專門的執行緒連結到主伺服器上，並把日誌拷貝回來。

6. Slave_SQL_Running: 指的就是專門執行sql的執行緒。它負責把複製回來的Relaylog執行到自己的資料庫中。這兩個引數必須都為Yes 才表明複製在正常工作。

其他的引數之後再介紹。

二， mysql 雙機熱備實戰

瞭解了上面的原理之後，我們來實戰。這裡有兩個重點，要想同步資料庫狀態，需要相同的初態，然後配置同步才有意義。當然你可以不要初態，這是你的自由。我們這裡從頭開始配置一遍。

我們先以A伺服器為起點，配置它的資料庫同步到B。這就是主-從複製了。之後再反過來做一次，就可以互相備份了。

1，第一步，

在A上面建立專門用於備份的使用者：

grant replication slave on *.* to <span class="str"><a target=_blank target="_blank" href="mailto:'repl_user'@'192.***.***.***'" style="color: rgb(70, 70, 70); text-decoration: none;">'repl_user'</a></span>@<span class="str">'192.***.***.***'</span> identified by <span class="str">'hj34$%&mnkb'</span>;

上面把ip地址換成B機器的ip地址。只允許B登入。安全。

使用者名稱為： repl_user

密碼為： hj34$********nkb

這個等會在B上面要用。

2. 開啟主伺服器的 binarylog。

很多伺服器是預設開啟的，我們這裡檢查一下：

開啟 /etc/my.cnf

我來解釋一下紅框中的配置：

前面三行，你可能已經有了。

binlog-do-db 用來表示，只把哪些資料庫的改動記錄到binary日誌中。可以寫上關注hello資料庫。但是我把它註釋掉了。只是展示一下。可以寫多行，表示關注多個資料庫。

binlog-ignore-db 表示，需要忽略哪些資料庫。我這裡忽略了其他的4個資料庫。

後面兩個用於在雙主（多主迴圈）互相備份。因為每臺資料庫伺服器都可能在同一個表中插入資料，如果表有一個自動增長的主鍵，那麼就會在多伺服器上出現主鍵衝突。解決這個問題的辦法就是讓每個資料庫的自增主鍵不連續。上圖說是，我假設需要將來可能需要10臺伺服器做備份，所以auto-increment-increment 設為10. 而 auto-increment-offset=1 表示這臺伺服器的序號。從1開始，不超過auto-increment-increment。

這樣做之後，我在這臺伺服器上插入的第一個id就是 1，第二行的id就是 11了，而不是2.

（同理，在第二臺伺服器上插入的第一個id就是2，第二行就是12，這個後面再介紹）這樣就不會出現主鍵衝突了。後面我們會演示這個id的效果。

3. 獲取主伺服器狀態，和同步初態。

假設我現在有這些資料庫在A上面。

如果你是全新安裝的，那麼不需要同步初態，直接跳過這一步，到後面直接檢視主伺服器狀態。

這裡我們假設有一個 hello 資料庫作為初態。

先鎖定 hello資料庫：

FLUSH TABLES WITH READ LOCK;

然後匯出資料：

我這裡只需要匯出hello資料庫，如果你有多個資料庫作為初態的話，需要匯出所有這些資料庫：

然後檢視A伺服器的binary日誌位置：

記住這個檔名和位置，等會在從伺服器上會用到。

主伺服器已經做完了，可以解除鎖定了：

4. 設定從伺服器 B 需要複製的資料庫

開啟從伺服器 B 的 /etc/my.cnf 檔案：

解釋一下上面的內容。

server-id 必須保證每個伺服器不一樣。這可能和迴圈同步有關。防止進入死迴圈。

replicate-do-db 可以指定需要複製的資料庫，我這裡注掉了。演示一下。

replicate-ignore-db 複製時需要排除的資料庫，我使用了，這個。除開系統的幾個資料庫之外，所有的資料庫都複製。

relay_log 中繼日誌的名字。前面說到了，複製執行緒需要先把遠端的變化拷貝到這個中繼日誌中，在執行。

log-slave-updates 意思是，中繼日誌執行之後，這些變化是否需要計入自己的binarylog。當你的B伺服器需要作為另外一個伺服器的主伺服器的時候需要開啟。就是雙主互相備份，或者多主迴圈備份。我們這裡需要，所以開啟。

儲存，重啟mysql。

5. 匯入初態，開始同步。

把剛才從A伺服器上匯出的 hello.sql 匯入到 B的hello資料庫中，如果B現在沒有hello資料庫，請先建立一個，然後再匯入：

建立資料庫：

mysql> create database hello <span class="kwrd">default</span> charset utf8;

把hello.sql 上傳到B上，然後匯入：

如果你剛才匯出了多個資料庫，需要把他們都一一上傳匯入。

開啟同步, 在B伺服器上執行：

 CHANGE MASTER TO 
       MASTER_HOST=<span class="str">'192.***.***.***'</span>, 
       MASTER_USER=<span class="str">'repl_user'</span>, 
       MASTER_PASSWORD=<span class="str">'hj3****'</span>, 
       MASTER_LOG_FILE=<span class="str">'mysql-bin.000004'</span>, 
       MASTER_LOG_POS=7145;