MySQL主從復(fù)制以及讀寫分離
堅(jiān)守“ 做人真誠(chéng) · 做事靠譜 · 口碑至上 · 高效敬業(yè) ”的價(jià)值觀，專業(yè)網(wǎng)站建設(shè)服務(wù)10余年為成都成都除甲醛小微創(chuàng)業(yè)公司專業(yè)提供成都定制網(wǎng)頁(yè)設(shè)計(jì)營(yíng)銷網(wǎng)站建設(shè)商城網(wǎng)站建設(shè)手機(jī)網(wǎng)站建設(shè)小程序網(wǎng)站建設(shè)網(wǎng)站改版，從內(nèi)容策劃、視覺(jué)設(shè)計(jì)、底層架構(gòu)、網(wǎng)頁(yè)布局、功能開(kāi)發(fā)迭代于一體的高端網(wǎng)站建設(shè)服務(wù)。
之前我們已經(jīng)對(duì)LNMP平臺(tái)的Nginx做過(guò)了負(fù)載均衡以及高可用的部署，今天我們就通過(guò)一些技術(shù)來(lái)提升數(shù)據(jù)的高可用以及數(shù)據(jù)庫(kù)性能的提升。
一、mysql主從復(fù)制
首先我們先來(lái)看一下主從復(fù)制能夠解決什么問(wèn)題。
在現(xiàn)在的世界發(fā)展的道路上，數(shù)據(jù)已經(jīng)是必不可缺的一部分了，對(duì)數(shù)據(jù)的安全性，也成為了現(xiàn)在的一個(gè)值得探討的問(wèn)題。那有什么方法能夠解決數(shù)據(jù)的安全性呢？
我們通過(guò)mysql本身的功能來(lái)實(shí)現(xiàn)數(shù)據(jù)的備份，之前我們也對(duì)數(shù)據(jù)可的數(shù)據(jù)進(jìn)行了一些備份，但是那些都不是很好的解決辦法，因?yàn)闊o(wú)論之前的導(dǎo)入導(dǎo)出也好，還是直接對(duì)數(shù)據(jù)庫(kù)所在目錄直接進(jìn)行拷貝，這些技術(shù)不能保證實(shí)時(shí)性，而我們今天所介紹的就是能夠?qū)?shù)據(jù)庫(kù)實(shí)現(xiàn)熱備份，從而提高數(shù)據(jù)庫(kù)的安全性——mysql主從復(fù)制
（1）基于語(yǔ)句的復(fù)制。在主服務(wù)器上執(zhí)行的sql語(yǔ)句，在從服務(wù)器上執(zhí)行同樣的語(yǔ)句。mysql默認(rèn)使用基于語(yǔ)句的復(fù)制，效率比其他方式較高。
（2）基于行的復(fù)制，把改變的內(nèi)容復(fù)制過(guò)去，而不是在從服務(wù)器上在執(zhí)行一遍。
（3）混合復(fù)制類型，默認(rèn)采用基于語(yǔ)句的復(fù)制，一旦發(fā)現(xiàn)基于語(yǔ)句無(wú)法精確復(fù)制時(shí)，就會(huì)采用基于行的復(fù)制。
mysql復(fù)制的工作過(guò)程如下所示：
圖1mysql復(fù)制的工作過(guò)程
（1）在每個(gè)事務(wù)跟新完成之前。master在二進(jìn)制日志記錄這些改變。寫入二進(jìn)制日志完成后，master通知存儲(chǔ)引擎提交事務(wù)。
（2）Slave將Master的Binary log復(fù)制到其中繼日志。首先，slave打開(kāi)一個(gè)工作線程——I/O線程，I/O線程在Master上打開(kāi)一個(gè)網(wǎng)絡(luò)連接，然后開(kāi)始Binlog dump process。 Binlog dump process從Master的二進(jìn)制日志中讀取事件，如果已經(jīng)成功鏈接到Master，他會(huì)進(jìn)行睡眠并等待Master產(chǎn)生新的事件。I/O線程將這些事件寫入中繼日志
（3）SQL slave thread（SQL從線程）處理該過(guò)程的最后一步。SQL線程從中繼日志讀取事件，并重放其中的事件而更新Slave的數(shù)據(jù)，使其與Master中的數(shù)據(jù)一致，只要該線程與I/O線程保持一致，中繼日志通常會(huì)位于OS的緩存中，所以中繼日志開(kāi)銷很小。
以上就是mysql主從復(fù)制的原理，Slave可以有多臺(tái)，主服務(wù)也可以有多臺(tái)，可以使用keepalived做HA的高可用性，建議mysql的數(shù)據(jù)不要只放在共享存儲(chǔ)上，而是每個(gè)Slave都擁有一個(gè)單獨(dú)的存儲(chǔ)存放數(shù)據(jù)。
復(fù)制的過(guò)程中有一個(gè)很重要的限制，即復(fù)制在Slave上是串行化的，也就是說(shuō)Master上的并行更新操作不能在Slave上并行操作，也就是不能同時(shí)執(zhí)行。
簡(jiǎn)單來(lái)說(shuō)就是實(shí)現(xiàn)讀與寫的分離（圖2）就是讀在從服務(wù)器上讀取數(shù)據(jù)，在寫數(shù)據(jù)的時(shí)候是寫在主服務(wù)上的?；驹砭褪亲屩鞣?wù)器處理一些簡(jiǎn)單的事務(wù)性查詢，而從服務(wù)器處理select查詢、數(shù)據(jù)庫(kù)復(fù)制被用來(lái)把事務(wù)性查詢導(dǎo)致的變更同步到集群的從數(shù)據(jù)庫(kù)中。
圖2
目前較為常見(jiàn)的Mysql讀寫分離分為兩種
在代碼中根據(jù)select、insert進(jìn)行路由分類，這類方法也是目前生產(chǎn)環(huán)境下應(yīng)用最廣泛的。優(yōu)點(diǎn)是性能較好，因?yàn)槌绦蛟诖a中實(shí)現(xiàn)，不需要增加額外的硬件開(kāi)支：缺點(diǎn)是需要開(kāi)發(fā)人員來(lái)實(shí)現(xiàn)，運(yùn)維人員無(wú)從下手。
代理一般介于應(yīng)用服務(wù)器和數(shù)據(jù)庫(kù)服務(wù)器之間，代理數(shù)據(jù)庫(kù)服務(wù)器接收到應(yīng)用服務(wù)器的請(qǐng)求后根據(jù)判斷后轉(zhuǎn)發(fā)到，后端數(shù)據(jù)庫(kù)，有以下代表性的程序。
（1）mysql_proxy。mysql_proxy是Mysql的一個(gè)開(kāi)源項(xiàng)目，通過(guò)其自帶的lua腳本進(jìn)行sql判斷，雖然是mysql的官方產(chǎn)品，但是mysql官方并不推薦將其部署在生產(chǎn)環(huán)境下。
（2）Atlas。是由 Qihoo 360, Web平臺(tái)部基礎(chǔ)架構(gòu)團(tuán)隊(duì)開(kāi)發(fā)維護(hù)的一個(gè)基于MySQL協(xié)議的數(shù)據(jù)中間層項(xiàng)目。它是在mysql-proxy 0.8.2版本的基礎(chǔ)上，對(duì)其進(jìn)行了優(yōu)化，增加了一些新的功能特性。360內(nèi)部使用Atlas運(yùn)行的mysql業(yè)務(wù)，每天承載的讀寫請(qǐng)求數(shù)達(dá)幾十億條。支持事物以及存儲(chǔ)過(guò)程
（3）Moeeba。由阿里巴巴集團(tuán)在職員工陳思儒使用序java語(yǔ)言進(jìn)行開(kāi)發(fā)，阿里巴巴集團(tuán)將其用戶生產(chǎn)環(huán)境下，但是他并不支持事物以及存數(shù)過(guò)程。
我們今天所演示的就是amoeba這款軟件。版本2.2.0
本案例使用5臺(tái)服務(wù)器搭建，具體拓?fù)淙鐖D3所示
圖3
主機(jī) | 操作系統(tǒng) | IP地址 | 主要軟件 |
Master | CentOS 6.5 x86_64 | 192.168.1.2 | cmake-2.8.6.tar.gz mysql-5.5.22.tar.gz |
Slave1 | CentOS 6.5 x86_64 | 192.168.1.3 | cmake-2.8.6.tar.gz mysql-5.5.22.tar.gz |
Slave2 | CentOS 6.5 x86_64 | 192.168.1.4 | cmake-2.8.6.tar.gz mysql-5.5.22.tar.gz |
Amoeba | CentOS 6.5 x86_64 | 192.168.1.1 | amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin |
客戶端 | CentOS 6.5 x86_64 | 192.168.1.5 |
以上就是今天的圖撲環(huán)境，如果需要解決單點(diǎn)故障的話，可以使用前面所講的keepalived實(shí)現(xiàn)，只不過(guò)多加了幾臺(tái)計(jì)算機(jī)而已。Master也可以使用keepalived去避免單點(diǎn)故障，之前已經(jīng)講過(guò)了，這里就不在過(guò)多的進(jìn)行講解了。
由于主從復(fù)制的時(shí)候時(shí)間必須要保持一致，這是我們可以再master作為時(shí)間同步服務(wù)器為salve提供時(shí)間同步。我們使用rpm方式安裝的ntp軟件包，采用yum的方式安裝
[root@centos2 ~]# yum -y install ntp
[root@centos2 ~]# vim /etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8 //這兩行在任意地方添加
[root@centos2 ~]# iptables -I INPUT - p udp --dport 123 -j ACCEPT //ntp 默認(rèn)使用 udp的123號(hào)端口
[root@centos2 ~]# service ntpd start //啟動(dòng)ntp服務(wù)
客戶端同步時(shí)間
如果沒(méi)有ntpdate命令可以使用yum安裝ntpdate軟件包
slave1
[root@centos3 ~]# ntpdate 192.168.1.2 之后的服務(wù)器一樣。
這里我以slave2服務(wù)器為例演示安裝mysql服務(wù)器，master slave1與slave2安裝一樣。
slave2：
[root@centos4 cmake-2.8.12]# ./configure && gmake && gmake install
[root@centos4 ~]# tar zxf mysql-5.5.38.tar.gz
[root@centos4 ~]# cd mysql-5.5.38
[root@centos4 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 && make && make install
[root@centos4 mysql-5.5.38]# useradd -M -s /sbin/nologin mysql
[root@centos4 mysql-5.5.38]# echo \
>"PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@centos4 mysql-5.5.38]# cp support-files/my-medium.cnf.sh /etc/my.cnf
cp：是否覆蓋"/etc/my.cnf"？ y
[root@centos4 mysql-5.5.38]#
[root@centos4 mysql-5.5.38]# cp support-files/mysql.server /etc/init.d/mysqld
[root@centos4 mysql-5.5.38]#
[root@centos4 mysql-5.5.38]# chkconfig --add mysqld
[root@centos4 mysql-5.5.38]# chkconfig mysqld on
[root@centos4 mysql-5.5.38]# chmod +x /etc/init.d/mysql
[root@centos4 mysql-5.5.38]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/
[root@centos4 mysql-5.5.38]#
[root@centos4 mysql-5.5.38]# chown -R mysql:mysql /usr/local/mysql/
[root@centos4 mysql-5.5.38]# service mysqld start
[root@centos4 mysql-5.5.38]# mysqladmin -u root -p password '123.abc'
默認(rèn)沒(méi)有密碼，直接在確認(rèn)舊密碼處回車即可。
[root@centos4 mysql-5.5.38]# mysql -u root -p123.abc
[root@centos2 ~]# vim /etc/my.cnf
server-id = 1 //mysql數(shù)據(jù)的唯一標(biāo)示（不能重復(fù)）
log-slave-updates=true //允許連級(jí)復(fù)制 （增加）
log-bin=master-bin //二進(jìn)制文件名（修改）
[root@centos2 ~]# service mysqld restart
[root@centos2 ~]# mysql -u root -p123.abc
賦予對(duì)所有庫(kù)和所有表的replication和slave權(quán)限，用戶為myslave來(lái)源為這個(gè)網(wǎng)段的任意IP密碼123.abc //這個(gè)用戶是從數(shù)據(jù)庫(kù)用來(lái)復(fù)制二進(jìn)制文件的用戶
mysql> grant replication slave on *.* to myslave@'192.168.1.%' identified by '123.abc';
圖4
mysql> exit
Bye
root@centos2 ~]# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT //允許目標(biāo)端口為3306的入站
[root@centos3 ~]# vim /etc/my.cnf
server-id = 2 //不能與其他實(shí)例重復(fù)
log-bin=mysql-bin //二進(jìn)制日志文件名
relay-log=relay-log-bin //復(fù)制過(guò)來(lái)的二進(jìn)制文件名
relay-log-index=slave-relay-bin.index //中繼日志存放的文件名稱
[root@centos3 ~]# service mysqld restart
[root@centos3 ~]# mysql -u root -p123.abc
mysql> change master to master_host='192.168.1.2' , master_user='myslave' , master_password='123.abc' , master_log_file='master-bin.000001' , master_log_pos=261;
ps:最后兩個(gè)配置項(xiàng)一定要與圖4的一樣否則不能同步
IP地址、用戶、密碼都master的數(shù)據(jù)庫(kù)信息
（5）啟動(dòng)同步
mysql> start slave;
（6）查看同步信息
圖5
\G 這個(gè)選項(xiàng)是讓結(jié)果格式化輸出
驗(yàn)證的結(jié)果一定是這兩個(gè)值必須都是yes如果一般情況下出現(xiàn)Slasve_IO_Running: connect 有可能是防火墻問(wèn)題，再不就是上面命令的紅色部分與圖3所顯示的值不一致導(dǎo)致，建議重啟master的mysqld服務(wù)之后在顯示值，之后在salve上進(jìn)行修改。在修改之前請(qǐng)先stop slave； 之后在進(jìn)行修改，改完之后在啟動(dòng)復(fù)制，在進(jìn)行驗(yàn)證
slave2的配置與slave1的配置完全一樣，建議修改的地方直接復(fù)制slave1的配置，在數(shù)據(jù)庫(kù)中操作時(shí)注意書寫不要錯(cuò)誤。
5、安裝amoeba
（1）部署jdk環(huán)境
[root@centos1 ~]# chmod +x jdk-6u14-linux-x64.bin
[root@centos1 ~]# ./jdk-6u14-linux-x64.bin
在之后出現(xiàn)的閱讀許可協(xié)議的時(shí)候直切按q退出就可以了
最后會(huì)出現(xiàn)以下信心
Do you agree to the above license terms? [yes or no]是否同意以上許可，這里我們直接yes 輸入no則退出安裝
之后會(huì)進(jìn)入一個(gè)安裝過(guò)程
Press Enter to continue..... //直接回車完成安裝
安裝完成之后會(huì)在當(dāng)前目錄下產(chǎn)生以下目錄
[root@centos1 ~]# ls jdk1.6.0_14/ -ld
drwxr-xr-x. 10 root root 4096 Nov 11 00:54 jdk1.6.0_14/
由于amoeba是基于jdk1.5所開(kāi)發(fā)的，所以不建議使用高于1.6的jdk環(huán)境
（2）移動(dòng)安裝目錄
[root@centos1 ~]# 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/jir
e/lib //將jdk的程序文件賦予CLASSPATH變量
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME
/bin //將jdk的程序文件賦予PATH變量
export AMOEBA_HOME=/usr/local/amoeba //定義AMOEBA的根目錄
export PATH=$PATH:$AMOEBA_HOME/bin 將amoeba的程序文件復(fù)制給PATH變量
[root@centos1 ~]# . /etc/profile //刷新profile文件
（4）創(chuàng)建amoeba的解壓目錄
[root@centos1 ~]# mkdir -p /usr/local/amoeba
[root@centos1 ~]# chmod -R 755 /usr/local/amoeba/
（5）解壓amoeba軟件包
[root@centos1 ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
（6）驗(yàn)證環(huán)境變量部署是否正確
[root@centos1 ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop
出現(xiàn)以上提示則表明環(huán)境變量設(shè)置正確，jdk已經(jīng)開(kāi)始工作了，我們可以查看一下jdk的版本
[root@centos1 ~]# javac -version
javac 1.6.0_28
6、配置amoeba實(shí)現(xiàn)讀寫分離
聲明：所有配置文件注釋都是以 ，再刪除注釋時(shí)請(qǐng)將內(nèi)容也一并刪除，最好是刪除正行，但是有些時(shí)候只需要?jiǎng)h除頭和尾即可，里面的配置項(xiàng)是可以直接使用的。
（1）修改amoeba安裝目錄下的conf目錄下的amoeba.xml主配置文件
這個(gè)配置文件需要定義兩個(gè)配置，第一是應(yīng)用程序使用什么用戶連接amoeba訪問(wèn)到后端的mysql數(shù)據(jù)庫(kù)，第二個(gè)是定義默認(rèn)寫池以及讀池。
因?yàn)榕渲梦募^多，只列出修改的部分
[root@centos1 ~]# vim /usr/local/amoeba/conf/amoeba.xml
conf
粗體部分是需要修改的部分
以上字體加粗部分就是這個(gè)配置文件需要修改的內(nèi)容
（2）修改dbServer.xml配置文件，定義寫的服務(wù)器以及讀的服務(wù)器并且指定算法。
[root@centos1 ~]# vim /usr/local/amoeba/conf/dbServers.xml
以上兩行為使用什么身份訪問(wèn)后臺(tái)數(shù)據(jù)庫(kù) 這個(gè)用戶需要在所有數(shù)據(jù)庫(kù)進(jìn)行授全，權(quán)限為對(duì)所有庫(kù)的所有表?yè)碛兴袡?quán)限，并且允許amoeba這臺(tái)主機(jī)的IP訪問(wèn) 用戶名不是固定的，根據(jù)實(shí)際情況建立。
以上部分都是需要修改的，slave2所在的部分是復(fù)制slave1的配置
這部分內(nèi)容是定義讀池負(fù)載均衡算法，1表示輪詢、2表示加權(quán)輪詢
以上就是所有配置文件的修改以及解釋
3、在master、slave1、slave2上建立test用戶，這個(gè)用戶需要根據(jù)配置文件建立
mysql> grant all on *.* to test@'192.168.1.%' identified by '123.abc';
好了，現(xiàn)在可以將amoeba的服務(wù)啟動(dòng)并驗(yàn)證
[root@centos1 ~]# amoeba start& //默認(rèn)amoeba啟動(dòng)之后需要占用一個(gè)終端，為了防止這種情況發(fā)生，我們可以直接將其放到后臺(tái)運(yùn)行，在啟動(dòng)過(guò)程中，如果發(fā)現(xiàn)出現(xiàn)很長(zhǎng)的提示信息并且都是以java結(jié)束，那么表名服務(wù)沒(méi)有啟動(dòng)成功，amoeba默認(rèn)監(jiān)聽(tīng)8066端口，我們可以進(jìn)行驗(yàn)證
[root@centos1 ~]# netstat -anpt | grep 8066
tcp 0 0 :::8066 :::* LISTEN 3405/java
7、建立防火墻規(guī)則
[root@centos1 ~]# iptables -I INPUT -p tcp --dport 8066 -j ACCEPT
8、master、slave1、slave2都需要開(kāi)放3306端口入站
[root@centos2 ~]# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
[root@centos2 ~]# service iptables save
iptables：將防火墻規(guī)則保存到 /etc/sysconfig/iptables： [確定]
[root@centos2 ~]#
另外兩臺(tái)從服務(wù)器也是一樣的配置，或者直接將iptables stop掉
9、測(cè)試
（1）在應(yīng)用服務(wù)器上
[root@web ~]# yum -y install mysql
通過(guò)代理訪問(wèn)mysql
[root@web ~]# mysql -u amoeba -p123.abc -h 192.168.1.1 -P8066
-P指定amoeba的默認(rèn)端口 這樣默認(rèn)設(shè)置需要指定端口，我們可以修改amoeba的amoeba.xml配置文件的第一個(gè)8066改為3306之后建立一條防火墻規(guī)則為允許3306端口入站并且重新啟動(dòng)amoeba服務(wù)
amoeba stop
amoeba start&
[root@centos1 ~]# amoeba stop
[root@centos1 ~]# amoeba start&
[root@centos1 ~]# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
在進(jìn)行登錄amoeba
[root@web ~]# mysql -u amoeba -p123.abc -h 192.168.1.1
（2）在master上創(chuàng)建banji庫(kù)，在benji庫(kù)中新建class表，看是否同步到其他服務(wù)器上，然后關(guān)掉各個(gè)服務(wù)器上的lave功能，再插入?yún)^(qū)別語(yǔ)句
Master操作
mysql> create database banji;
mysql> create table banji.class(id int);
slave1操作
圖6
mysql> stop slave;
slave2操作
圖7
mysql> stop slave;
Master操作
mysql> insert into banji.class values(1);
mysql> insert into banji.class values(2);
mysql> insert into banji.class values(3);
圖8
從上圖可以看出執(zhí)行了兩次查詢分別從slave1和slave2各自的數(shù)據(jù)庫(kù)中讀取，內(nèi)容是各自的值，因?yàn)闆](méi)有同步，所以值只有一個(gè)
mysql> insert into banji.class values(4);
圖9
看不到剛插入的4，也就證明了，寫操作在Master我們可以去Master上看一下
Master操作
圖10
在Master可以看見(jiàn)剛插入的值，我們將各個(gè)slave的功能開(kāi)啟
Slave1，slave2操作
mysql> start slave;
client操作
重慶分公司
重慶分公司
