mysql基于gtid的读写分离

实验环境：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好得多。