MySQL主從複製架構搭建及讀寫分離測試

周旭龍的部落格發表於2014-12-01
MySql架構

一、業務發展驅動資料發展

隨著網站業務的不斷髮展，使用者量的不斷增加，資料量成倍地增長，資料庫的訪問量也呈線性地增長。特別是在使用者訪問高峰期間，併發訪問量突然增大，資料庫的負載壓力也會增大，如果架構方案不夠健壯，那麼資料庫伺服器很有可能在高併發訪問負載壓力下當機，造成資料訪問服務的失效，從而導致網站的業務中斷，給公司和使用者造成雙重損失。那麼，有木有一種方案能夠解決此問題，使得資料庫不再因為負載壓力過高而成為網站的瓶頸呢？答案肯定是有的。

目前，大部分的主流關係型資料庫都提供了主從熱備功能，通過配置兩臺（或多臺）資料庫的主從關係，可以將一臺資料庫伺服器的資料更新同步到另一臺伺服器上。網站可以利用資料庫的這一功能，實現資料庫的讀寫分離，從而改善資料庫的負載壓力。

master-slave

利用資料庫的讀寫分離，Web伺服器在寫資料的時候，訪問主資料庫（Master），主資料庫通過主從複製機制將資料更新同步到從資料庫（Slave），這樣當Web伺服器讀資料的時候，就可以通過從資料庫獲得資料。這一方案使得在大量讀操作的Web應用可以輕鬆地讀取資料，而主資料庫也只會承受少量的寫入操作，還可以實現資料熱備份，可謂是一舉兩得的方案。

二、MySQL資料複製原理

剛剛我們瞭解了關係型資料庫的讀寫分離能夠實現資料庫的主從架構，那麼主從架構中最重要的資料複製又是怎麼一回事呢？MySQL作為最流行的關係型資料庫之一，通過了解MySQL的資料複製流程，會使得我們對主從複製的認知會有一定的幫助。

mysql

從上圖來看，整體上有如下三個步湊：

（1）Master將改變記錄到二進位制日誌(binary log)中（這些記錄叫做二進位制日誌事件，binary log events）；

（2）Slave將Master的二進位制日誌事件(binary log events)拷貝到它的中繼日誌(relay log)；

PS：從圖中可以看出，Slave伺服器中有一個I/O執行緒(I/O Thread)在不停地監聽Master的二進位制日誌(Binary Log)是否有更新：如果沒有它會睡眠等待Master產生新的日誌事件；如果有新的日誌事件(Log Events)，則會將其拷貝至Slave伺服器中的中繼日誌(Relay Log)。

（3）Slave重做中繼日誌(Relay Log)中的事件，將Master上的改變反映到它自己的資料庫中。

PS：從圖中可以看出，Slave伺服器中有一個SQL執行緒(SQL Thread)從中繼日誌讀取事件，並重做其中的事件從而更新Slave的資料，使其與Master中的資料一致。只要該執行緒與I/O執行緒保持一致，中繼日誌通常會位於OS的快取中，所以中繼日誌的開銷很小。

三、MySQL主從複製實戰

3.1 實驗環境總覽與準備工作

（1）實驗環境

①伺服器環境：本次我們主要藉助VMware Workstation搭建一個三臺Windows Server 2003組成的MySQL伺服器叢集，其中一臺作為Master伺服器（IP：192.168.80.10），其餘兩臺均作為Slave伺服器（IP：192.168.80.11,192.168.80.12）。

②客戶機環境：本次我們在Windows 7宿主機（IP：192.168.80.1）編寫一個C#控制檯程式，對MySQL伺服器進行基本的CRUD訪問測試。

（2）準備工作

下載MySQL檔案：http://dev.mysql.com/downloads/mysql/5.5.html#downloads

這裡我們選擇5.5版本，為了節省時間，直接選擇了Archive免安裝版本。又由於虛擬機器中的Windows Server 2003是32位，所以選擇了32-bit的Archive版本進行使用。

下載完成後，將三個壓縮包分別拷貝至Master（IP：192.168.80.10）、Slave1（IP：192.168.80.11）及Slave2（IP：192.168.80.12）中。

3.2 配置MySQL主伺服器

（1）將MySQL檔案拷貝到Master伺服器，並解壓到一個指定資料夾。這裡我放在了：C:\MySQLServer\mysql-5.5.40-win32

（2）新建一個配置檔案，取名為：my-master.ini，新增以下內容：

 1 [client]
 2 port=3306
 3 default-character-set=utf8
 4 
 5 [mysqld]
 6 port=3306
 7 
 8 #character_set_server=utf8 一定要這樣寫;
 9 character_set_server=utf8
10 
11 #解壓目錄
12 basedir=C:\MySQLServer\mysql-5.5.40-win32
13 
14 #解壓目錄下data目錄,必須為data目錄
15 datadir=C:\MySQLServer\mysql-5.5.40-win32\data
16 
17 #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 這個有問題，在建立完新使用者登入時報錯
18 sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
19 
20 #主伺服器的配置
21 #01.開啟二進位制日誌
22 log-bin=master-bin
23 #02.使用二進位制日誌的索引檔案
24 log-bin-index=master.bin.index
25 #03.為伺服器新增唯一的編號
26 server-id=1

（3）將my-master.ini傳送到Master伺服器中mysql所在的資料夾中，並在命令列中將其註冊為Windows服務：（這裡要轉到mysql的bin資料夾中進行操作，因為沒有設定環境變數）

（4）啟動mysql服務，並設為自啟動型別；

（5）使用root賬號登陸mysql，建立一個具有複製許可權的使用者；（此時root是沒有密碼的，直接回車即可）

（6）在Slave1或Slave2上通過遠端登入Master上的mysql測試新建使用者是否可以登入；

3.3 配置MySQL從伺服器

（1）同Master伺服器，將MySQL檔案拷貝解壓到指定資料夾下；

（2）新建一個配置檔案，取名為：my-slave.ini，新增以下內容：

[client]
port=3306
default-character-set=utf8

[mysqld]
port=3306

#character_set_server=utf8 一定要這樣寫;
character_set_server=utf8

#解壓目錄
basedir=C:\MySQLServer\mysql-5.5.40-win32

#解壓目錄下data目錄,必須為data目錄
datadir=C:\MySQLServer\mysql-5.5.40-win32\data

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 這個有問題，在建立完新使用者登入時報錯
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#從伺服器的配置
#01.為伺服器新增唯一的編號
server-id=2
#02.開啟中繼日誌
relay-log=slave-relay-log-bin
#03.使用中繼日誌的索引檔案
relay-log-index=slave-relay-log-bin.index

PS：這裡server-id要確保唯一，我們這裡Master（192.168.80.10）的server-id=1，那麼Slave1（192.168.80.11）就設定其server-id=2，Slave2（192.168.80.12）則設定其server-id=3。

（3）將my-slave.ini傳送到Slave1和Slave2伺服器中mysql所在的資料夾中，並在命令列中將其註冊為Windows服務：（這裡要轉到mysql的bin資料夾中進行操作，因為沒有設定環境變數）

（4）分別啟動兩臺Slave的mysql服務，步湊同master所述；當然，也可以在cmd中輸入命令：net start MySQL

（5）分別使用兩臺Slave的root賬號登陸mysql，通過指定的語句配置主從關係設定；

（6） 為了方便後面的測試，這裡我們在Master上通過root進入mysql，建立一個測試用的資料庫和資料表；

（7）還要建立一個使用者，這個使用者具有對所有資料庫的增刪查改的許可權，以便用來進行測試；

3.4 編寫C#程式測試主從複製結構

（1）下載mysql for .net開發包，新增對mysql.data.dll的引用

（2）在控制檯程式中寫程式碼訪問Master伺服器，並檢視程式執行結果；

①資料庫連線部分：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="mysqlmaster" 
         connectionString="server=192.168.80.10;database=dbtest;uid=sa;password=123456"/>
    </connectionStrings>
</configuration>

②程式程式碼部分：在程式中首先顯示user表內容（這時表是空的），然後會新增5條user資訊，其中會修改第3條user資訊的name為Edison Chou，最後會刪除第5條user資訊；

static void Main(string[] args)
        {
            string connStr = ConfigurationManager.ConnectionStrings["mysqlmaster"]
                .ConnectionString;
            // 01.Query
            ShowUserData(connStr);
            // 02.Add a user to table
            for (int i = 0; i < 5; i++)
            {
                AddUserData(connStr, "TestUser" + (i + 1).ToString());
            }
            ShowUserData(connStr);
            // 03.Update a user on table
            UpdateUserData(connStr, 3, "EdisonChou");
            ShowUserData(connStr);
            // 04.Delete a user from table
            DeleteUserData(connStr, 5);
            ShowUserData(connStr);

            Console.ReadKey();
        }

        #region 01.Func:ShowUserData
        private static void ShowUserData(string connStr)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "select * from user";
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            Console.WriteLine("------------table:user------------");
                            while (reader.Read())
                            {
                                Console.WriteLine(reader[0] + "-" + reader[1]);
                            }
                            Console.WriteLine("------------table:user------------");
                        }
                    }
                }
            }
        }
        #endregion

        #region 02.Func:AddUserData
        private static void AddUserData(string connStr, string userName)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "insert into user(name) values('" + userName + "')";
                    int result = cmd.ExecuteNonQuery();
                    if (result > 0)
                    {
                        Console.WriteLine("Add User Successfully.");
                    }
                }
            }
        }
        #endregion

        #region 03.Func:UpdateUserData
        private static void UpdateUserData(string connStr, int userId,
    string userName)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "update user set name='" + userName
                        + "' where id=" + userId;
                    int result = cmd.ExecuteNonQuery();
                    if (result > 0)
                    {
                        Console.WriteLine("Update User Successfully.");
                    }
                }
            }
        }
        #endregion

        #region 04.Func:DeleteUserData
        private static void DeleteUserData(string connStr, int userId)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "delete from user where id=" + userId;
                    int result = cmd.ExecuteNonQuery();
                    if (result > 0)
                    {
                        Console.WriteLine("Delete User Successfully.");
                    }
                }
            }
        }
        #endregion

③程式執行結果：

（3）在Slave1（192.168.80.11）和Slave2（192.168.80.12）上檢視user表是否自動進行了資料同步；

①首先在Master上檢視user表還剩哪些資訊？

②其次在Slave1上檢視user表是否進行了同步：

③最後在Slave2上檢視user表是否進行了同步：

（4）初步嘗試讀寫分離：一主一從模式的一個最簡單的實現方式

①在Slave1上新建一個只具有讀（select）許可權的使用者，這裡取名為reader：

create user reader;

grant select on *.* to reader identified by ’123456′;

②新增一個mysqlslave的資料庫連線字串：

    <connectionStrings>
        <add name="mysqlmaster" 
             connectionString="server=192.168.80.10;database=dbtest;uid=sa;password=123456"/>
        <add name="mysqlslave"
             connectionString="server=192.168.80.11;database=dbtest;uid=reader;password=123456"/>
    </connectionStrings>

③新增一個列舉DbCommandType來記錄讀操作和寫操作：

    public enum DbCommandType
    {
        Read,
        Write
    }

④修改讀取資料表的程式碼判斷是讀操作還是寫操作：

private static void ShowUserData(DbCommandType commandType)
        {
            string connStr = null;
            if (commandType == DbCommandType.Write)
            {
                connStr = ConfigurationManager.ConnectionStrings["mysqlmaster"]
                    .ConnectionString;
            }
            else
            {
                connStr = ConfigurationManager.ConnectionStrings["mysqlslave"]
                    .ConnectionString;
            }

            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "select * from user";
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            Console.WriteLine("------------table:user------------");
                            while (reader.Read())
                            {
                                Console.WriteLine(reader[0] + "-" + reader[1]);
                            }
                            Console.WriteLine("------------table:user------------");
                        }
                    }
                }
            }
        }

PS：關於MySQL的讀寫分離實現，主要有以下幾種方式：

一種是基於MySQL-Proxy做排程伺服器模式，另一種是藉助阿里巴巴開源專案Amoeba(變形蟲)專案實現（這種方式貌似用的比較多），另外呢就是自己寫一個類似於雜湊演算法的程式庫來選擇目標資料庫；

學習小結

此次我們主要簡單地學習了主從複製的一些相關概念，瞭解了MySQL在Windows下搭建主從複製架構的過程，最後通過改變程式方式使得一主一從模式下實現讀寫分離（雖然是很簡單很粗陋的實現）。後續有空時，我會嘗試在Linux下藉助阿里巴巴開源專案Amoeba搭建真正的MySQL讀寫分離模式，到時也會將搭建的過程分享出來。雖然，我沒有相關的真實實踐經驗，也有很多人跟我說“你這是在紙上談兵”，我也知道“紙上得來終覺淺，絕知此事要躬行”，但在沒畢業之前，我還是會做一些相關的初步瞭解性質的實踐學習，也許以後到了公司，就會有真正的戰場在等著我了。

相關文章