实验环境:redhat6.5,mysql5.7,iptables和selinux关闭
server1 172.25.5.1 master,wirte
server3 172.25.5.3 slave,read
server2 172.25.5.2 proxy
mysql主从复制请参考上一篇文章 mysql数据备份
server2:
tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy
cd mysql-proxy
export PATH=$PATH:/root/Desktop/mysql-proxy/bin/
/etc/init.d/mysqld start
vim /root/Desktop/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
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
mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log \
--proxy-read-only-backend-addresses="172.25.5.3:3306" \
--proxy-lua-script="/root/Desktop/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" \
--admin-password="admin" \
--admin-lua-script="/root/Desktop/mysql-proxy/share/doc/mysql-proxy/admin.lua"
netstat -tunlp
测试:
可以用select * from backends查看读写分离状态,但是之后再连接则报错,可能是因为不太稳定
据说Amoeba for MySQL是一款不错的mysql集群软件,性能要比mysql-proxy好得多。