MySQL Master/Slave Server 架設

MySQL 提供 Master/Slave 機制讓您輕易的完成多個 MySQL Server 之間的資料同步，有了多個資料同步的 MySQL Server 在管理上會較有彈性，例如你可以建置備援主機或是進行負載平衡等等。但是要注意：一台 Master Server 可以擁有很多台 Slave Server；但一台 Slave Server 只可對應到一台 Master Server。

架設步驟：

    [事前準備]

       1. 確保 Master 與 Slave 之間的資料一致


    [Master Server 方面]

       1. 設定 Server-id
       2. 開啟 Binary Log
       3. 設定 Replication Slave 權限


    [Slave Server 方面]

       1. 設定 Server-id
       2. 將 Master_Host 設定為 Master Server
       3. 啟動與檢查 Slave Server 的狀態



在此範例中我們假設：

    [Master Server]

       1. IP 為 192.168.1.1
       2. server-id 為 1
       3. 進行資料同步時所使用的帳號資訊：
          帳號: slave_server
          密碼: 12345678


    [Slave Server]

       1. IP 為 192.168.1.2
       2. server-id 為 2



Master/Slave Server 架設

一、Master Server 方面


    A.設定 Server-id
    首先要設定 server-id。基本上沒有什麼特別的限制，只要 Master 和 Slave 的 server-id 不一樣即可，但其值必需為 1 至 2^32-1 之間。但為了方便識別，通常我們會把這個值設定為 IP 的最後一組數字，例如若 IP 為 192.168.1.1，則 server-id 設定為 1;若 IP 為 192.168.1.2，則 server-id 設定為 2。
    [mysqld]
    server-id=1


    B.開啟 Binary Log

        修改 MySQL Server 的系統設定檔，在 [mysqld] 下方加上 log-bin=mysql-bin，例如：

引用

        [mysqld]
        log-bin=mysql-bin

        MySQL 的 Binary Log 會將所有對於資料庫的修改操作全部記錄起來，而 Slave 與 Master 之間進行資料同步的方式很簡單，就是 Slave 會把 Master Server 的 Binary Log 拿過來執行，也就是說 Slave Server 會 "重做" 在 Master Server 上發生的各種修改操作。因此 Master Server 勿必要開啟 Binary Log 功能，否則 Master/Slave 架構無法運作。


    C.設定 Replication Slave 權限

        我們必須要在 Master Server 上做設定，讓 Slave 具有可以從 Master Server 上 Copy 資料的權限(正式的說法為 Replication Slave Priviledges)，所需使用的指令如下：

        GRANT REPLICATION SLAVE ON *.* TO 'slave_server'@'192.168.1.2',IDENTIFIED BY '12345678'

        意思為：

           1. 允許 192.168.1.2 這個 IP 使用 slave_server 帳號，來進行資料同步(Replication)。
           2. slave_server 這個帳號的密碼為 12345678。


        此時您可以從 Slave Server(192.168.1.2) 使用 mysql client program 進行驗證，看是否有正確的開啟權限，例如使用以下的指令：(注意，是在 Slave Server 上進行驗證)

引用

       
        mysql -h 192.168.1.1 -u slave_server -p

        接著系統會要求您輸入密碼，若可以順利登入即表示設定成功。



二、Slave Server 方面


    A.設定 Server-id

        在 Slave 我們將其設定為 2：

引用

        [mysqld]
        server-id=2

    B.將 Master_Host 設定為 Master Server

        我們必須要明確的告訴 Slave Server 哪一台 Server 才是 Master Server，使用以下的指令即可：
        引用:

        CHANGE MASTER TO MASTER_HOST='192.168.1.1',
        MASTER_PORT=3306, MASTER_USER='slave_server',
        MASTER_PASSWORD='12345678';

        意思為：

           1. Master Server 是 192.168.1.1
           2. 使用 TCP Port 3306 連接
           3. 使用 slave_server 這個帳號登入
           4. 登入時使用的密碼為 12345678


    C.啟動與檢查 Slave Server 的狀態

        設定好後，Master/Slave 機制仍未啟動，您必須要使用以下的指令來開啟或關閉 Master/Slave 機制：

           1. START slave; (啟動 Master/Slave 機制)
           2. STOP slave; (停止 Master/Slave 機制)


        當你執行 'START slave;' 後，可使用以下的指令來檢查執行狀態：
        引用:
        SHOW SLAVE STATUS \G
        執行後應該會看到如下的報表：

引用

        mysql> SHOW SLAVE STATUS \G
        *************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
        Master_Host: 192.168.1.1
        Master_User: slave_server
        Master_Port: 3306
        Connect_Retry: 60
        Master_Log_File: mysql-bin.000019
        Read_Master_Log_Pos: 16492717
        Relay_Log_File: www-relay-bin.000018
        Relay_Log_Pos: 16492854
        Relay_Master_Log_File: mysql-bin.000019
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
        Replicate_Do_DB: example
        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: 16492717
        Relay_Log_Space: 16492854
        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)

        重點是紅色那三行：

        Slave_IO_Running：
        是否要從 Master Server 複製 Binary Log 資料，必須為 Yes。

        Slave_SQL_Running：
        是否要執行從 Master Server 複製過來的 Binary Log 資料，必須為 Yes。

        Seconds_Behind_Master：
        Slave 的資料落後了 Master 多少秒，執行一段時間後應該會是零。



Master/Slave 的應用

vbulletin 原本即內建支援多台 MySQL Server 之間的負載平衡，只要你事先建立好 MySQL Master/Slave Server，並在 Slave Server 開放 Master Server 的權限，接下來只要在 vbb 的設定檔中做一些簡單的修改即可。

一、在 Master Server 方面


    在此我們假設 Master 使用以下的帳號資訊登入 slave：

        帳號: vbb_user
        密碼: 12345678

    修改 vbb 的系統設定檔：

引用

   
    $config['Database']['dbtype'] = 'mysql_slave';
    $config['SlaveServer']['servername'] = '192.168.1.2';
    $config['SlaveServer']['port'] = 3306;
    $config['SlaveServer']['username'] = 'vbb_user';
    $config['SlaveServer']['password'] = '12345678';

二、在 Slave Server 方面


    記得要開啟 Master 的權限：

    GRANT ALL ON example.* to 'vbb_user'@'192.168.1.1',
    IDENTIFIED BY '12345678';

    然後再使用以下的指令確認是否正常運作：

引用

    SHOW FULL PROCESSLIST;