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

Tags: fail over • failover • load balancer • load balancing • MySQL • proxy

This entry was posted on Thursday, March 27th, 2008 at 3:15 pm and is filed under Group Blog Posts, MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.