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

9 comments on “Simple MySQL Proxy Failover”

YoungWoo Kim on March 31, 2008 at 11:18 am said:

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.

Reply ↓
Sheeri Cabral on March 31, 2008 at 11:58 am said:

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?

Reply ↓
YoungWoo Kim on March 31, 2008 at 9:02 pm said:

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.

Reply ↓
Erol on January 26, 2009 at 3:01 pm said:

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?

Reply ↓
Sheeri Cabral on February 3, 2009 at 4:27 pm said:

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

Reply ↓
Kyle on June 16, 2009 at 11:04 am said:

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

Reply ↓
Diego on June 16, 2009 at 12:26 pm said:

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

Reply ↓
val on June 5, 2010 at 12:55 am said:

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?

Reply ↓
Pratyush on October 24, 2012 at 9:21 am said:

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?

Reply ↓