本文主要給大家介紹MySQL-proxy如何實(shí)現(xiàn)數(shù)據(jù)庫讀寫分離,希望可以給大家補(bǔ)充和更新些知識,如有其它問題需要了解的可以持續(xù)在創(chuàng)新互聯(lián)行業(yè)資訊里面關(guān)注我的更新文章的。
創(chuàng)新互聯(lián)專注于鐵門關(guān)網(wǎng)站建設(shè)服務(wù)及定制,我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗(yàn)。 熱誠為您提供鐵門關(guān)營銷型網(wǎng)站建設(shè),鐵門關(guān)網(wǎng)站制作、鐵門關(guān)網(wǎng)頁設(shè)計(jì)、鐵門關(guān)網(wǎng)站官網(wǎng)定制、微信平臺小程序開發(fā)服務(wù),打造鐵門關(guān)網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供鐵門關(guān)網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。
機(jī)器:192.168.21.139
完成實(shí)戰(zhàn)之前的必要條件:主從復(fù)制環(huán)境搭建好。
MySQL Proxy是一個(gè)處于你的Client端和MySQL server端之間的簡單程序,它可以監(jiān)測、分析或改變它們的通信。它使用靈活,沒有限制,常見的用途包括:負(fù)載平衡,故障、查詢分析,查詢過濾和修改等等。
MySQL Proxy就是這么一個(gè)中間層代理,簡單的說,MySQL Proxy就是一個(gè)連接池,負(fù)責(zé)將前臺應(yīng)用的連接請求轉(zhuǎn)發(fā)給后臺的數(shù)據(jù)庫,并且通過使用lua腳本,可以實(shí)現(xiàn)復(fù)雜的連接控制和過濾,從而實(shí)現(xiàn)讀寫分離和負(fù)載平衡。對于應(yīng)用來說,MySQLProxy是完全透明的,應(yīng)用則只需要連接到MySQL Proxy的監(jiān)聽端口即可。當(dāng)然,這樣proxy機(jī)器可能成為單點(diǎn)失效,但完全可以使用多個(gè)proxy機(jī)器做為冗余,在應(yīng)用云服務(wù)器的連接池配置中配置到多個(gè)proxy的連接參數(shù)即可。
MySQL Proxy更強(qiáng)大的一項(xiàng)功能是實(shí)現(xiàn)“讀寫分離”,基本原理是讓主數(shù)據(jù)庫處理事務(wù)性查詢,讓從庫處理SELECT查詢。數(shù)據(jù)庫復(fù)制被用來把事務(wù)性查詢導(dǎo)致的變更同步到集群中的從庫。
主機(jī)名 | IP地址 | 系統(tǒng)版本 |
mysql-proxy | 192.168.21.139 | CentOS release 6.4 (Final) 2.6.32-431.el6.x86_64 |
mysql-master | 192.168.21.135 | CentOS release 6.4 (Final) 2.6.32-431.el6.x86_64 |
mysql-slave | 192.168.21.135 | CentOS release 6.4 (Final) 2.6.32-431.el6.x86_64 |
yum -y install gcc gcc-c++autoconf libevent-devel pkgconfig libtool mysql-devel
[root@zyl tools]# wgethttp://ftp.gnome.org/pub/gnome/sources/glib/2.22/glib-2.22.5.tar.gz
tar xf glib-2.22.5.tar.gz
cd glib-2.22.5
./configure--prefix=/usr/local/glib-2.22.5
make && make install
echo $?
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
tar xf lua-5.1.4.tar.gz
cd lua-5.1.4
sed -i's#^INSTALL_TOP=.*#INSTALL_TOP= /usr/local/lua-5.1.4#gi' ./Makefile
sed -i 's#^CFLAGS=.*#CFLAGS= -02-fPIC -Wall $(MYCFLAGS)#gi' ./src/Makefile
make linux install
echo $?
tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
cdmysql-proxy-0.8.5-linux-glibc2.3-x86-64bit
cd ..
cp -amysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy
mv mysql-proxy /application/
cd /application/
ls
pkill mysql
netstat -lnput|grep 330
cd mysql
cd ../mysql-proxy/
ls
mkdir conf
mkdir logs
ls
cd ./conf/
ls
#添加到PATH環(huán)境變量
echo 'exportPATH=$PATH:/application/mysql-proxy/bin/' >>/etc/profile
tail -1 /etc/profile
source /etc/profile
which mysql-proxy
/application/mysql-proxy/bin/mysql-proxy--help
vim ./mysql-proxy.cnf
#############################################
簡單一個(gè)配置(192.168.21.135:單機(jī)多實(shí)例)
[mysql-proxy]
plugins=admin,proxy
admin-username=admin
admin-password=admin
admin-lua-script=/application/mysql-proxy/lib/mysql-proxy/lua/admin.lua
proxy-backend-addresses=192.168.21.135:3306 èmaster
proxy-read-only-backend-addresses=192.168.21.135:3308èslave
proxy-lua-script=/application/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-level=debug 日志的級別
keepalive=true 在mysql-proxy崩潰時(shí)嘗試重啟之;
daemon=true 以守護(hù)進(jìn)程模式啟動(dòng)mysql-proxy;
log-file=/application/mysql-proxy/logs/proxy.log
##########################
chmod 0660 mysql-proxy.cnf
啟動(dòng):
/application/mysql-proxy/bin/mysql-proxy--defaults-file=/application/mysql-proxy/conf/mysql-proxy.cnf
cat logs/proxy.log
[root@zyl tools]# netstat -lnpt|grep mysql-proxy
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 4261/mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 4261/mysql-proxy
4040是proxy端口;4041是admin端口,也就是管理。
mysqlgrant all on *.* to 'zyl'@'192.168.21.139' identified by '888666';
mysql> flush privileges
##########################
多開幾個(gè)終端,測試連接:
[root@zyl -master ]# mysql -u zyl -p -h 192.168.21.139 --port 4040
[root@zyl mysql-proxy]# mysql-uadmin -P4041 -h 192.168.21.139 -padmin
mysql> select * from help;
+------------------------+------------------------------------+
| command | description |
+------------------------+------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | liststhe backends and their state |
+------------------------+------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
| 1 | 192.168.21.135:3306 | unknown| rw |NULL | 0 |
| 2 | 192.168.21.135:3308 | unknown| ro | NULL | 0 |
+-------------+---------------------+---------+------+------+-------------------+
最終結(jié)果:
mysql> select * frombackends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid |connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
| 1 |192.168.21.135:3306 | up | rw | NULL | 0 |
| 2 |192.168.21.135:3308 | up | ro | NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)
##出現(xiàn)兩個(gè)up,那么分離成功了。
1.
[root@zyl lua-5.1.4]# make linuxinstall
cd src && make linux
make[1]: Entering directory`/home/tools/lua-5.1.4/src'
make allMYCFLAGS=-DLUA_USE_LINUX MYLIBS="-Wl,-E -ldl -lreadline -lhistory-lncurses"
make[2]: Entering directory`/home/tools/lua-5.1.4/src'
gcc -O2 -Wall -fPIC -DLUA_USE_LINUX -c -o lua.o lua.c
在包含自 lua.h:16 的文件中,
從 lua.c:15:
luaconf.h:275:31:錯(cuò)誤:readline/readline.h:沒有那個(gè)文件或目錄
luaconf.h:276:30:錯(cuò)誤:readline/history.h:沒有那個(gè)文件或目錄
lua.c:在函數(shù)‘pushline’中:
lua.c:182:警告:隱式聲明函數(shù)‘readline’
lua.c:182:警告:賦值時(shí)將整數(shù)賦給指針,未作類型轉(zhuǎn)換
lua.c:在函數(shù)‘loadline’中:
lua.c:210:警告:隱式聲明函數(shù)‘a(chǎn)dd_history’
make[2]: *** [lua.o]錯(cuò)誤 1
make[2]: Leaving directory`/home/tools/lua-5.1.4/src'
make[1]: *** [linux]錯(cuò)誤 2
make[1]: Leaving directory`/home/tools/lua-5.1.4/src'
make: *** [linux]錯(cuò)誤 2
解決:
yum installlibtermcap-devel ncurses-devel libevent-devel readline-devel
-------------------------------------------------------------------------------------------
2.
[root@zyl tools]# mysql -u zyl -p -h 192.168.21.139 --port 4040
ERROR 1105 (HY000): #07000MySQLProxy Lua script failed to load. Check the error log.
解決:
[root@zyl lua]# pkillmysql-proxy
[root@zyl lua]# cp./mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua/opt/mysql-proxy/lib/mysql-proxy/lua/
[root@zyl lua]# vim/opt/mysql-proxy/init.d/mysql-proxy
更改腳本:
檢查這個(gè)腳本:--proxy-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua"
-------------------------------------------------------------------------------------------
3.
[root@zyl lua]# mysql -u zyl -p -h 192.168.21.139 --port 4040
注:因?yàn)閞w-splitting.lua腳本默認(rèn)有4個(gè)鏈接才啟用分離;所以多開啟幾個(gè)終端;多測試幾下;你也可以去修改里面的相關(guān)值;
改rw-splitting.lua讀寫分離腳本如下參數(shù):
4.編譯glib時(shí)報(bào)錯(cuò)
configure: error:
*** You must have either havegettext support in your C library, or use the
*** GNU gettext library.(http://www.gnu.org/software/gettext/gettext.html
解決:
[root@zyl glib-2.22.5]# yum install -y gettext
-------------------------------------------------------------------------------------------
5.
[root@zyl conf]#/application/mysql-proxy/bin/mysql-proxy--defaults-file=/application/mysql-proxy/conf/mysql-proxy.cnf
2016-09-02 22:28:37: (critical)mysql-proxy-cli.c:326: loading config from'/application/mysql-proxy/conf/mysql-proxy.cnf' failed: permissions of/application/mysql-proxy/conf/mysql-proxy.cnf aren't secure (0660or stricter required)
2016-09-02 22:28:37: (message)Initiating shutdown, requested from mysql-proxy-cli.c:328
2016-09-02 22:28:37: (message) shuttingdown normally, exit code is: 1
解決:
[root@zyl mysql-proxy]# chmod0660 mysql-proxy.cnf
看了以上關(guān)于mysql-proxy如何實(shí)現(xiàn)數(shù)據(jù)庫讀寫分離,希望能給大家在實(shí)際運(yùn)用中帶來一定的幫助。本文由于篇幅有限,難免會有不足和需要補(bǔ)充的地方,如有需要更加專業(yè)的解答,可在官網(wǎng)聯(lián)系我們的24小時(shí)售前售后,隨時(shí)幫您解答問題的。
文章標(biāo)題:mysql-proxy如何實(shí)現(xiàn)數(shù)據(jù)庫讀寫分離
文章網(wǎng)址:http://muchs.cn/article16/geeddg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供營銷型網(wǎng)站建設(shè)、電子商務(wù)、域名注冊、靜態(tài)網(wǎng)站、微信公眾號、品牌網(wǎng)站設(shè)計(jì)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)