一、業(yè)務(wù)發(fā)展驅(qū)動(dòng)數(shù)據(jù)發(fā)展
隨著網(wǎng)站業(yè)務(wù)的不斷發(fā)展，用戶量的不斷增加，數(shù)據(jù)量成倍地增長，數(shù)據(jù)庫的訪問量也呈線性地增長。特別是在用戶訪問高峰期間，并發(fā)訪問量突然增大，數(shù)據(jù)庫的負(fù)載壓力也會(huì)增大，如果架構(gòu)方案不夠健壯，那么數(shù)據(jù)庫服務(wù)器很有可能在高并發(fā)訪問負(fù)載壓力下宕機(jī)，造成數(shù)據(jù)訪問服務(wù)的失效，從而導(dǎo)致網(wǎng)站的業(yè)務(wù)中斷，給公司和用戶造成雙重?fù)p失。那么，有木有一種方案能夠解決此問題，使得數(shù)據(jù)庫不再因?yàn)樨?fù)載壓力過高而成為網(wǎng)站的瓶頸呢？答案肯定是有的。
目前，大部分的主流關(guān)系型數(shù)據(jù)庫都提供了主從熱備功能，通過配置兩臺(tái)（或多臺(tái)）數(shù)據(jù)庫的主從關(guān)系，可以將一臺(tái)數(shù)據(jù)庫服務(wù)器的數(shù)據(jù)更新同步到另一臺(tái)服務(wù)器上。網(wǎng)站可以利用數(shù)據(jù)庫的這一功能，實(shí)現(xiàn)數(shù)據(jù)庫的讀寫分離，從而改善數(shù)據(jù)庫的負(fù)載壓力。
利用數(shù)據(jù)庫的讀寫分離，Web服務(wù)器在寫數(shù)據(jù)的時(shí)候，訪問主數(shù)據(jù)庫（Master），主數(shù)據(jù)庫通過主從復(fù)制機(jī)制將數(shù)據(jù)更新同步到從數(shù)據(jù)庫（Slave），這樣當(dāng)Web服務(wù)器讀數(shù)據(jù)的時(shí)候，就可以通過從數(shù)據(jù)庫獲得數(shù)據(jù)。這一方案使得在大量讀操作的Web應(yīng)用可以輕松地讀取數(shù)據(jù)，而主數(shù)據(jù)庫也只會(huì)承受少量的寫入操作，還可以實(shí)現(xiàn)數(shù)據(jù)熱備份，可謂是一舉兩得的方案。
二、MySQL數(shù)據(jù)復(fù)制原理
剛剛我們了解了關(guān)系型數(shù)據(jù)庫的讀寫分離能夠?qū)崿F(xiàn)數(shù)據(jù)庫的主從架構(gòu)，那么主從架構(gòu)中最重要的數(shù)據(jù)復(fù)制又是怎么一回事呢？MySQL作為最流行的關(guān)系型數(shù)據(jù)庫之一，通過了解MySQL的數(shù)據(jù)復(fù)制流程，會(huì)使得我們對(duì)主從復(fù)制的認(rèn)知會(huì)有一定的幫助。
從上圖來看，整體上有如下三個(gè)步湊：
（1）Master將改變記錄到二進(jìn)制日志(binary log)中（這些記錄叫做二進(jìn)制日志事件，binary log events）；
（2）Slave將Master的二進(jìn)制日志事件(binary log events)拷貝到它的中繼日志(relay log)；
PS：從圖中可以看出，Slave服務(wù)器中有一個(gè)I/O線程(I/O Thread)在不停地監(jiān)聽Master的二進(jìn)制日志(Binary Log)是否有更新：如果沒有它會(huì)睡眠等待Master產(chǎn)生新的日志事件；如果有新的日志事件(Log Events)，則會(huì)將其拷貝至Slave服務(wù)器中的中繼日志(Relay Log)。
（3）Slave重做中繼日志(Relay Log)中的事件，將Master上的改變反映到它自己的數(shù)據(jù)庫中。
PS：從圖中可以看出，Slave服務(wù)器中有一個(gè)SQL線程(SQL Thread)從中繼日志讀取事件，并重做其中的事件從而更新Slave的數(shù)據(jù)，使其與Master中的數(shù)據(jù)一致。只要該線程與I/O線程保持一致，中繼日志通常會(huì)位于OS的緩存中，所以中繼日志的開銷很小。
三、MySQL主從復(fù)制實(shí)戰(zhàn)
3.1 實(shí)驗(yàn)環(huán)境總覽與準(zhǔn)備工作
（1）實(shí)驗(yàn)環(huán)境
①服務(wù)器環(huán)境：本次我們主要借助VMware Workstation搭建一個(gè)三臺(tái)Windows Server 2003組成的MySQL服務(wù)器集群，其中一臺(tái)作為Master服務(wù)器（IP：192.168.80.10），其余兩臺(tái)均作為Slave服務(wù)器（IP：192.168.80.11,192.168.80.12）。
②客戶機(jī)環(huán)境：本次我們?cè)赪indows 7宿主機(jī)（IP：192.168.80.1）編寫一個(gè)C#控制臺(tái)程序，對(duì)MySQL服務(wù)器進(jìn)行基本的CRUD訪問測(cè)試。
（2）準(zhǔn)備工作
下載MySQL文件：http://dev.mysql.com/downloads/mysql/5.5.html#downloads
這里我們選擇5.5版本，為了節(jié)省時(shí)間，直接選擇了Archive免安裝版本。又由于虛擬機(jī)中的Windows Server 2003是32位，所以選擇了32-bit的Archive版本進(jìn)行使用。
下載完成后，將三個(gè)壓縮包分別拷貝至Master（IP：192.168.80.10）、Slave1（IP：192.168.80.11）及Slave2（IP：192.168.80.12）中。
3.2 配置MySQL主服務(wù)器
（1）將MySQL文件拷貝到Master服務(wù)器，并解壓到一個(gè)指定文件夾。這里我放在了：C:\MySQLServer\mysql-5.5.40-win32
（2）新建一個(gè)配置文件，取名為：my-master.ini，添加以下內(nèi)容：
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 這個(gè)有問題，在創(chuàng)建完新用戶登錄時(shí)報(bào)錯(cuò) 18 sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 19 20 #主服務(wù)器的配置 21 #01.開啟二進(jìn)制日志 22 log-bin=master-bin 23 #02.使用二進(jìn)制日志的索引文件 24 log-bin-index=master.bin.index 25 #03.為服務(wù)器添加唯一的編號(hào) 26 server-id=1
（3）將my-master.ini傳送到Master服務(wù)器中mysql所在的文件夾中，并在命令行中將其注冊(cè)為Windows服務(wù)：（這里要轉(zhuǎn)到mysql的bin文件夾中進(jìn)行操作，因?yàn)闆]有設(shè)置環(huán)境變量）
（4）啟動(dòng)mysql服務(wù)，并設(shè)為自啟動(dòng)類型；
（5）使用root賬號(hào)登陸mysql，創(chuàng)建一個(gè)具有復(fù)制權(quán)限的用戶；（此時(shí)root是沒有密碼的，直接回車即可）
（6）在Slave1或Slave2上通過遠(yuǎn)程登錄Master上的mysql測(cè)試新建用戶是否可以登錄；
3.3 配置MySQL從服務(wù)器
（1）同Master服務(wù)器，將MySQL文件拷貝解壓到指定文件夾下；
（2）新建一個(gè)配置文件，取名為：my-slave.ini，添加以下內(nèi)容：
[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 這個(gè)有問題，在創(chuàng)建完新用戶登錄時(shí)報(bào)錯(cuò) sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #從服務(wù)器的配置 #01.為服務(wù)器添加唯一的編號(hào) 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）就設(shè)置其server-id=2，Slave2（192.168.80.12）則設(shè)置其server-id=3。
（3）將my-slave.ini傳送到Slave1和Slave2服務(wù)器中mysql所在的文件夾中，并在命令行中將其注冊(cè)為Windows服務(wù)：（這里要轉(zhuǎn)到mysql的bin文件夾中進(jìn)行操作，因?yàn)闆]有設(shè)置環(huán)境變量）
（4）分別啟動(dòng)兩臺(tái)Slave的mysql服務(wù)，步湊同master所述；當(dāng)然，也可以在cmd中輸入命令：net start MySQL
（5）分別使用兩臺(tái)Slave的root賬號(hào)登陸mysql，通過指定的語句配置主從關(guān)系設(shè)置；
（6） 為了方便后面的測(cè)試，這里我們?cè)贛aster上通過root進(jìn)入mysql，創(chuàng)建一個(gè)測(cè)試用的數(shù)據(jù)庫和數(shù)據(jù)表；
（7）還要?jiǎng)?chuàng)建一個(gè)用戶，這個(gè)用戶具有對(duì)所有數(shù)據(jù)庫的增刪查改的權(quán)限，以便用來進(jìn)行測(cè)試；
3.4 編寫C#程序測(cè)試主從復(fù)制結(jié)構(gòu)
（1）下載mysql for .net開發(fā)包，添加對(duì)mysql.data.dll的引用
（2）在控制臺(tái)程序中寫代碼訪問Master服務(wù)器，并查看程序運(yùn)行結(jié)果；
①數(shù)據(jù)庫連接部分：
<?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表內(nèi)容（這時(shí)表是空的），然后會(huì)添加5條user信息，其中會(huì)修改第3條user信息的name為Edison Chou，最后會(huì)刪除第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
③程序運(yùn)行結(jié)果：
（3）在Slave1（192.168.80.11）和Slave2（192.168.80.12）上查看user表是否自動(dòng)進(jìn)行了數(shù)據(jù)同步；
①首先在Master上查看user表還剩哪些信息？
②其次在Slave1上查看user表是否進(jìn)行了同步：
③最后在Slave2上查看user表是否進(jìn)行了同步：
（4）初步嘗試讀寫分離：一主一從模式的一個(gè)最簡單的實(shí)現(xiàn)方式
①在Slave1上新建一個(gè)只具有讀（select）權(quán)限的用戶，這里取名為reader：
create user reader;
grant select on *.* to reader identified by ’123456′;
②新增一個(gè)mysqlslave的數(shù)據(jù)庫連接字符串：
<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>
③新增一個(gè)枚舉DbCommandType來記錄讀操作和寫操作：
public enum DbCommandType
{
Read,
Write
}
④修改讀取數(shù)據(jù)表的代碼判斷是讀操作還是寫操作：
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：關(guān)于MySQL的讀寫分離實(shí)現(xiàn)，主要有以下幾種方式：
一種是基于MySQL-Proxy做調(diào)度服務(wù)器模式，另一種是借助阿里巴巴開源項(xiàng)目Amoeba(變形蟲)項(xiàng)目實(shí)現(xiàn)（這種方式貌似用的比較多），另外呢就是自己寫一個(gè)類似于哈希算法的程序庫來選擇目標(biāo)數(shù)據(jù)庫；
學(xué)習(xí)小結(jié)
此次我們主要簡單地學(xué)習(xí)了主從復(fù)制的一些相關(guān)概念，了解了MySQL在Windows下搭建主從復(fù)制架構(gòu)的過程，最后通過改變程序方式使得一主一從模式下實(shí)現(xiàn)讀寫分離（雖然是很簡單很粗陋的實(shí)現(xiàn)）。后續(xù)有空時(shí)，我會(huì)嘗試在Linux下借助阿里巴巴開源項(xiàng)目Amoeba搭建真正的MySQL讀寫分離模式，到時(shí)也會(huì)將搭建的過程分享出來。雖然，我沒有相關(guān)的真實(shí)實(shí)踐經(jīng)驗(yàn)，也有很多人跟我說“你這是在紙上談兵”，我也知道“紙上得來終覺淺，絕知此事要躬行”，但在沒畢業(yè)之前，我還是會(huì)做一些相關(guān)的初步了解性質(zhì)的實(shí)踐學(xué)習(xí)，也許以后到了公司，就會(huì)有真正的戰(zhàn)場(chǎng)在等著我了。
哈爾濱品用軟件有限公司致力于為哈爾濱的中小企業(yè)制作大氣、美觀的優(yōu)秀網(wǎng)站，并且能夠搭建符合百度排名規(guī)范的網(wǎng)站基底，使您的網(wǎng)站無需額外費(fèi)用，即可穩(wěn)步提升排名至首頁。歡迎體驗(yàn)最佳的哈爾濱網(wǎng)站建設(shè)。