读写分离mysql数据库mariadb_MariaDB数据库读写分离实现（一）：mysql-proxy的使用
一.mysql-proxy簡介
MySQL-Proxy是處在你的MySQL數(shù)據(jù)庫客戶和服務(wù)端之間的程序，它還支持嵌入性腳本語言Lua。這個代理可以用來分析、監(jiān)控和變換(transform)通信數(shù)據(jù)，它支持非常廣泛的使用場景：
負載平衡和故障轉(zhuǎn)移處理
查詢分析和日志
SQL宏(SQL macros)
查詢重寫(query rewriting)
執(zhí)行shell命令
MySQL-Proxy更強大的一項功能是實現(xiàn)“讀寫分離(Read/Write Splitting)”。基本的原理是讓主數(shù)據(jù)庫處理事務(wù)性查詢，而從數(shù)據(jù)庫處理SELECT查詢。數(shù)據(jù)庫復(fù)制被用來把事務(wù)性查詢導致的變更同步到集群中的從數(shù)據(jù)庫。
簡單的說，MySQL Proxy就是一個連接池，負責將前臺應(yīng)用的連接請求轉(zhuǎn)發(fā)給后臺的數(shù)據(jù)庫，并且通過使用lua腳本，可以實現(xiàn)復(fù)雜的連接控制和過濾，從而實現(xiàn)讀寫分離和負載平衡。對于應(yīng)用來說，MySQL Proxy是完全透明的，應(yīng)用則只需要連接到MySQL Proxy的監(jiān)聽端口即可。當然，這樣proxy機器可能成為單點失效，但完全可以使用多個proxy機器做為冗余，在應(yīng)用服務(wù)器的連接池配置中配置到多個proxy的連接參數(shù)即可。
其實現(xiàn)如圖所示：
二.基本環(huán)境介紹，
主數(shù)據(jù)庫服務(wù)器：172.16.31.20，MariaDB已經(jīng)安裝
從數(shù)據(jù)庫服務(wù)器：172.16.31.21，MariaDB已經(jīng)安裝
主從復(fù)制架構(gòu)配置完畢，實現(xiàn)了主從復(fù)制；詳細配置參考博客：
http://sohudrgon.blog.51cto.com/3088108/1608207
Mysql-proxy讀寫分離服務(wù)器節(jié)點：172.16.31.22，未配置；
規(guī)劃架構(gòu)如圖：
三.mysql-proxy讀寫分離實現(xiàn)；
1.安裝mysql-proxy軟件
如果你有epel源，epel源收錄了mysql-proxy的軟件包，我們可以使用yum安裝mysql-proxy，省去了編譯安裝的麻煩；但是EPEL源將讀寫分離的LUN腳本給去掉了，我們還需要從源碼包中就讀寫分離腳本復(fù)制出來；
我這里提供mysql-proxy的下載，還提供一個讀寫分離的lun腳本文件：
http://down.51cto.com/data/1978347
下面就開始進行mysql-proxy的安裝
查看yum源中的mysql-proxy包：[root@node5?~]#?yum?list?all?mysql-proxy
Loaded?plugins:?fastestmirror,?refresh-packagekit,?security
base?????????????????????????????????????????????????????|?3.2?kB?????00:00
base/primary_db??????????????????????????????????????????|?3.3?MB?????00:00
epel?????????????????????????????????????????????????????|?3.0?kB?????00:00
epel/primary_db??????????????????????????????????????????|?7.2?MB?????00:00
extra????????????????????????????????????????????????????|?2.9?kB?????00:00
extra/primary_db?????????????????????????????????????????|?1.1?MB?????00:00
Available?Packages
mysql-proxy.i686?????????????????????????0.8.5-1.el6????????????????????????epel
mysql-proxy.x86_64???????????????????????0.8.5-1.el6????????????????????????epel
安裝mysql-proxy包：[root@node5?~]#?yum?install?-y?mysql-proxy
我們查看epel源的mysql-proxy包提供的lun腳本：[root@node5?~]#?ls?/usr/share/doc/mysql-proxy-0.8.5/examples/
tutorial-basic.lua??????tutorial-prep-stmts.lua??tutorial-states.lua
tutorial-constants.lua??tutorial-query-time.lua??tutorial-tokenize.lua
tutorial-inject.lua?????tutorial-resultset.lua???tutorial-union.lua
tutorial-keepalive.lua??tutorial-rewrite.lua?????tutorial-warnings.lua
tutorial-monitor.lua????tutorial-routing.lua
tutorial-packets.lua????tutorial-scramble.lua
可以發(fā)現(xiàn)沒有rw-splitting.lua 這個讀寫分離腳本，我們?nèi)ハ螺d一個同版本的源碼包，將其中的讀寫分離腳本拷貝到指定目錄：[root@node5?~]#?tar?xf?mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@node5?~]#?ls?mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/
active-queries.lua???????ro-balance.lua???????????tutorial-resultset.lua
active-transactions.lua??ro-pooling.lua???????????tutorial-rewrite.lua
admin-sql.lua????????????rw-splitting.lua?????????tutorial-routing.lua
analyze-query.lua????????tutorial-basic.lua???????tutorial-scramble.lua
auditing.lua?????????????tutorial-constants.lua???tutorial-states.lua
commit-obfuscator.lua????tutorial-inject.lua??????tutorial-tokenize.lua
commit-obfuscator.msc????tutorial-keepalive.lua???tutorial-union.lua
COPYING??????????????????tutorial-monitor.lua?????tutorial-warnings.lua
histogram.lua????????????tutorial-packets.lua?????xtab.lua
load-multi.lua???????????tutorial-prep-stmts.lua
README???????????????????tutorial-query-time.lua
[root@node5?~]#?ls?/usr/lib64/mysql-proxy/lua/proxy/
auto-config.lua??balance.lua??commands.lua??parser.lua??test.lua??tokenizer.lua
[root@node5?~]#?cp?mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua?/usr/lib64/mysql-proxy/lua/
[root@node5?~]#?ls?/usr/lib64/mysql-proxy/lua/
admin.lua???glib2.so??lpeg.so???posix.so??rw-splitting.lua
chassis.so??lfs.so????mysql.so??proxy
2.配置mysql-proxy讀寫分離[root@node5?~]#?vim?/etc/mysql-proxy.cnf
[mysql-proxy]
daemon?=?true
pid-file?=?/var/run/mysql-proxy.pid
log-file?=?/var/log/mysql-proxy.log
log-level?=?debug
max-open-files?=?1024
plugins?=?admin,proxy
user?=?mysql-proxy
#
#代理配置
#Proxy?Configuration
#代理的地址和端口
proxy-address?=?0.0.0.0:3306
#后端的讀寫mysql節(jié)點服務(wù)器
proxy-backend-addresses?=?172.16.31.20:3306
#后端的只讀mysql服務(wù)器節(jié)點；
proxy-read-only-backend-addresses?=?172.16.31.21:3306
#實現(xiàn)讀寫分離的lun腳本配置文件；
proxy-lua-script?=?/usr/lib64/mysql-proxy/lua/rw-splitting.lua
#proxy-skip-profiling?=?true
#
#管理配置
#?Admin?Configuration
#admin-address?=?0.0.0.0:4041
admin-lua-script?=?/usr/lib64/mysql-proxy/lua/admin.lua
admin-username?=?admin
admin-password?=?admin
3.配置完成啟動mysql-proxy服務(wù)器：[root@node5?~]#?service?mysql-proxy?start
Starting?mysql-proxy:??????????????????????????????????????[??OK??]
查看監(jiān)聽端口：[root@node5?~]#?ss?-tunl?|grep?tcp
tcp????LISTEN?????0??????128???????????????????:::22???????????????????:::*
tcp????LISTEN?????0??????128????????????????????*:22????????????????????*:*
tcp????LISTEN?????0??????128????????????127.0.0.1:631???????????????????*:*
tcp????LISTEN?????0??????128??????????????????::1:631??????????????????:::*
tcp????LISTEN?????0??????100??????????????????::1:25???????????????????:::*
tcp????LISTEN?????0??????100????????????127.0.0.1:25????????????????????*:*
tcp????LISTEN?????0??????128????????????127.0.0.1:6010??????????????????*:*
tcp????LISTEN?????0??????128??????????????????::1:6010?????????????????:::*
tcp????LISTEN?????0??????128????????????????????*:54181?????????????????*:*
tcp????LISTEN?????0??????128????????????????????*:4041??????????????????*:*
tcp????LISTEN?????0??????128????????????????????*:3306??????????????????*:*
tcp????LISTEN?????0??????128???????????????????:::111??????????????????:::*
tcp????LISTEN?????0??????128????????????????????*:111???????????????????*:*
tcp????LISTEN?????0??????128???????????????????:::49776????????????????:::*
3306代理端口和管理端口4041都是啟動的；
5.主從服務(wù)器節(jié)點需要授權(quán)一個遠程連接允許mysql-proxy服務(wù)器訪問后端的主從服務(wù)器節(jié)點：
在master節(jié)點node3上授權(quán)即可：MariaDB?[hellodb]>?grant?all?on?*.*?to?'root'@'172.16.%.%'?identified?by?'oracle';
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[hellodb]>?flush?privileges;
Query?OK,?0?rows?affected?(0.00?sec)
我們在node5節(jié)點通過這個節(jié)點的代理訪問數(shù)據(jù)庫：[root@node5?~]#?mysql?-uroot?-h172.16.31.22?-poracle?-e?'show?databases';
+--------------------+
|?Database???????????|
+--------------------+
|?binlog?????????????|
|?hellodb????????????|
|?information_schema?|
|?mysql??????????????|
|?performance_schema?|
|?relaylog???????????|
|?test???????????????|
+--------------------+
可以發(fā)現(xiàn)數(shù)據(jù)庫是后端節(jié)點的數(shù)據(jù)庫；
6.讀寫分離測試：
讀寫分離測試我們先查看一下管理端口：[root@node5?~]#?mysql?-uadmin?-padmin?-P4041?-h172.16.31.22
Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g.
Your?MySQL?connection?id?is?1
Server?version:?5.0.99-agent-admin
Copyright?(c)?2000,?2013,?Oracle?and/or?its?affiliates.?All?rights?reserved.
Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its
affiliates.?Other?names?may?be?trademarks?of?their?respective
owners.
Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear?the?current?input?statement.
#可以發(fā)現(xiàn)主節(jié)點172.16.31.20是讀寫數(shù)據(jù)庫服務(wù)器節(jié)點，狀態(tài)是up，正常；
#而172.16.31.21是只讀數(shù)據(jù)庫服務(wù)器節(jié)點，但是狀態(tài)未知，我們需要去讀取一些后端只讀服務(wù)器的數(shù)據(jù)；
mysql>?select?*?from?backends;
+-------------+-------------------+---------+------+------+-------------------+
|?backend_ndx?|?address???????????|?state???|?type?|?uuid?|?connected_clients?|
+-------------+-------------------+---------+------+------+-------------------+
|???????????1?|?172.16.31.20:3306?|?up??????|?rw???|?NULL?|?????????????????0?|
|???????????2?|?172.16.31.21:3306?|?unknown?|?ro???|?NULL?|?????????????????0?|
+-------------+-------------------+---------+------+------+-------------------+
2?rows?in?set?(0.00?sec)
我們退出管理端口去執(zhí)行一些select語句：[root@node5?~]#?mysql?-uroot?-h172.16.31.22?-poracle?-e?'select?*?from?hellodb.teachers';
+-----+---------------+-----+--------+
|?TID?|?Name??????????|?Age?|?Gender?|
+-----+---------------+-----+--------+
|???1?|?Song?Jiang????|??45?|?M??????|
|???2?|?Zhang?Sanfeng?|??94?|?M??????|
|???3?|?Miejue?Shitai?|??77?|?F??????|
|???4?|?Lin?Chaoying??|??93?|?F??????|
+-----+---------------+-----+--------+
多執(zhí)行一些select語句，不同的最好；
執(zhí)行完成后去查看管理端口狀態(tài)：[root@node5?~]#?mysql?-uadmin?-padmin?-P?4041?-h172.16.31.22
Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g.
Your?MySQL?connection?id?is?1
Server?version:?5.0.99-agent-admin
Copyright?(c)?2000,?2013,?Oracle?and/or?its?affiliates.?All?rights?reserved.
Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its
affiliates.?Other?names?may?be?trademarks?of?their?respective
owners.
Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear?the?current?input?statement.
mysql>?select?*?from?backends;
+-------------+-------------------+-------+------+------+-------------------+
|?backend_ndx?|?address???????????|?state?|?type?|?uuid?|?connected_clients?|
+-------------+-------------------+-------+------+------+-------------------+
|???????????1?|?172.16.31.20:3306?|?up????|?rw???|?NULL?|?????????????????0?|
|???????????2?|?172.16.31.21:3306?|?up????|?ro???|?NULL?|?????????????????0?|
+-------------+-------------------+-------+------+------+-------------------+
2?rows?in?set?(0.00?sec)
全部up了，狀態(tài)正常；
至此，基于mysql-proxy實現(xiàn)數(shù)據(jù)庫讀寫分類的實驗就實現(xiàn)了。
總結(jié)
以上是生活随笔為你收集整理的读写分离mysql数据库mariadb_MariaDB数据库读写分离实现（一）：mysql-proxy的使用的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql每秒57000_MySQL 性
- 下一篇： mysql autoextend_inn