mysql mysql-proxy實現(xiàn)讀寫分離

MySQL讀寫分離是指讓master處理寫操作氓癌，讓slave處理讀操作号坡，非常適用于讀操作量比較大的場景，可減輕master的壓力。
使用mysql-proxy實現(xiàn)mysql的讀寫分離柱徙，mysql-proxy實際上是作為后端mysql主從服務(wù)器的代理缓屠，它直接接受客戶端的請求，對SQL語句進行分析护侮，判斷出是讀操作還是寫操作敌完，然后分發(fā)至對應(yīng)的mysql服務(wù)器上。

環(huán)境準備：

三臺主機：
192.168.20.10 master
192.168.20.11 slave
192.168.20.12 proxy

第一：192.168.20.10羊初、11兩臺mysql搭建主從復(fù)制架構(gòu)滨溉。
第二、在proxy機器上安裝配置mysql-proxy长赞，實現(xiàn)master/slave架構(gòu)讀寫分離晦攒。
1、下載mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
2得哆、安裝mysql-proxy
[root@proxy install]# tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
mysql-proxy-0.8.5-linux-el6-x86-64bit/
mysql-proxy-0.8.5-linux-el6-x86-64bit/bin/
......
[root@proxy install]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/
[root@proxy local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[root@proxy local]# useradd -r mysql-proxy
[root@proxy local]# id mysql-proxy
uid=493(mysql-proxy) gid=486(mysql-proxy) groups=486(mysql-proxy)

3脯颜、添加mysql-proxy自啟動
[root@proxy local]# cd /etc/init.d/
[root@proxy init.d]# vi mysql-proxy

!/bin/bash

mysql-proxy This script starts and stops the mysql-proxy daemon

chkconfig: - 78 30

processname: mysql-proxy

description: mysql-proxy is a proxy daemon for mysql

Source function library.

. /etc/rc.d/init.d/functions
prog="/usr/local/mysql-proxy/bin/mysql-proxy"

Source networking configuration.

if [ -f /etc/sysconfig/network ]; then
. /etc/sysconfig/network
fi

Check that networking is up.

[ ${NETWORKING} = "no" ] && exit 0

Set default mysql-proxy configuration.

ADMIN_USER="admin"
ADMIN_PASSWD="admin"
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_OPTIONS="--daemon"
PROXY_PID=/var/run/mysql-proxy.pid
PROXY_USER="mysql-proxy"

Source mysql-proxy configuration.

if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
RETVAL=0
start() {
echo -n $"Starting$ prog: "
daemon $prog$ PROXY_OPTIONS --pid-file= $PROXY_PID --proxy-address="$ PROXY_ADDRESS" --user= $PROXY_USER --admin-username="$ ADMIN_USER" --admin-lua-script=" $ADMIN_LUA_SCRIPT" --admin-password="$ ADMIN_PASSWORD"
RETVAL= $? echo if [$ RETVAL -eq 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
}
stop() {
echo -n $"Stopping$ prog: "
killproc -p $PROXY_PID -d 3$ prog
RETVAL= $? echo if [$ RETVAL -eq 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
}

See how we were called.

case " $1" in start) start ;; stop) stop ;; restart) stop start ;; condrestart|try-restart) if status -p$ PROXY_PIDFILE $prog >&/master/null; then stop start fi ;; status) status -p$ PROXY_PID $prog ;; *) echo "Usage:$ 0 {start|stop|restart|reload|status|condrestart|try-restart}"
RETVAL=1
;;
esac
exit $RETVAL

[root@proxy init.d]# chmod +x /etc/init.d/mysql-proxy
[root@proxy init.d]# chkconfig --add mysql-proxy
[root@proxy init.d]# vim /etc/sysconfig/mysql-proxy

Options for mysql-proxy

ADMIN_USER="admin"
ADMIN_PASSWORD="admin"
ADMIN_ADDRESS=""
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_ADDRESS=""
PROXY_USER="mysql-proxy"
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.20.10:3306 --proxy-read-only-backend-addresses=192.168.20.11:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

[root@proxy init.d]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua
function set_error(errmsg)
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = errmsg or "error"
}
end
function read_query(packet)
if packet:byte() ~= proxy.COM_QUERY then
set_error("[admin] we only handle text-based queries (COM_QUERY)")
return proxy.PROXY_SEND_RESULT
end
local query = packet:sub(2)
local rows = { }
local fields = { }
if query:lower() == "select * from backends" then
fields = {
{ name = "backend_ndx",
type = proxy.MYSQL_TYPE_LONG },
{ name = "address",
type = proxy.MYSQL_TYPE_STRING },
{ name = "state",
type = proxy.MYSQL_TYPE_STRING },
{ name = "type",
type = proxy.MYSQL_TYPE_STRING },
{ name = "uuid",
type = proxy.MYSQL_TYPE_STRING },
{ name = "connected_clients",
type = proxy.MYSQL_TYPE_LONG },
}

for i = 1, #proxy.global.backends do
local states = {
"unknown",
"up",
"down"
}
local types = {
"unknown",
"rw",
"ro"
}
local b = proxy.global.backends[i]

rows[#rows + 1] = {
i,
b.dst.name, -- configured backend address
states[b.state + 1], -- the C-id is pushed down starting at 0
types[b.type + 1], -- the C-id is pushed down starting at 0
b.uuid, -- the MySQL Server's UUID if it is managed
b.connected_clients -- currently connected clients
}
end
elseif query:lower() == "select * from help" then
fields = {
{ name = "command",
type = proxy.MYSQL_TYPE_STRING },
{ name = "description",
type = proxy.MYSQL_TYPE_STRING },
}
rows[#rows + 1] = { "SELECT * FROM help", "shows this help" }
rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }
else
set_error("use 'SELECT * FROM help' to see the supported commands")
return proxy.PROXY_SEND_RESULT
end

proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return proxy.PROXY_SEND_RESULT
end

4、啟動proxy服務(wù)：
[root@proxy bin]# service mysql-proxy start
Starting /usr/local/mysql-proxy/bin/mysql-proxy: [ OK ]

[root@proxy bin]# ss -nalp|grep mysql
LISTEN 0 128 *:4041 : users:(("mysql-proxy",28520,11))
LISTEN 0 128 *:3306 : users:(("mysql-proxy",28520,10))

[root@proxy bin]# mysql -uadmin -padmin -h192.168.20.12 --port=4041
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 | 192.168.20.10:3306 | unknown | rw | NULL | 0 |
| 2 | 192.168.20.11:3306 | unknown | ro | NULL | 0 |
+-------------+-----------------+---------+------+------+-------------------+
2 rows in set (0.00 sec)
配置完成贩据。

第三栋操、測試
[root@master ~]# su – mysql
-bash-4.1$ mysql -uroot -p12345
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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>
mysql>
mysql> GRANT ALL ON . TO 'alex'@'192.168.20.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

主從機器上：
tcpdump -i eth0 -nn -XX ip dst 192.168.20.10 and tcp dst port 3306
tcpdump -i eth0 -nn -XX ip dst 192.168.20.11 and tcp dst port 3306
進行抓包觀察。

PROXY機器上操作：
[root@proxy bin]# mysql -ualex -p123456 -h192.168.20.12 --port=3306 （這個地方連接3306）
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.6.37-log MySQL Community Server (GPL)
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>create database alex;
mysql>use mysql;
mysql>select * from user \G

[root@proxy ~]# mysql -uadmin -padmin -h192.168.20.12 --port=4041
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

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 | 192.168.20.10:3306 | up | rw | NULL | 0 |
| 2 | 192.168.20.11:3306 | up | ro | NULL | 0 |
+-------------+-----------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)

成功實現(xiàn)讀寫分離饱亮。

作者：crpp0902
來源：CSDN
原文：https://blog.csdn.net/crpp0902/article/details/76085155
版權(quán)聲明：本文為博主原創(chuàng)文章矾芙，轉(zhuǎn)載請附上博文鏈接！

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末近上，一起剝皮案震驚了整個濱河市剔宪，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌壹无，老刑警劉巖歼跟，帶你破解...
沈念sama閱讀 210,835評論 6贊 490
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異格遭，居然都是意外死亡哈街，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 89,900評論 2贊 383
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門拒迅，熙熙樓的掌柜王于貴愁眉苦臉地迎上來骚秦，“玉大人，你說我怎么就攤上這事璧微∽鞴浚” “怎么了？”我有些...
開封第一講書人閱讀 156,481評論 0贊 345
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵前硫，是天一觀的道長胞得。經(jīng)常有香客問我，道長屹电，這世上最難降的妖魔是什么阶剑？我笑而不...
開封第一講書人閱讀 56,303評論 1贊 282
?港島之戀（遺憾婚禮）
正文為了忘掉前任跃巡，我火速辦了婚禮，結(jié)果婚禮上牧愁，老公的妹妹穿的比我還像新娘素邪。我一直安慰自己，他們只是感情好猪半，可當我...
茶點故事閱讀 65,375評論 5贊 384
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布兔朦。她就那樣靜靜地躺著，像睡著了一般磨确。火紅的嫁衣襯著肌膚如雪沽甥。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 49,729評論 1贊 289
城市分裂傳說
那天乏奥，我揣著相機與錄音摆舟，去河邊找鬼。笑死英融，一個胖子當著我的面吹牛盏檐，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播驶悟，決...
沈念sama閱讀 38,877評論 3贊 404
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼胡野，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了痕鳍？” 一聲冷哼從身側(cè)響起硫豆，我...
開封第一講書人閱讀 37,633評論 0贊 266
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎笼呆，沒想到半個月后熊响，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 44,088評論 1贊 303
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡诗赌，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 36,443評論 2贊 326
?白月光啟示錄
正文我和宋清朗相戀三年汗茄，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片铭若。...
茶點故事閱讀 38,563評論 1贊 339
活死人
序言：一個原本活蹦亂跳的男人離奇死亡洪碳，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出叼屠，到底是詐尸還是另有隱情瞳腌，我是刑警寧澤，帶...
沈念sama閱讀 34,251評論 4贊 328
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布镜雨，位于F島的核電站嫂侍，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜挑宠，卻給世界環(huán)境...
茶點故事閱讀 39,827評論 3贊 312
男人毒藥：我在死后第九天來索命
文/蒙蒙一菲盾、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧痹栖，春花似錦亿汞、人聲如沸瞭空。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,712評論 0贊 21
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽咆畏。三九已至南捂，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間旧找，已是汗流浹背溺健。一陣腳步聲響...
開封第一講書人閱讀 31,943評論 1贊 264
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留钮蛛，地道東北人鞭缭。一個月前我還...
沈念sama閱讀 46,240評論 2贊 360
代替公主和親
正文我出身青樓，卻偏偏與公主長得像魏颓，于是被迫代替她去往敵國和親岭辣。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當晚...
茶點故事閱讀 43,435評論 2贊 348

mysql mysql-proxy實現(xiàn)讀寫分離

!/bin/bash

mysql-proxy This script starts and stops the mysql-proxy daemon

chkconfig: - 78 30

processname: mysql-proxy

description: mysql-proxy is a proxy daemon for mysql

Source function library.

Source networking configuration.

Check that networking is up.

Set default mysql-proxy configuration.

Source mysql-proxy configuration.

See how we were called.

Options for mysql-proxy

成功實現(xiàn)讀寫分離饱亮。

推薦閱讀更多精彩內(nèi)容