mysql主從復制以及讀寫分離

1、mysql主從復制以及讀寫分離之前我們已經(jīng)對LNMP平臺的Nginx做過了負載均衡以及高可用的部署，今天我們就通過一些技術(shù)來提升數(shù)據(jù)的高可用以及數(shù)據(jù)庫性能的提升。一、mysql主從復制首先我們先來看一下主從復制能夠解決什么問題。1、數(shù)據(jù)庫簡介  在現(xiàn)在的世界發(fā)展的道路上，數(shù)據(jù)已經(jīng)是必不可缺的一部分了，對數(shù)據(jù)的安全性，也成為了現(xiàn)在的一個值得探討的問題。那有什么方法能夠解決數(shù)據(jù)的安全性呢？          我們通過mysql本身的功能來實現(xiàn)數(shù)據(jù)的備份，之前我們也對數(shù)據(jù)可的數(shù)據(jù)進行了一些備

2、份，但是那些都不是很好的解決辦法，因為無論之前的導入導出也好，還是直接對數(shù)據(jù)庫所在目錄直接進行拷貝，這些技術(shù)不能保證實時性，而我們今天所介紹的就是能夠?qū)?shù)據(jù)庫實現(xiàn)熱備份，從而提高數(shù)據(jù)庫的安全性mysql主從復制2、主從復制原理1）mysql支持的復制類型       （1）基于語句的復制。在主服務(wù)器上執(zhí)行的sql語句，在從服務(wù)器上執(zhí)行同樣的語句。mysql默認使用基于語句的復制，效率比其他方式較高。       （2）基于行的復制，把改變的內(nèi)容復制過去，而

3、不是在從服務(wù)器上在執(zhí)行一遍。       （3）混合復制類型，默認采用基于語句的復制，一旦發(fā)現(xiàn)基于語句無法精確復制時，就會采用基于行的復制。2）復制的工作過程mysql復制的工作過程如下所示：圖1mysql復制的工作過程       （1）在每個事務(wù)跟新完成之前。master在二進制日志記錄這些改變。寫入二進制日志完成后，master通知存儲引擎提交事務(wù)。       （2）Slave將

4、Master的Binary log復制到其中繼日志。首先，slave打開一個工作線程I/O線程，I/O線程在Master上打開一個網(wǎng)絡(luò)連接，然后開始Binlog dump process。Binlog dump process從Master的二進制日志中讀取事件，如果已經(jīng)成功鏈接到Master，他會進行睡眠并等待Master產(chǎn)生新的事件。I/O線程將這些事件寫入中繼日志       （3）SQL slave thread（SQL 從線程）處理該過程的最后一步。SQL線程從中繼日志讀取事件，并重放其中的事件而更新Slave的數(shù)

5、據(jù)，使其與Master中的數(shù)據(jù)一致，只要該線程與I/O線程保持一致，中繼日志通常會位于OS的緩存中，所以中繼日志開銷很小。       以上就是mysql主從復制的原理，Slave可以有多臺，主服務(wù)也可以有多臺，可以使用keepalived做HA的高可用性，建議mysql的數(shù)據(jù)不要只放在共享存儲上，而是每個Slave都擁有一個單獨的存儲存放數(shù)據(jù)。       復制的過程中有一個很重要的限制，即復制在Slave上是串行化的，也就是說Master上的并行更新操

6、作不能在Slave上并行操作，也就是不能同時執(zhí)行。3、mysql讀寫分離原理       簡單來說就是實現(xiàn)讀與寫的分離（圖2）就是讀在從服務(wù)器上讀取數(shù)據(jù)，在寫數(shù)據(jù)的時候是寫在主服務(wù)上的。基本原理就是讓主服務(wù)器處理一些簡單的事務(wù)性查詢，而從服務(wù)器處理select查詢、數(shù)據(jù)庫復制被用來把事務(wù)性查詢導致的變更同步到集群的從數(shù)據(jù)庫中。圖2       目前較為常見的Mysql讀寫分離分為兩種1)基于程序代碼內(nèi)部實現(xiàn)    &

7、#160;  在代碼中根據(jù)select 、insert進行路由分類，這類方法也是目前生產(chǎn)環(huán)境下應(yīng)用最廣泛的。優(yōu)點是性能較好，因為程序在代碼中實現(xiàn)，不需要增加額外的硬件開支：缺點是需要開發(fā)人員來實現(xiàn)，運維人員無從下手。2）基于中間代理層實現(xiàn)       代理一般介于應(yīng)用服務(wù)器和數(shù)據(jù)庫服務(wù)器之間，代理數(shù)據(jù)庫服務(wù)器接收到應(yīng)用服務(wù)器的請求后根據(jù)判斷后轉(zhuǎn)發(fā)到，后端數(shù)據(jù)庫，有以下代表性的程序。（1）mysql_proxy。mysql_proxy是Mysql的一個開源項目，通過其自帶的lua腳本進行sql判斷

8、，雖然是mysql的官方產(chǎn)品，但是mysql官方并不推薦將其部署在生產(chǎn)環(huán)境下。（2）Atlas。是由 Qihoo 360, Web平臺部基礎(chǔ)架構(gòu)團隊開發(fā)維護的一個基于MySQL協(xié)議的數(shù)據(jù)中間層項目。它是在mysql-proxy 0.8.2版本的基礎(chǔ)上，對其進行了優(yōu)化，增加了一些新的功能特性。360內(nèi)部使用Atlas運行的mysql業(yè)務(wù)，每天承載的讀寫請求數(shù)達幾十億條。支持事物以及存儲過程（3）Moeeba。由阿里巴巴集團在職員工陳思儒使用序java語言進行開發(fā)，阿里巴巴集團將其用戶生產(chǎn)環(huán)境下，但是他并不支持事物以及存數(shù)過程。     

9、  我們今天所演示的就是amoeba這款軟件。版本2.2.0二、案例環(huán)境       本案例使用5臺服務(wù)器搭建，具體拓撲如圖3所示圖3      主機操作系統(tǒng)IP地址主要軟件MasterCentOS 6.5 x86_64192.168.1.2cmake-2.8.6.tar.gzmysql-5.5.22.tar.gzSlave1CentOS 6.5 x86_64192.168.1.3cmake-2.8.6.tar.gzmysql-5.5.22.ta

10、r.gzSlave2CentOS 6.5 x86_64192.168.1.4cmake-2.8.6.tar.gzmysql-5.5.22.tar.gzAmoebaCentOS 6.5 x86_64192.168.1.1amoeba-mysql-binary-2.2.0.tar.gzjdk-6u14-linux-x64.bin客戶端CentOS 6.5 x86_64192.168.1.5        以上就是今天的圖撲環(huán)境，如果需要解決單點故障的話，可以使用前面所講的keepalived實現(xiàn)，只不過多加了幾臺計算機

11、而已。Master也可以使用keepalived去避免單點故障，之前已經(jīng)講過了，這里就不在過多的進行講解了。1、配置時間同步       由于主從復制的時候時間必須要保持一致，這是我們可以再master作為時間同步服務(wù)器為salve提供時間同步。我們使用rpm方式安裝的ntp軟件包，采用yum的方式安裝rootcentos2 # yum -y install ntprootcentos2 # vim /etc/ntp.confserver 127.127.1.0fudge 127.127.1.0 stratum 8 

12、;  /這兩行在任意地方添加rootcentos2 # iptables  -I  INPUT - p udp  -dport  123  -j ACCEPT    /ntp  默認使用 udp的123號端口rootcentos2 # service ntpd start  /啟動ntp服務(wù)客戶端同步時間        如果沒有ntpdate命令可以使用yum安裝ntpdate軟件包slave1&

13、#160;      rootcentos3 # ntpdate 192.168.1.2 之后的服務(wù)器一樣。2、安裝部署mysql服務(wù)器這里我以slave2服務(wù)器為例演示安裝mysql服務(wù)器，master slave1 與slave2安裝一樣。       slave2：（1）安裝mysql并創(chuàng)建程序用戶rootcentos4 cmake-2.8.12# ./configure  &&  gmake  &&

14、;  gmake installrootcentos4 # tar zxf mysql-5.5.38.tar.gzrootcentos4 # cd mysql-5.5.38rootcentos4 mysql-5.5.38# cmake  > -DCMAKE_INSTALL_PREFIX=/usr/local/mysql  -DSYSCONFDIR=/etc/ -DDEFAULT_CHARSET=utf8  -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all  &

15、;& make  &&  make installrootcentos4 mysql-5.5.38# useradd  -M -s /sbin/nologin  mysql（2）優(yōu)化程序執(zhí)行路徑rootcentos4 mysql-5.5.38# echo >"PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile（3）創(chuàng)建主配置文件rootcentos4 mysql-5.5.38# cp support-files/my-f.sh  /etc

16、/fcp：是否覆蓋"/etc/f"？ yrootcentos4 mysql-5.5.38#（4）創(chuàng)建服務(wù)腳本并添加為系統(tǒng)服務(wù)rootcentos4 mysql-5.5.38# cp support-files/mysql.server /etc/init.d/mysqldrootcentos4 mysql-5.5.38#rootcentos4 mysql-5.5.38# chkconfig -add mysqldrootcentos4 mysql-5.5.38# chkconfig mysqld onrootcentos4 mysql-5.5.38# chmod +x /e

17、tc/init.d/mysql（5）初始化數(shù)據(jù)庫rootcentos4 mysql-5.5.38# /usr/local/mysql/scripts/mysql_install_db -user=mysql -basedir=/usr/local/mysql/ -datadir=/usr/local/mysql/rootcentos4 mysql-5.5.38#（6）修改安裝目錄權(quán)限r(nóng)ootcentos4 mysql-5.5.38# chown -R mysql:mysql  /usr/local/mysql/（7）啟動服務(wù)rootcentos4 mysql-5.5.38# serv

18、ice mysqld start（8）為用戶root設(shè)置密碼rootcentos4 mysql-5.5.38# mysqladmin  -u root -p password '123.abc'默認沒有密碼，直接在確認舊密碼處回車即可。（9）登錄mysql數(shù)據(jù)庫rootcentos4 mysql-5.5.38# mysql -u root -p123.abc3、配置master服務(wù)器（1）修改/etc/f 主配置文件rootcentos2 # vim /etc/fserver-id = 1 /mysql數(shù)據(jù)的唯一標示（不能重復）log-slave-updat

19、es=true /允許連級復制（增加）log-bin=master-bin /二進制文件名（修改）（2）重啟mysql服務(wù)rootcentos2 # service mysqld restart（3）登錄mysql數(shù)據(jù)庫rootcentos2 # mysql -u root -p123.abc（4）建立授權(quán)用戶賦予對所有庫和所有表的replication和slave權(quán)限，用戶為myslave來源為這個網(wǎng)段的任意IP密碼123.abc  /這個用戶是從數(shù)據(jù)庫用來復制二進制文件的用戶mysql> grant  replication  slave  o

20、n *.*  to  myslave'192.168.1.%' identified by '123.abc'（5）查看數(shù)據(jù)庫的二進制文件名和段偏移量的值圖4（6）退出mysql數(shù)據(jù)庫mysql> exitBye（7）建立防火墻規(guī)則rootcentos2 # iptables  -I  INPUT  -p  tcp  -dport  3306  -j ACCEPT  /允許目標端口為3306的入站4、配置從服務(wù)器（slave1）（1）修改/etc/froot

21、centos3 # vim /etc/fserver-id       = 2                                      /不能與其他實例

22、重復log-bin=mysql-bin                                           /二進制日志文件名relay-log=relay-log

23、-bin                                  /復制過來的二進制文件名relay-log-index=slave-relay-bin.index        

24、      /中繼日志存放的文件名稱（2）重新啟動mysqld服務(wù)rootcentos3 # service mysqld restart（3）登錄mysql數(shù)據(jù)庫rootcentos3 # mysql -u root -p123.abc（4）配置mysql數(shù)據(jù)同步mysql> change master  to  master_host='192.168.1.2'  ,  master_user='myslave'  ,  master_pass

25、word='123.abc'  ,  master_log_file='master-bin.000001'  ,  master_log_pos=261;ps:最后兩個配置項一定要與圖4的一樣否則不能同步IP地址、用戶、密碼都master的數(shù)據(jù)庫信息（5）啟動同步mysql> start slave;（6）查看同步信息圖5       G  這個選項是讓結(jié)果格式化輸出     

26、  驗證的結(jié)果一定是這兩個值必須都是yes如果一般情況下出現(xiàn)Slasve_IO_Running: connect  有可能是防火墻問題，再不就是上面命令的紅色部分與圖3所顯示的值不一致導致，建議重啟master的mysqld服務(wù)之后在顯示值，之后在salve上進行修改。在修改之前請先stop slave；之后在進行修改，改完之后在啟動復制，在進行驗證       slave2的配置與slave1的配置完全一樣，建議修改的地方直接復制slave1的配置，在數(shù)據(jù)庫中操作時注意書寫不要錯誤。

27、5、安裝amoeba（1）部署jdk環(huán)境rootcentos1 # chmod  +x jdk-6u14-linux-x64.binrootcentos1 # ./jdk-6u14-linux-x64.bin       在之后出現(xiàn)的閱讀許可協(xié)議的時候直切按q退出就可以了       最后會出現(xiàn)以下信心Do you agree to the above license terms? yes or no 是否同意以上許可，這里我們直接ye

28、s  輸入no則退出安裝       之后會進入一個安裝過程Press Enter to continue.  /直接回車完成安裝安裝完成之后會在當前目錄下產(chǎn)生以下目錄rootcentos1 # ls  jdk1.6.0_14/  -lddrwxr-xr-x. 10 root root 4096 Nov 11 00:54 jdk1.6.0_14/       由于amoeba是基于jdk1.5所開發(fā)的，所以

29、不建議使用高于1.6的jdk環(huán)境（2）移動安裝目錄rootcentos1 # mv jdk1.6.0_14/ /usr/local/jdk1.6（3）修改profile文件export JAVA_HOME=/usr/local/jdk1.6  /設(shè)置jdk的根目錄export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jire/lib /將jdk的程序文件賦予CLASSPATH變量export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin  /將jdk的程序文

30、件賦予PATH變量export AMOEBA_HOME=/usr/local/amoeba       /定義AMOEBA的根目錄export PATH=$PATH:$AMOEBA_HOME/bin  將amoeba的程序文件復制給PATH變量rootcentos1 # . /etc/profile   /刷新profile文件（4）創(chuàng)建amoeba的解壓目錄rootcentos1 # mkdir -p /usr/local/amoebarootcentos1 # chmod -R 755 /u

31、sr/local/amoeba/（5）解壓amoeba軟件包rootcentos1 # tar zxf amoeba-mysql-binary-2.2.0.tar.gz  -C /usr/local/amoeba/（6）驗證環(huán)境變量部署是否正確rootcentos1 # /usr/local/amoeba/bin/amoebaamoeba start|stop       出現(xiàn)以上提示則表明環(huán)境變量設(shè)置正確，jdk已經(jīng)開始工作了，我們可以查看一下jdk的版本rootcentos1 # javac -versionj

32、avac 1.6.0_286、配置amoeba實現(xiàn)讀寫分離       聲明：所有配置文件注釋都是以 <!- 內(nèi)容 ->，再刪除注釋時請將內(nèi)容也一并刪除，最好是刪除正行，但是有些時候只需要刪除頭和尾即可，里面的配置項是可以直接使用的。（1）修改amoeba安裝目錄下的conf目錄下的amoeba.xml主配置文件       這個配置文件需要定義兩個配置，第一是應(yīng)用程序使用什么用戶連接amoeba訪問到后端的mys

33、ql數(shù)據(jù)庫，第二個是定義默認寫池以及讀池。       因為配置文件較多，只列出修改的部分rootcentos1 # vim /usr/local/amoeba/conf/amoeba.xml<property name="authenticator"><bean      class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"><p

34、roperty name="user">amoeba</property><property name="password">123.abc</property><property name="filter"><bean class="com.meidusa.amoeba.server.IPAccessController">          

35、60;                                           <property name="ipFile">$amo

36、eba.home/conf/access_list.conf</property>               </bean>             </property>       </bean></property> &#

37、160;     粗體部分是需要修改的部分<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter"> <property name="ruleLoader"> <bean class="com.meidusa.amoeba.route.TableRuleFileLoader"><property name="ruleFile"

38、;>$amoeba.home/conf/rule.xml</property><property name="functionFile">$amoeba.home/conf/ruleFunctionMap.xml </property>          </bean>    </property><property   name="sqlFunctionFile&

39、quot;>$amoeba.home/conf/functionMap.xml</property>      <property name="LRUMapSize">1500</property>      <property name="defaultPool">master</property>     /定義默認的寫池 &#

40、160;    <property name="writePool">master</property>  /定義寫池      <property name="readPool">slaves</property> /定義讀池，以上名字隨便             <property name

41、="needParse">true</property></queryRouter>       以上字體加粗部分就是這個配置文件需要修改的內(nèi)容（2）修改dbServer.xml配置文件，定義寫的服務(wù)器以及讀的服務(wù)器并且指定算法。rootcentos1 # vim  /usr/local/amoeba/conf/dbServers.xml<property name="user">test</property> 

42、                  /26行<property name="password">123.abc</property> / 27行       以上兩行為使用什么身份訪問后臺數(shù)據(jù)庫  這個用戶需要在所有數(shù)據(jù)庫進行授全，權(quán)限為對所有庫的所有表擁有所有權(quán)限，并且允許amoeba這臺主機

43、的IP訪問用戶名不是固定的，根據(jù)實際情況建立。<dbServer name="master"  parent="abstractServer">   <factoryConfig>  /定義寫的服務(wù)器這個名字必須與前面定義的寫池保持一致 <!- mysql ip -> <property name="ipAddress">192.168.1.2</property> /寫服務(wù)器的IP    <

44、;/factoryConfig> </dbServer> <dbServer name="slave1"  parent="abstractServer"> /這個名字隨便 <factoryConfig> <!- mysql ip -><property name="ipAddress">192.168.1.3</property>/讀服務(wù)器IP   </factoryConfig&

45、gt;</dbServer> <dbServer name="slave2"  parent="abstractServer">/這個名字隨便 <factoryConfig> <!- mysql ip -> <property name="ipAddress">192.168.1.4</property>/讀服務(wù)器的IP      </factoryConfi

46、g> </dbServer>以上部分都是需要修改的，slave2所在的部分是復制slave1的配置<dbServer name="slaves" virtual="true">/調(diào)用主配置文件定義的讀池 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool"><!- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA->

47、<property name="loadbalance">1</property> <!- Separated by commas,such as: server1,server2,server1 -> <property name="poolNames">slave1,slave2</property>/定義讀服務(wù)器所在的配置型    </poolConfig></dbServer>   &

48、#160;   這部分內(nèi)容是定義讀池負載均衡算法，1表示輪詢、2表示加權(quán)輪詢       以上就是所有配置文件的修改以及解釋3、在master、slave1、slave2上建立test用戶，這個用戶需要根據(jù)配置文件建立mysql> grant all  on *.* to test'192.168.1.%' identified by '123.abc'       好了，現(xiàn)在可

49、以將amoeba的服務(wù)啟動并驗證rootcentos1 # amoeba start&    /默認amoeba啟動之后需要占用一個終端，為了防止這種情況發(fā)生，我們可以直接將其放到后臺運行，在啟動過程中，如果發(fā)現(xiàn)出現(xiàn)很長的提示信息并且都是以java結(jié)束，那么表名服務(wù)沒有啟動成功，amoeba默認監(jiān)聽8066端口，我們可以進行驗證rootcentos1 # netstat -anpt | grep 8066tcp        0      

50、;0 :8066       :*                        LISTEN      3405/java        7、建立防火墻規(guī)則rootcentos1 # ip

51、tables -I INPUT -p tcp -dport 8066 -j ACCEPT8、master、slave1、slave2都需要開放3306端口入站rootcentos2 # iptables -I INPUT -p tcp -dport 3306 -j ACCEPTrootcentos2 # service iptables saveiptables：將防火墻規(guī)則保存到 /etc/sysconfig/iptables：     確定rootcentos2 #      &#

52、160;另外兩臺從服務(wù)器也是一樣的配置，或者直接將iptables stop掉9、測試（1）在應(yīng)用服務(wù)器上rootweb # yum -y install mysql       通過代理訪問mysqlrootweb # mysql -u amoeba -p123.abc -h 192.168.1.1 -P8066       -P 指定amoeba的默認端口  這樣默認設(shè)置需要指定端口，我們可以修改amoeba的amoeba.xml配

53、置文件的第一個8066改為3306之后建立一條防火墻規(guī)則為允許3306端口入站并且重新啟動amoeba服務(wù)       amoeba stop       amoeba start&<property name="port">3306</property> /在11行rootcentos1 # amoeba stoprootcentos1 # amoeba start&rootcentos1 # iptables -I INPUT