mysql-proxy實作讀寫分離腳本

将以下腳本儲存為/etc/init.d/mysql-proxy ，賦予權限

chmod 755 /etc/init.d/mysql-proxy

<code># mysql-proxy This script starts and stops the mysql-proxy daemon</code>

<code># chkconfig: - 78 30</code>

<code># processname: mysql-proxy</code>

<code># description: mysql-proxy is a proxy daemon for mysql</code>

<code># Source function library.</code>

<code>. </code><code>/etc/rc</code><code>.d</code><code>/init</code><code>.d</code><code>/functions</code>

<code>prog=</code><code>"/usr/local/mysql-proxy/bin/mysql-proxy"</code>

<code># Source networking configuration.</code>

<code>if</code> <code>[ -f </code><code>/etc/sysconfig/network</code> <code>]; </code><code>then</code>

<code> </code><code>. </code><code>/etc/sysconfig/network</code>

<code># Check that networking is up.</code>

<code>[ ${NETWORKING} = </code><code>"no"</code> <code>] && </code><code>exit</code> <code>0</code>

<code># Set default mysql-proxy configuration.</code>

<code>ADMIN_USER=</code><code>"admin"</code>

<code>ADMIN_PASSWD=</code><code>"admin"</code>

<code>ADMIN_LUA_SCRIPT=</code><code>"/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"</code>

<code>PROXY_OPTIONS=</code><code>"--daemon"</code>

<code>PROXY_PID=</code><code>/var/run/mysql-proxy</code><code>.pid</code>

<code>PROXY_USER=</code><code>"mysql-proxy"</code>

<code># Source mysql-proxy configuration.</code>

<code>if</code> <code>[ -f </code><code>/etc/sysconfig/mysql-proxy</code> <code>]; </code><code>then</code>

<code> </code><code>. </code><code>/etc/sysconfig/mysql-proxy</code>

<code>RETVAL=0</code>

<code>start() {</code>

<code> </code><code>echo</code> <code>-n $</code><code>"Starting $prog: "</code>

<code> </code><code>daemon $prog $PROXY_OPTIONS --pid-</code><code>file</code><code>=$PROXY_PID --proxy-address=</code><code>"$PROXY_ADDRESS"</code> <code>--user=$PROXY_USER --admin-username=</code><code>"$ADMIN_USER"</code> <code>--admin-lua-script=</code><code>"$ADMIN_LUA_SCRIPT"</code> <code>--admin-password=</code><code>"$ADMIN_PASSWORD"</code>

<code> </code><code>RETVAL=$?</code>

<code> </code><code>if</code> <code>[ $RETVAL -</code><code>eq</code> <code>0 ]; </code><code>then</code>

<code> </code><code>touch</code> <code>/var/lock/subsys/mysql-proxy</code>

<code> </code><code>echo</code> <code>-n $</code><code>"Stopping $prog: "</code>

<code> </code><code>killproc -p $PROXY_PID -d 3 $prog</code>

<code> </code><code>rm</code> <code>-f </code><code>/var/lock/subsys/mysql-proxy</code>

<code> </code><code>rm</code> <code>-f $PROXY_PID</code>

<code># See how we were called.</code>

<code> </code><code>start)</code>

<code> </code><code>start</code>

<code> </code><code>restart)</code>

<code> </code><code>condrestart|try-restart)</code>

<code> </code><code>if</code> <code>status -p $PROXY_PIDFILE $prog >&</code><code>/dev/null</code><code>; </code><code>then</code>

<code> </code><code>start</code>

<code> </code><code>status)</code>

<code> </code><code>status -p $PROXY_PID $prog</code>

<code> </code><code>echo</code> <code>"Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"</code>

<code> </code><code>RETVAL=1</code>

<code>exit</code> <code>$RETVAL</code>

2.為服務腳本提供配置檔案/etc/sysconfig/mysql-proxy，内容如下所示：

<code># Options for mysql-proxy </code>

<code>ADMIN_PASSWORD=</code><code>"admin"</code>

<code>ADMIN_ADDRESS=</code><code>""</code>

<code>PROXY_ADDRESS=</code><code>""</code>

<code>PROXY_OPTIONS=</code><code>"--daemon --log-level=info --log-use-syslog"</code>

#注意最後一行，需要根據實際生産場景進行修改，比如;

PROXY_OPTIONS="--daemon --log-level=info --log-file=/var/log/mysql-proxy.log --plugins=proxy

--plugins=admin --proxy-backend-addresses=192.168.141.101:3306 --proxy-read-only-backend-ad

dresses=192.168.141.102:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy

/rw-splitting.lua"

3.複制如下内容建立admin.lua檔案，将其儲存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目錄中:

<code>--[[ $%BEGINLICENSE%$</code>

<code> </code><code>This program is </code><code>free</code> <code>software; you can redistribute it and</code><code>/or</code>

<code> </code><code>modify it under the terms of the GNU General Public License as</code>

<code> </code><code>published by the Free Software Foundation; version 2 of the</code>

<code> </code><code>License.</code>

<code> </code><code>This program is distributed </code><code>in</code> <code>the hope that it will be useful,</code>

<code> </code><code>but WITHOUT ANY WARRANTY; without even the implied warranty of</code>

<code> </code><code>MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the</code>

<code> </code><code>GNU General Public License </code><code>for</code> <code>more</code> <code>details.</code>

<code> </code><code>You should have received a copy of the GNU General Public License</code>

<code> </code><code>along with this program; </code><code>if</code> <code>not, write to the Free Software</code>

<code> </code><code>Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA</code>

<code> </code><code>$%ENDLICENSE%$ --]]</code>

<code> </code><code>function</code> <code>set_error(errmsg) </code>

<code> </code><code>proxy.response = {</code>

<code> </code><code>type</code> <code>= proxy.MYSQLD_PACKET_ERR,</code>

<code> </code><code>errmsg = errmsg or </code><code>"error"</code>

<code>function</code> <code>read_query(packet)</code>

<code> </code><code>if</code> <code>packet:byte() ~= proxy.COM_QUERY </code><code>then</code>

<code> </code><code>set_error(</code><code>"[admin] we only handle text-based queries (COM_QUERY)"</code><code>)</code>

<code> </code><code>return</code> <code>proxy.PROXY_SEND_RESULT</code>

<code> </code><code>local</code> <code>query = packet:sub(2)</code>

<code> </code><code>local</code> <code>rows = { }</code>

<code> </code><code>local</code> <code>fields = { }</code>

<code> </code><code>if</code> <code>query:lower() == </code><code>"select * from backends"</code> <code>then</code>

<code> </code><code>fields = { </code>

<code> </code><code>{ name = </code><code>"backend_ndx"</code><code>, </code>

<code> </code><code>type</code> <code>= proxy.MYSQL_TYPE_LONG },</code>

<code> </code><code>{ name = </code><code>"address"</code><code>,</code>

<code> </code><code>type</code> <code>= proxy.MYSQL_TYPE_STRING },</code>

<code> </code><code>{ name = </code><code>"state"</code><code>,</code>

<code> </code><code>{ name = </code><code>"connected_clients"</code><code>, </code>

<code> </code><code>for</code> <code>i = 1, </code><code>#proxy.global.backends do</code>

<code> </code><code>local</code> <code>states = {</code>

<code> </code><code>"unknown"</code><code>,</code>

<code> </code><code>local</code> <code>types = {</code>

<code> </code><code>local</code> <code>b = proxy.global.backends[i]</code>

<code> </code><code>b.dst.name, -- configured backend address</code>

<code> </code><code>states[b.state + 1], -- the C-</code><code>id</code> <code>is pushed down starting at 0</code>

<code> </code><code>types[b.</code><code>type</code> <code>+ 1], -- the C-</code><code>id</code> <code>is pushed down starting at 0</code>

<code> </code><code>b.uuid, -- the MySQL Server's UUID </code><code>if</code> <code>it is managed</code>

<code> </code><code>b.connected_clients -- currently connected clients</code>

<code> </code><code>elseif query:lower() == </code><code>"select * from help"</code> <code>then</code>

<code> </code><code>{ name = </code><code>"command"</code><code>, </code>

<code> </code><code>{ name = </code><code>"description"</code><code>, </code>

<code> </code><code>rows[</code><code>#rows + 1] = { "SELECT * FROM help", "shows this help" }</code>

<code> </code><code>rows[</code><code>#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }</code>

<code> </code><code>set_error(</code><code>"use 'SELECT * FROM help' to see the supported commands"</code><code>)</code>

<code> </code><code>type</code> <code>= proxy.MYSQLD_PACKET_OK,</code>

<code> </code><code>resultset = {</code>

<code> </code><code>fields = fields,</code>

<code> </code><code>return</code> <code>proxy.PROXY_SEND_RESULT</code>

本文轉自limingyu0312 51CTO部落格，原文連結：http://blog.51cto.com/limingyu/1933927，如需轉載請自行聯系原作者

mysql-proxy實作讀寫分離腳本

繼續閱讀

mysql備份腳本

mysql--用腳本實作分庫分表備份

mysql 備份腳本

Mysql備份腳本

MySQL備份腳本

MySQL分庫分表備份腳本

找出bas_Material不存在在 erpdata_zl.dbo.bas_Material中的記錄, CheckSum

【基礎】mysql資料庫（key_buffer_size）

Proxy實作MySQL讀寫分離

mysql常用指令和腳本mysql常用指令和腳本

MySQL分庫備份腳本

建構高性能web之路------mysql讀寫分離實戰

MySQL主從複制讀寫分離，看這篇就夠了！思維導圖前言為什麼使用主從複制、讀寫分離主從複制的原理如何實作主從複制讀寫分離缺點

ECS如何選擇正确的MySQL用戶端？檢視ECS系統類型及版本選擇适合Centos系統的MySQL用戶端下載下傳MySQL用戶端版本官網下載下傳

DBLinq mysql輔助腳本

Springboot + Mysql8 讀寫分離實戰