Simple MySQL Proxy Failover
mysql-proxy defaults to round-robin load balancing. There are fancy tricks around to get mysql-proxy to balance connections based on how many idle connections there are in a proxy-based connection pool.
But there is no code that I found that would simply load balance based on “always go to one server, go to another server only when the first server is down.”
Well, I spent way too long figuring this out today, again running into the problem where the manual hasn’t been updated. I have indeed made a Forge snippet of this code, but it does not hurt to post it here.
This was in fact taken from http://jan.kneschke.de/projects/mysql/mysql-proxy-learns-r-w-splitting, which I referenced above as having the fancy trick above — but there is one very important difference — the internal structure proxy.servers has been changed to proxy.backends. Sadly, the manual page for the Internal Structures has not been updated to reflect this.
At least now there’s a comment on the documentation page that states that.
function connect_server()
for i = 1, #proxy.backends do
local s = proxy.backends[i]
if s.state ~= proxy.BACKEND_STATE_DOWN then
proxy.connection.backend_ndx = i
-- print ("connecting to " .. i)
return
end
end
end
function read_query(packet)
for i = 1, #proxy.backends do
local s = proxy.backends[i]
if s.state ~= proxy.BACKEND_STATE_DOWN then
proxy.connection.backend_ndx = i
-- print ("connecting to " .. i)
return
end
end
end
Category: Group Blog Posts, MySQL
Tags: failover, load balancer, load balancing, MySQL, proxy
Hi Sheeri .
Thanks for your posting.
I got a Problem. It does not work mysql-proxy 0.6.1 on RHEL4.
Which version of mysql-proxy do you use for testing your lua script?
Thanks.
yw kim.
YoungWoo — I’m using
mysql-proxy-0.6.0-solaris10-x86_64
Can you give more details on how it does not work? ie, are you getting a syntax error with Lua, or are you finding that it does not work with failover? If that’s the case, what’s the behavior you’re seeing — are you seeing it do round-robin load balancing? Have you set up 2 backend servers with the –proxy-addresses startup option to mysql-proxy?
Sheeri,
Thanks for your advice.
So, I tested mysql-proxy 0.6.0.
As a result, It works fine with 0.6.0. :-)
In my case, I have two mysql servers with master-master replication scheme.
I think, mysql-proxy(0.6.1) does not recognize backends status(UP or DOWN).
Thanks.
Regards,
yw kim.
Thanks fr this snippet of code.
One problem when I roll it out Im getting connection refused even though the proxy admin shows both backends
I start it with this:
/usr/local/sbin/mysql-proxy –proxy-lua-script=/opt/scripts/failover.lua –proxy-backend-addresses=192.168.100.21:3306 –proxy-backend-addresses=192.168.100.20:3306
The failover.lua is your code?
Any ideas what may cause this?
Erol,
Having the *exact* error would be useful. However, try doing a
telnet 192.168.100.21 3306
from the proxy machine to see if you can connect to the backend.
Also, you are aware that the proxy sits on port 4040 by default, right? So to connect you have to do:
mysql -h proxyserver -u user -p -P4040
We had the same issue with 0.6.1 and rolled back to 0.6.0. 0.6.1 basically broke load balancing.
Hi Kyle,
This has been fixed on 0.7.0 (we have binaries available for 0.7.1 here http://dev.mysql.com/downloads/mysql-proxy/
Thanks