MySQL主從復制讀寫分離

引言：在數據管理中，如果對MySQL數據庫的讀寫操作都在同一個數據庫服務器中的進行，往往是無法滿足實際需要的。因為，這樣做不僅容易造成數據的丟失和損壞，而且因為同時承擔讀寫操作，大大增加了數據庫服務器的負載，造成服務器運行效率的降低。

在數據管理中，對MySQL數據庫的讀寫操作都在同一個數據庫服務器中進行的話，往往無法滿足實際需要。

解決的方法：通過主從復制的方式同步數據，通過讀寫分離提高數據庫的并發負載能力。所謂主從復制指的是同時配置多臺MySQL數據庫服務器，讓某臺服務器作為MySQL主服務器，其余的作為從服務器，讓應用程序服務器只對MySQL主服務器進行寫操作，對從服務器只進行讀操作。

MySQL主從復制原理

MySQL主服務器和從服務器之間通過數據同步的方式，使其存儲的數據彼此保持一致。因此說，主從復制是讀寫分離的前提條件。MySQL主從復制有三種實現方式，包括基于語句的復制，基于行的復制，基于類型的復制。對于基于語句的復制方式來說，當在MySQL主服務器上執行了修改數據庫的T-SQL語句后，再在MySQL從服務器上重復執行相應的語句即可，這是主從復制默認的操作方式。其優點是執行效率高，但缺點是數據復制不是很精準。對于基于行的復制方式來說，只需將MySQL主服務器中發生改動的數據復制到MySQL從服務器中，其復制的不是具體的命令而是真實數據。優點是精準度高，缺點是效率很低。

對于混合類型的復制來說，在默認情況下，優先采用基于語句的復制，來提高復制效率。當發現使用該方法無法提高復制的精準度時，就會采用基于行的復制方法來處理剩余的數據。MySQL主從復制的原理是當在執行更新、插入、刪除等語句時，首先對MySQL主服務器進行數據變更操作，將其寫入自己的二進制日志中。MySQL從服務器利用I/O線程讀取MySQL主服務器中的日志。當發現日志中出現的數據變動信息，就會讓MySQL從服務器執行復制動作。

MySQL讀寫分離原理分析

讀寫分離的原理是只在MySQL主服務器上寫數據，只在MySQL從服務器上讀取數據，實現MySQL讀寫分離。一般來說有兩種方式：其一,是基于程序代碼來實現，因為主從服務器的名稱和IP均不相同，因此在設計前端程序時，可以將所有的Select語句全部定位到MySQL從服務器上，而執行Insert，Update等語句時，全部定位到MySQL主服務器上。其二,是配置中間代理層，即在MySQL服務器和客戶端之間設置代理服務器，客戶端將讀寫操作請求全部提交給代理服務器，由代理服務器來決定如何將寫操作轉交給MySQL主服務器，將讀操作轉交給MySQL從服務器。其優點是可以優化維護操作，缺點是需要購買和配置代理服務器，讓網絡結構變得復雜。這里就后一種方式，通過實例來說明如何實現MySQL的主從復制和讀寫分離功能。

配置實驗網絡環境

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

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

安裝MySQL和所需的組件

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

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

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

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

配置MySQL主動復制參數

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

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

執 行“mysql -u root-p”命令，輸入密碼后登錄到MySQL，在控制臺提示符下執行“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;”命令，設置與其關聯的MySQL主服務器信息，注意，這里的各項參數需要和主服務器上的配置相同。例如賬戶名、密碼以及日志文件等。執行“start slave;”，“show slave statusG;”命令，在返回信息中的“Slave_IO_Runnin”欄和“Slave_SQL_Running”欄中必須顯示“YES”，才表示配置成功。在MySQL主服務器上登錄到MySQL控制臺，執行“create database dbceshi;”命令，創建一個用于測試的數據庫。

在某個MySQL從服務器中登錄到MySQL控制臺，執行“show databases;”命令，可以看到上述數據庫，這就說明主從復制已經配置完畢。

代理服務器上配置AMO EBA

接下來在代理服務器上配置Amoeba，因為Amoeba是使用JDK開發的，所以需要安裝JDK組件。注意，不需要使用最新的JDK。執行“chmod +x /usr/local/jdk-6u14-linux-x64.bin”命令，為其配置可執行權限。

執行“./jdk-6u14-linux-x64.bin”命 令，安裝JDK組 件。 執 行“mv jdk1.6_14/ /usr/local/jdk1.6”命 令，將JDK文件復制到指定的位置。執行“vim /etc/profile” 命令，對其配置文件進行修改，在其中的“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”等內容，配置和JDK和AMOEBA相關的路徑信息。

執行“source /etc/profile”語句，使上述配置生效。執行“java -version”命令，來驗證其版本。執行“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”等語句，依次執行創建對應版本的AMOEBA運行路徑，解壓AMOEBA壓縮包，為其設置可讀可寫可執行的權限，配置AMOEBA等操作。

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

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

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

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

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

按照同樣的方法進行同樣的修改時，不同的是修改的內容為“”和“192.168.0.91”。 因為默認該文件中只提供了兩個“”的語句段，所以需要復制并添加一個新的語句段。其完整內容包括“”，“”，“ < property name=ip Address>192.168.0.92 ”，“ ”，“”。這樣就將兩個從服務器信息添加進來。

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

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

在客戶端執行測試操作

因為在AMOEBA配置文件中設置的用戶名為“amoeba”，密碼為“qwe123”，所以為了實現讀寫分離，在客戶端程序中需要使用該賬戶和密碼，來操作后臺MySQL數據庫。

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

使用上述用于測試的數據庫，再使用“create table”，“insert into”，“select”等指令，在該數據庫創建表并插入數據和查看數據。當進入MySQL主服務器或者從服務器中，執行查看操作時，可看到上述表及數據已存在，說明主從復制和讀寫分離正常。