Simple MySQL Proxy Failover
Mar 27, 2008 / By Sheeri Cabral
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