Simple MySQL Proxy Failover

Mar 27, 2008 / By Sheeri Cabral

Tags:

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

9 Responses to “Simple MySQL Proxy Failover”

  • YoungWoo Kim says:

    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.

  • Sheeri Cabral says:

    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?

  • YoungWoo Kim says:

    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.

  • Erol says:

    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?

  • Sheeri Cabral says:

    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

  • Kyle says:

    We had the same issue with 0.6.1 and rolled back to 0.6.0. 0.6.1 basically broke load balancing.

  • Diego says:

    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

  • val says:

    I’m having an issue using this script. I have my setup as two mysqld servers with master-master replication. When I unplug the network cable to master1 the mysql-proxy takes 3 minutes to failover to master2 and only marks master1 into state=2 (down) for 4 seconds. Then it ‘wakes it up’ again to state=0 (unknown). Upon the next query it takes another 3 minutes to fail (3 minutes because of the tcp timeout?) back to state=2 and then the whole cycle stars over again.

    Why would it try to wake up the master1 after 4 seconds? What if the master1 machine goes down for the entire night, shouldn’t it just keep it down until it can be fixed? It would be fine if it didn’t hang for 3 minutes, but using this with web applications those 3 minutes is unacceptable.

    Am I doing it wrong?

  • Pratyush says:

    I have a Master-Master setup of MySQL servers. Following is my requirement:
    1. If the Active server goes down, the standby server should start taking requests.
    2. Once the active server is back up and running, all the requests should start hitting Active again.

    I used MySQL proxy 0.8 and the failover lua script but the results were not as expected. i.e. once the active was down, the requests were correctly sent to the standby server but once the active server was back up and running, it continued to send the requests to the standby server.

    Could you tell me what might be the reason? Is this the expected behavior?

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>