MySQL Proxy for CentOS Configuration Guide

Update on 2011/07/17
如果碰到

2011-07-17 02:56:25: (critical) proxy-plugin.c:263: read_query_result() in /etc/mysql-proxy/proxy.lua tries to modify the resultset, but hasn’t asked to buffer it in proxy.query:append(…, { resultset_is_needed = true }). We ignore the change to the result-set.

這樣的問題的話, 修改 /etc/mysql-proxy/proxy.lua 的第 201 行, 把

proxy.queries:append(1, packet)

改成

proxy.queries:append(1, packet, {resultset_is_needed = true})

就可以了

一樣是個Run down, 中間碰到問題請找Google大神
基本準備三台機器, 兩台互為MySQL Server Master/Slave, 請參考MySQL Replication (Master-Slave) for CentOS Installation Guide(後文各稱為Master/Slave), 再參考MySQL-Proxy for CentOS 5 Installation Guide安裝好一台MySQL-Proxy(後文稱為Proxy), 然後進入設定程序.

首先相關目錄

[Proxy] # mkdir /etc/mysql-proxy/

後續的lua檔案會放至於此
接著設定成服務, 建立一個檔案/etc/init.d/mysql-proxy, 內容如下

#!/bin/sh
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: – 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon to mysql

# Source function library.
. /etc/rc.d/init.d/functions

PROXY_PATH=/usr/local/bin

prog=”mysql-proxy”

# Source networking configuration.
. /etc/sysconfig/network

# Check that networking is up.
[ ${NETWORKING} = “no” ] && exit 0

# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ] ; then
. /etc/sysconfig/mysql-proxy
fi

# Set default mysql-proxy configuration.
PROXY_PID=/var/run/mysql-proxy.pid

PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH

# By default it’s all good
RETVAL=0

# See how we were called.
case “$1″ in
start)
# Start daemon.
echo -n $”Starting $prog: ”
daemon $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS $ADMIN_PLUGIN_OPTIONS $PROXY_PLUGIN_OPTIONS
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
;;
stop)
# Stop daemons.
echo -n $”Stopping $prog: ”
killproc $prog
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
;;
restart)
$0 stop
sleep 3
$0 start
;;
condrestart)
[ -e /var/lock/subsys/mysql-proxy ] && $0 restart
;;
status)
status mysql-proxy
RETVAL=$?
;;
*)
echo “Usage: $0 {start|stop|restart|status|condrestart}”
RETVAL=1
;;
esac

exit $RETVAL

並且設定成可執行

[Proxy] # chmod a+x /etc/init.d/mysql-proxy
接著在/etc/sysconfig下建立服務啟動設定檔 /etc/sysconfig/mysql-proxy, 內容如下

# Options to mysql-proxy
# do not remove –daemon
LD_LIBRARY_PATH=”/usr/local/lib:$LD_LIBRARY_PATH”
LUA_PATH=”/usr/local/lib/mysql-proxy/lua/?.lua;$LUA_PATH”
LUA_CPATH=”/usr/local/lib/mysql-proxy/lua/?.so;$LUA_CPATH”
export LD_LIBRARY_PATH LUA_PATH LUA_CPATH

PROXY_OPTIONS=”–daemon”

ADMIN_PLUGIN_OPTIONS=”–admin-address=PROXY_IP:4401 –admin-username=admin –admin-password=admin_password –admin-lua-script=/etc/mysql-proxy/admin.lua”
PROXY_PLUGIN_OPTIONS=”–proxy-address=PROXY_IP:3306 –proxy-read-only-backend-addresses=SLAVE_IP:3306 –proxy-backend-addresses=MASTER_IP:3306 –proxy-lua-script=/etc/mysql-proxy/proxy.lua”

請把其中的 PROXY_IP, MASTER_IP, SLAVE_IP 改成各自的ip address, 並且變更admin使用者名稱及密碼為你想要的.
建立成系統服務

[Proxy] # /sbin/chkconfig –add mysql-proxy
[Proxy] # /sbin/chkconfig mysql-proxy on
到MASTER及SLAVE下建立一個用來連線使用的帳號密碼, 請注意為了Replication的完整性, 請考慮在SLAVE上將該帳號的權限鎖死為僅SELECT.
設定/etc/mysql-proxy/admin.lua, 內容如下

–[[ $%BEGINLICENSE%$
Copyright (c) 2008, 2009, Oracle and/or its affiliates. All rights reserved.

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; version 2 of the
License.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
02110-1301 USA

$%ENDLICENSE%$ –]]

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

這個檔案可以在/usr/local/lib/mysql-proxy/lua找到, 複製過去也行, 或者可以自己寫個lua script, 這篇不是LUA語言教學, 就略過不提.
設定/etc/mysql-proxy/proxy.lua, 內容如下

–[[ $%BEGINLICENSE%$
Copyright (C) 2007-2008 MySQL AB, 2008 Sun Microsystems, Inc

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 of the License.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

$%ENDLICENSE%$ –]]

—
— a flexible statement based load balancer with connection pooling
—
— * build a connection pool of min_idle_connections for each backend and maintain
— its size
— *
—
—

local commands = require(“proxy.commands”)
local tokenizer = require(“proxy.tokenizer”)
local lb = require(“proxy.balance”)
local auto_config = require(“proxy.auto-config”)

— config
—
— connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 4,
max_idle_connections = 8,

is_debug = false
}
end

—
— read/write splitting sends all non-transactional SELECTs to the slaves
—
— is_in_transaction tracks the state of the transactions
local is_in_transaction = false

— if this was a SELECT SQL_CALC_FOUND_ROWS … stay on the same connections
local is_in_select_calc_found_rows = false

—
— get a connection to a backend
—
— as long as we don’t have enough connections in the pool, create new connections
—
function connect_server()
local is_debug = proxy.global.config.rwsplit.is_debug
— make sure that we connect to each backend at least ones to
— keep the connections to the servers alive
—
— on read_query we can switch the backends again to another backend

if is_debug then
print()
print(“[connect_server] ” .. proxy.connection.client.address)
end

local rw_ndx = 0

— init all backends
for i = 1, #proxy.global.backends do
local s = proxy.global.backends[i]
local pool = s.pool — we don’t have a username yet, try to find a connections which is idling
local cur_idle = pool.users[“”].cur_idle_connections

pool.min_idle_connections = proxy.global.config.rwsplit.min_idle_connections
pool.max_idle_connections = proxy.global.config.rwsplit.max_idle_connections

if is_debug then
print(” [“.. i ..”].connected_clients = ” .. s.connected_clients)
print(” [“.. i ..”].pool.cur_idle = ” .. cur_idle)
print(” [“.. i ..”].pool.max_idle = ” .. pool.max_idle_connections)
print(” [“.. i ..”].pool.min_idle = ” .. pool.min_idle_connections)
print(” [“.. i ..”].type = ” .. s.type)
print(” [“.. i ..”].state = ” .. s.state)
end

— prefer connections to the master
if s.type == proxy.BACKEND_TYPE_RW and
s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle < pool.min_idle_connections then proxy.connection.backend_ndx = i break elseif s.type == proxy.BACKEND_TYPE_RO and s.state ~= proxy.BACKEND_STATE_DOWN and cur_idle < pool.min_idle_connections then proxy.connection.backend_ndx = i break elseif s.type == proxy.BACKEND_TYPE_RW and s.state ~= proxy.BACKEND_STATE_DOWN and rw_ndx == 0 then rw_ndx = i end end if proxy.connection.backend_ndx == 0 then if is_debug then print(" [" .. rw_ndx .. "] taking master as default") end proxy.connection.backend_ndx = rw_ndx end -- pick a random backend -- -- we someone have to skip DOWN backends -- ok, did we got a backend ? if proxy.connection.server then if is_debug then print(" using pooled connection from: " .. proxy.connection.backend_ndx) end -- stay with it return proxy.PROXY_IGNORE_RESULT end if is_debug then print(" [" .. proxy.connection.backend_ndx .. "] idle-conns below min-idle") end -- open a new connection end --- -- put the successfully authed connection into the connection pool -- -- @param auth the context information for the auth -- -- auth.packet is the packet function read_auth_result( auth ) if is_debug then print("[read_auth_result] " .. proxy.connection.client.address) end if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then -- auth was fine, disconnect from the server proxy.connection.backend_ndx = 0 elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then -- we received either a -- -- * MYSQLD_PACKET_ERR and the auth failed or -- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent print("(read_auth_result) ... not ok yet"); elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then -- auth failed end end --- -- read/write splitting function read_query( packet ) local is_debug = proxy.global.config.rwsplit.is_debug local cmd = commands.parse(packet) local c = proxy.connection.client local r = auto_config.handle(cmd) if r then return r end local tokens local norm_query -- looks like we have to forward this statement to a backend if is_debug then print("[read_query] " .. proxy.connection.client.address) print(" current backend = " .. proxy.connection.backend_ndx) print(" client default db = " .. c.default_db) print(" client username = " .. c.username) if cmd.type == proxy.COM_QUERY then print(" query = " .. cmd.query) end end if cmd.type == proxy.COM_QUIT then -- don't send COM_QUIT to the backend. We manage the connection -- in all aspects. proxy.response = { type = proxy.MYSQLD_PACKET_OK, } if is_debug then print(" (QUIT) current backend = " .. proxy.connection.backend_ndx) end return proxy.PROXY_SEND_RESULT end proxy.queries:append(1, packet) -- read/write splitting -- -- send all non-transactional SELECTs to a slave if not is_in_transaction and cmd.type == proxy.COM_QUERY then tokens = tokens or assert(tokenizer.tokenize(cmd.query)) local stmt = tokenizer.first_stmt_token(tokens) if stmt.token_name == "TK_SQL_SELECT" then is_in_select_calc_found_rows = false local is_insert_id = false for i = 1, #tokens do local token = tokens[i] -- SQL_CALC_FOUND_ROWS + FOUND_ROWS() have to be executed -- on the same connection -- print("token: " .. token.token_name) -- print(" val: " .. token.text) if not is_in_select_calc_found_rows and token.token_name == "TK_SQL_SQL_CALC_FOUND_ROWS" then is_in_select_calc_found_rows = true elseif not is_insert_id and token.token_name == "TK_LITERAL" then local utext = token.text:upper() if utext == "LAST_INSERT_ID" or utext == "@@INSERT_ID" then is_insert_id = true end end -- we found the two special token, we can't find more if is_insert_id and is_in_select_calc_found_rows then break end end -- if we ask for the last-insert-id we have to ask it on the original -- connection if not is_insert_id then local backend_ndx = lb.idle_ro() if backend_ndx > 0 then
proxy.connection.backend_ndx = backend_ndx
end
else
print(” found a SELECT LAST_INSERT_ID(), staying on the same backend”)
end
end
end

— no backend selected yet, pick a master
if proxy.connection.backend_ndx == 0 then
— we don’t have a backend right now
—
— let’s pick a master as a good default
—
proxy.connection.backend_ndx = lb.idle_failsafe_rw()
end

— by now we should have a backend
—
— in case the master is down, we have to close the client connections
— otherwise we can go on
if proxy.connection.backend_ndx == 0 then
return proxy.PROXY_SEND_QUERY
end

local s = proxy.connection.server

— if client and server db don’t match, adjust the server-side
—
— skip it if we send a INIT_DB anyway
if cmd.type ~= proxy.COM_INIT_DB and
c.default_db and c.default_db ~= s.default_db then
print(” server default db: ” .. s.default_db)
print(” client default db: ” .. c.default_db)
print(” syncronizing”)
proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db)
end

— send to master
if is_debug then
if proxy.connection.backend_ndx > 0 then
local b = proxy.global.backends[proxy.connection.backend_ndx]
print(” sending to backend : ” .. b.address);
print(” is_slave : ” .. tostring(b.type == proxy.BACKEND_TYPE_RO));
print(” server default db: ” .. s.default_db)
print(” server username : ” .. s.username)
end
print(” in_trans : ” .. tostring(is_in_transaction))
print(” in_calc_found : ” .. tostring(is_in_select_calc_found_rows))
print(” COM_QUERY : ” .. tostring(cmd.type == proxy.COM_QUERY))
end

return proxy.PROXY_SEND_QUERY
end

—
— as long as we are in a transaction keep the connection
— otherwise release it so another client can use it
function read_query_result( inj )
local is_debug = proxy.global.config.rwsplit.is_debug
local res = assert(inj.resultset)
local flags = res.flags

if inj.id ~= 1 then
— ignore the result of the USE
— the DB might not exist on the backend, what do do ?
—
if inj.id == 2 then
— the injected INIT_DB failed as the slave doesn’t have this DB
— or doesn’t have permissions to read from it
if res.query_status == proxy.MYSQLD_PACKET_ERR then
proxy.queries:reset()
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = “can’t change DB “.. proxy.connection.client.default_db ..
” to on slave ” .. proxy.global.backends[proxy.connection.backend_ndx].address
}

return proxy.PROXY_SEND_RESULT
end
end
return proxy.PROXY_IGNORE_RESULT
end

is_in_transaction = flags.in_trans
local have_last_insert_id = (res.insert_id and (res.insert_id > 0))

if not is_in_transaction and
not is_in_select_calc_found_rows and
not have_last_insert_id then
— release the backend
proxy.connection.backend_ndx = 0
elseif is_debug then
print(“(read_query_result) staying on the same backend”)
print(” in_trans : ” .. tostring(is_in_transaction))
print(” in_calc_found : ” .. tostring(is_in_select_calc_found_rows))
print(” have_insert_id : ” .. tostring(have_last_insert_id))
end
end

—
— close the connections if we have enough connections in the pool
—
— @return nil – close connection
— IGNORE_RESULT – store connection in the pool
function disconnect_client()
local is_debug = proxy.global.config.rwsplit.is_debug
if is_debug then
print(“[disconnect_client] ” .. proxy.connection.client.address)
end

— make sure we are disconnection from the connection
— to move the connection into the pool
proxy.connection.backend_ndx = 0
end

這個檔案在MySQL-Proxy的repository找的到, 是標準的Read/Write分離, 請自行Google
接著啟動MySQL-Proxy

[Proxy] # /sbin/service mysql-proxy start

連接上Admin Console

[Proxy] # mysql -u admin -padmin_password -h PROXY_IP -P 4401
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, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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 | MASTER_IP:3306 | unknown | rw | NULL | 0 |
| 2 | SLAVE_IP:3306 | unknown | ro | NULL | 0 |
+————-+——————–+———+——+——+——————-+
2 rows in set (0.00 sec)

或者是直接用設定在MASTER與SLAVE上相同的mysql使用者帳密透過proxy連接

[Proxy] # mysql -u USERNAME -pPASWORD -h PROXY_IP -P 3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 162
Server version: 5.0.77-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

這樣就可以正常使用了

一樣, 這只是個clean install/configure的run down, 碰到問題請詢問Google大人. 雖然那堆config很吃篇幅, 不過我相信有人用的到, 避免每個人Google到不同的東西, 所以寫個清楚.