MySQL主從復(fù)制讀寫分離

引言：在數(shù)據(jù)管理中，如果對(duì)MySQL數(shù)據(jù)庫(kù)的讀寫操作都在同一個(gè)數(shù)據(jù)庫(kù)服務(wù)器中的進(jìn)行，往往是無(wú)法滿足實(shí)際需要的。因?yàn)?，這樣做不僅容易造成數(shù)據(jù)的丟失和損壞，而且因?yàn)橥瑫r(shí)承擔(dān)讀寫操作，大大增加了數(shù)據(jù)庫(kù)服務(wù)器的負(fù)載，造成服務(wù)器運(yùn)行效率的降低。

在數(shù)據(jù)管理中，對(duì)MySQL數(shù)據(jù)庫(kù)的讀寫操作都在同一個(gè)數(shù)據(jù)庫(kù)服務(wù)器中進(jìn)行的話，往往無(wú)法滿足實(shí)際需要。

解決的方法：通過主從復(fù)制的方式同步數(shù)據(jù)，通過讀寫分離提高數(shù)據(jù)庫(kù)的并發(fā)負(fù)載能力。所謂主從復(fù)制指的是同時(shí)配置多臺(tái)MySQL數(shù)據(jù)庫(kù)服務(wù)器，讓某臺(tái)服務(wù)器作為MySQL主服務(wù)器，其余的作為從服務(wù)器，讓應(yīng)用程序服務(wù)器只對(duì)MySQL主服務(wù)器進(jìn)行寫操作，對(duì)從服務(wù)器只進(jìn)行讀操作。

MySQL主從復(fù)制原理

MySQL主服務(wù)器和從服務(wù)器之間通過數(shù)據(jù)同步的方式，使其存儲(chǔ)的數(shù)據(jù)彼此保持一致。因此說，主從復(fù)制是讀寫分離的前提條件。MySQL主從復(fù)制有三種實(shí)現(xiàn)方式，包括基于語(yǔ)句的復(fù)制，基于行的復(fù)制，基于類型的復(fù)制。對(duì)于基于語(yǔ)句的復(fù)制方式來(lái)說，當(dāng)在MySQL主服務(wù)器上執(zhí)行了修改數(shù)據(jù)庫(kù)的T-SQL語(yǔ)句后，再在MySQL從服務(wù)器上重復(fù)執(zhí)行相應(yīng)的語(yǔ)句即可，這是主從復(fù)制默認(rèn)的操作方式。其優(yōu)點(diǎn)是執(zhí)行效率高，但缺點(diǎn)是數(shù)據(jù)復(fù)制不是很精準(zhǔn)。對(duì)于基于行的復(fù)制方式來(lái)說，只需將MySQL主服務(wù)器中發(fā)生改動(dòng)的數(shù)據(jù)復(fù)制到MySQL從服務(wù)器中，其復(fù)制的不是具體的命令而是真實(shí)數(shù)據(jù)。優(yōu)點(diǎn)是精準(zhǔn)度高，缺點(diǎn)是效率很低。

對(duì)于混合類型的復(fù)制來(lái)說，在默認(rèn)情況下，優(yōu)先采用基于語(yǔ)句的復(fù)制，來(lái)提高復(fù)制效率。當(dāng)發(fā)現(xiàn)使用該方法無(wú)法提高復(fù)制的精準(zhǔn)度時(shí)，就會(huì)采用基于行的復(fù)制方法來(lái)處理剩余的數(shù)據(jù)。MySQL主從復(fù)制的原理是當(dāng)在執(zhí)行更新、插入、刪除等語(yǔ)句時(shí)，首先對(duì)MySQL主服務(wù)器進(jìn)行數(shù)據(jù)變更操作，將其寫入自己的二進(jìn)制日志中。MySQL從服務(wù)器利用I/O線程讀取MySQL主服務(wù)器中的日志。當(dāng)發(fā)現(xiàn)日志中出現(xiàn)的數(shù)據(jù)變動(dòng)信息，就會(huì)讓MySQL從服務(wù)器執(zhí)行復(fù)制動(dòng)作。

MySQL讀寫分離原理分析

讀寫分離的原理是只在MySQL主服務(wù)器上寫數(shù)據(jù)，只在MySQL從服務(wù)器上讀取數(shù)據(jù)，實(shí)現(xiàn)MySQL讀寫分離。一般來(lái)說有兩種方式：其一,是基于程序代碼來(lái)實(shí)現(xiàn)，因?yàn)橹鲝姆?wù)器的名稱和IP均不相同，因此在設(shè)計(jì)前端程序時(shí)，可以將所有的Select語(yǔ)句全部定位到MySQL從服務(wù)器上，而執(zhí)行Insert，Update等語(yǔ)句時(shí)，全部定位到MySQL主服務(wù)器上。其二,是配置中間代理層，即在MySQL服務(wù)器和客戶端之間設(shè)置代理服務(wù)器，客戶端將讀寫操作請(qǐng)求全部提交給代理服務(wù)器，由代理服務(wù)器來(lái)決定如何將寫操作轉(zhuǎn)交給MySQL主服務(wù)器，將讀操作轉(zhuǎn)交給MySQL從服務(wù)器。其優(yōu)點(diǎn)是可以優(yōu)化維護(hù)操作，缺點(diǎn)是需要購(gòu)買和配置代理服務(wù)器，讓網(wǎng)絡(luò)結(jié)構(gòu)變得復(fù)雜。這里就后一種方式，通過實(shí)例來(lái)說明如何實(shí)現(xiàn)MySQL的主從復(fù)制和讀寫分離功能。

配置實(shí)驗(yàn)網(wǎng)絡(luò)環(huán)境

在代理服務(wù)器上安裝Amoeba這款軟件，其IP為192.168.0.10。 一 臺(tái)MySQL服務(wù)器作為主服務(wù)器，其IP為 192.168.0.90。另外兩臺(tái)作為從服務(wù)器，其IP分別為192.168.0.91和192.168.0.92。 應(yīng)用客戶端主機(jī)的IP為192.168.1.100，這些主機(jī)安裝的都是CentOS6.5。為了便于管理，在MySQL主服務(wù)器上打開終端界面，執(zhí)行“hostname mysqlzl”命令，對(duì)其進(jìn)行更名操作。 執(zhí)行“VIM /etc/sysconfig/networkscripts/ifcfg-eth0”，將其中的“ONBOOT”的值修改為“yes”，將“BOOTPROTO”的值修改為“static”。添加“IPADDR=192.168.0.90”和“NETMASK =255.255.255.0”語(yǔ)句，之后保存該文件。

執(zhí)行“/etc/init.d/network resrart”命令，重啟網(wǎng)卡。執(zhí)行“bash”命令確認(rèn)，執(zhí)行“cd /etc/yum.repos.d”，“vim CentOSMedia.repo”命令，在該文件中只保留“baseurl=file:///media”，將無(wú) 關(guān)的 內(nèi) 容清除，將“ebable”的值設(shè)置為“1”。之后插入CentOS 6.5光盤，執(zhí)行“mount /dev/cdrom /media/”命令，配置好YUM源。之后在代理服務(wù)器和兩臺(tái)MySQL從服務(wù)器上分別執(zhí)行修改名稱（例如“proxysrv”，“mysqlc1”和“mysqlc2”），配 置 IP 和YUM源等操作。

安裝MySQL和所需的組件

之后需要在MySQL主服務(wù)器和從服務(wù)器上安裝MySQL數(shù)據(jù)庫(kù)組件和Cmake。首先，必須保證這些服務(wù)器之間時(shí)間保持同步，例如在mysqlzl上執(zhí)行“yun install ntp -y” 命令，在mysqlzl上執(zhí)行“vim/etc/init.d/ntp.conf” 命令，在該配置文件后面添加“server 127.127.1.0”和“fudge 127.127.1.0 stratum 8”語(yǔ)句，然后執(zhí)行“/etc/init.d/ntp”命令重啟該服務(wù)。在mysqlc1和mysqlc2服務(wù)器上分別執(zhí)行“yum install ntpdate -y”命令，安裝時(shí)間同步工具，執(zhí)行“/usr/sbin/ntpdate 192.168.0.90”命令，來(lái)同步mysqlzl服務(wù)器上的時(shí)間信息。

為了便于這些MySQL服務(wù)器之間順利通訊，可以 執(zhí) 行“iptables -F”命令，來(lái)清理防火墻規(guī)則。在MySQL主從服務(wù)器上分別 執(zhí) 行“yum install gcc gcc-c++ ncurres-devel make -y”，“tar zxvf cmake-x.x.x.tar.gz”，“./configure ; gmake ;gmake install”，來(lái)安裝對(duì)應(yīng)版本的Cmake組件。執(zhí)行“tar zxvf mysql-5.x.xx.tar.gz” 命 令，解壓對(duì)應(yīng)版本的MySQL安 裝 包，執(zhí)行“cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql&&make&&make install”命 令，對(duì) MySQL進(jìn)行編譯安裝，其中的“PREFIX=/usr/local/mysql”參數(shù)是指定其安裝位置。在“mysqlc1”和“mysqlc2”服務(wù)器上分別執(zhí)行“cd mysql-5.x.xx”命令，進(jìn)入解壓路徑。

執(zhí)行“cp supportfiles/my-medium.cnf /etc/my.cnf”，“cp supportfiles/mysql.server /etc/rc.d/init.d/mysqld”，復(fù)制所需的文件。執(zhí)行“chmod +x /etc/rc.d/init.d/mysqld”命令，來(lái)設(shè)置所需的可執(zhí)行權(quán)限。執(zhí)行“chkconfig -add mysqld”命令，將MySQL添加到系統(tǒng)服務(wù)中。執(zhí)行“echo"PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile”，“source /etc/profile”，添加所需環(huán)境變量并使其立即生效。執(zhí)行“groupadd mysql”，“useradd -M -s /sbin/nologin mysql”命令，創(chuàng)建名為Mysql的組，在其中添加名為mysql的賬戶。

執(zhí)行“chown -Rmysql:mysql /u s r/local/mysql/”命令，更改mysql用戶對(duì)指定目錄的權(quán) 限。 執(zhí) 行“/usr/local/mysql/scripts/mysql_install_db --user=mysql--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data” 命 令，對(duì)MySQL數(shù)據(jù)庫(kù)進(jìn)行優(yōu)化處理。執(zhí)行“/etc/init.d/mysqld start”命 令，啟動(dòng)Mysql。執(zhí)行“chkconfig mysql on”命令，使其自動(dòng)啟動(dòng)。為了安全起見，執(zhí)行“mysqladmin–u root password "p@ssw0rd"”，為SQL管理員設(shè)置密碼，密碼為“p@ssw0rd”，當(dāng)然，您可以根據(jù)實(shí)際情況更改。這樣，就完成了對(duì)MySQL從服務(wù)器的初始化配置。

配置MySQL主動(dòng)復(fù)制參數(shù)

在mysqlzl上同樣執(zhí)行以上命令，來(lái)實(shí)現(xiàn)初始化操作。注意，主從服務(wù)器上的MySQL管理員密碼需要設(shè)置為一致。執(zhí)行“vim /etc/ld.so.conf”命令，對(duì)該文件中的“server-id = 1”修改為“server-id = 11”，如果保持默認(rèn)值的話，該服務(wù)器就無(wú)法作為MySQL主服務(wù)器使用。將“l(fā)ogbin=mysql-bin”修 改 為“l(fā)og-bin=master-bin”，添加一行內(nèi)容“l(fā)og-slaveupdates=true”，允許從服務(wù)器從本機(jī)更新。保存該文件，執(zhí) 行“service mysqld restart”命令，重啟 MySQL服 務(wù)。 執(zhí) 行“mysql -u root -p”命令，輸入密碼后登錄到MySQL。在控制臺(tái)“mysql>”提示符下執(zhí)行“GRANT REPLICATION SLAVE ON *.* TO 'cong user'@'192.168.0.%'IDENTI FIEDBY'qwe123'”，為賬戶名為“conguser”的用戶設(shè)置“qwe123”的密碼，允許其訪問192.168.0網(wǎng)段的所有MySQL從服務(wù)器。

執(zhí)行“FLUSHPRIVILEGES”命令，執(zhí)行權(quán)限刷新操作。執(zhí)行“show master status” 命 令，記下返回信息中的“File”和“Position”的值，這其實(shí)是主MySQL的日志文件名稱和定位值。例如“masterbin.000001”和“397”。在兩臺(tái)MySQL從服務(wù)器上分別執(zhí)行“vim /etc/my.cnf”命令，對(duì)配置文件進(jìn)行修改，將其中的“server-id”的值修改為22。如果保持默認(rèn)為1，則無(wú)法將其作為從服務(wù)器使用。添加“relaylog=relay-log-bin” 和“relay-log-index=slaverelay-bin.index”兩行 語(yǔ)句，設(shè)置日志以及索引信息。執(zhí) 行“/etc/init.d/mysqld restart”命令，重啟 MySQL。

執(zhí) 行“mysql -u root-p”命令，輸入密碼后登錄到MySQL，在控制臺(tái)提示符下執(zhí)行“change master to master_host='192.168.0.90',master_user='conguser',master_password='qwe123',master_log_file='maste rbin.000001',master_log_pos=397;”命令，設(shè)置與其關(guān)聯(lián)的MySQL主服務(wù)器信息，注意，這里的各項(xiàng)參數(shù)需要和主服務(wù)器上的配置相同。例如賬戶名、密碼以及日志文件等。執(zhí)行“start slave;”，“show slave statusG;”命令，在返回信息中的“Slave_IO_Runnin”欄和“Slave_SQL_Running”欄中必須顯示“YES”，才表示配置成功。在MySQL主服務(wù)器上登錄到MySQL控制臺(tái)，執(zhí)行“create database dbceshi;”命令，創(chuàng)建一個(gè)用于測(cè)試的數(shù)據(jù)庫(kù)。

在某個(gè)MySQL從服務(wù)器中登錄到MySQL控制臺(tái)，執(zhí)行“show databases;”命令，可以看到上述數(shù)據(jù)庫(kù)，這就說明主從復(fù)制已經(jīng)配置完畢。

代理服務(wù)器上配置AMO EBA

接下來(lái)在代理服務(wù)器上配置Amoeba，因?yàn)锳moeba是使用JDK開發(fā)的，所以需要安裝JDK組件。注意，不需要使用最新的JDK。執(zhí)行“chmod +x /usr/local/jdk-6u14-linux-x64.bin”命令，為其配置可執(zhí)行權(quán)限。

執(zhí)行“./jdk-6u14-linux-x64.bin”命 令，安裝JDK組 件。 執(zhí) 行“mv jdk1.6_14/ /usr/local/jdk1.6”命 令，將JDK文件復(fù)制到指定的位置。執(zhí)行“vim /etc/profile” 命令，對(duì)其配置文件進(jìn)行修改，在其中的“export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL”一 行 后面 添 加“export JAVA_HOME=/usr/local/jdk1.6”，“ export CLASSPATH=$CLASSPATH:$JAVA_HOME/l i b:$JAVA_HOME/jre/lib”，“export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin”，“export AMOEBA_HOME=/usr/local/amoeba/”，“export PATH=$PATH:$AMOEBA_HOME/bin”等內(nèi)容，配置和JDK和AMOEBA相關(guān)的路徑信息。

執(zhí)行“source /etc/profile”語(yǔ)句，使上述配置生效。執(zhí)行“java -version”命令，來(lái)驗(yàn)證其版本。執(zhí)行“mkdir /usr/local/amoeba”，“tar zxvf /root/amoeba-mysql-binary-x.x.x.tar.gz -C /usr/local/amoeba”，“chmod-R 755 /usr/local/amoeba”，“/usr/local/amoeba/bin/amoeba”等語(yǔ)句，依次執(zhí)行創(chuàng)建對(duì)應(yīng)版本的AMOEBA運(yùn)行路徑，解壓AMOEBA壓縮包，為其設(shè)置可讀可寫可執(zhí)行的權(quán)限，配置AMOEBA等操作。

注意，這里的AMOEBA解壓路徑需要和上述配置文件中的內(nèi)容一致。在MySQL主從服務(wù)器上的“mysql>”控制臺(tái)下分別執(zhí)行“grant all on *.* to aduser@'192.168.0.%'identified by 'hello'”命令，創(chuàng)建名稱為“aduser”，密碼為“hello”的賬戶，使其擁有對(duì)192.168.0網(wǎng)段的訪問權(quán)限。

在代理服務(wù)器上進(jìn)入“/usr/local/amoeba/conf”目錄，執(zhí)行“vim amoeba.xml”命令。在編輯界面中找 到“”一行，在其下兩行的內(nèi)容修改為“amueba”和“qwe123”。

找到“”一行，將其下的“server1”修改為“master”， 將“server1”修改為“master”， 將“server1”修改為“slaves”，同時(shí)將其上方的注釋標(biāo)記刪除，使上述兩行生效。

保存該文件后，執(zhí)行“vimdb Servers.xml”命令，在編輯界面中找到“root”行，將其修改為“aduser”，將其下的一行修 改 為“hello”，并將該行的注釋標(biāo)記刪除。這兩行啟用了上述“aduser”賬戶，即讓AMOEBA使用該賬戶來(lái)連接MySQL數(shù)據(jù)庫(kù)。

將之后的“”修改為“”，在隨后的“127.0.0.1”修改為“192.168.0.90”，即設(shè)置MySQL主服務(wù)器的名稱和IP。

按照同樣的方法進(jìn)行同樣的修改時(shí)，不同的是修改的內(nèi)容為“”和“192.168.0.91”。 因?yàn)槟J(rèn)該文件中只提供了兩個(gè)“”的語(yǔ)句段，所以需要復(fù)制并添加一個(gè)新的語(yǔ)句段。其完整內(nèi)容包括“”，“”，“ < property name=ip Address>192.168.0.92 ”，“ ”，“”。這樣就將兩個(gè)從服務(wù)器信息添加進(jìn)來(lái)。

將其后的“”修改為“”，這和上述主配置保持一致。并需要將其后面的“ Server1，server2”修改為“mysqlc1，mysqlc2”，與上述兩個(gè)MySQL從服務(wù)器的名稱保持一致。然后保存該文件，完成AMOEBA的相關(guān)配置操作。

執(zhí)行“amoeba start &”命令，啟動(dòng)AMOEBA。執(zhí)行“netstat anpt | gerp jave”命令，在端口列表中顯示8066端口，說明AMOEBA配置沒有問題。

在客戶端執(zhí)行測(cè)試操作

因?yàn)樵贏MOEBA配置文件中設(shè)置的用戶名為“amoeba”，密碼為“qwe123”，所以為了實(shí)現(xiàn)讀寫分離，在客戶端程序中需要使用該賬戶和密碼，來(lái)操作后臺(tái)MySQL數(shù)據(jù)庫(kù)。

例如，在客戶端安裝好MySQL，配置正確的IP后，執(zhí) 行“mysql –u amoeba h 192.168.0.10 –P 8066-p”，使用預(yù)設(shè)的“amoeba”連接代理服務(wù)器，并指定連接端口，其中“-p”表示要輸入密碼。輸入上述密碼，連接成功后出現(xiàn)“mysql>”提示符，執(zhí)行“use dbceshi”命令。

使用上述用于測(cè)試的數(shù)據(jù)庫(kù)，再使用“create table”，“insert into”，“select”等指令，在該數(shù)據(jù)庫(kù)創(chuàng)建表并插入數(shù)據(jù)和查看數(shù)據(jù)。當(dāng)進(jìn)入MySQL主服務(wù)器或者從服務(wù)器中，執(zhí)行查看操作時(shí)，可看到上述表及數(shù)據(jù)已存在，說明主從復(fù)制和讀寫分離正常。