Friday, April 23, 2010

Deploying and monitoring mysql-proxy with supervisor

I've known about supervisor ever since Chris McDonough's PyCon 2008 talk. I've wanted to use it before, especially in conjunction with mysql-proxy, but my efforts in running it haven't been met with success. That is, until I realized the one but important mistake I was making: I was trying to run mysql-proxy in its own --daemon mode, and I was expecting supervisor to control the mysql-proxy at the same time. Well, this is not how supervisor works.

Supervisor handles processes that run in the foreground, and it daemonizes them for you. It knows how to capture stdout and stderr for the process it controls and how to save those streams either combined or separately to their own log files.

Of course, what supervisor excels at is actually monitoring that the process is running -- it can detect that the process died or was killed, and it will restart that process within seconds. This sounds like the perfect scenario for the deployment of mysql-proxy. Here's what I did to get that to work:

1) Installed supervisor via easy_install.
# easy_install supervisor
I also created a configuration file for supervisord by running:
# echo_supervisord_conf > /etc/supervisord.conf
2) Downloaded the binary package for mysql-proxy (version 0.8 at the time of this post). I un-tar-ed the package in /opt/mysql-proxy-0.8.0-linux-glibc2.3-x86-64bit and I made a symlink to it called /opt/mysql-proxy.

3) I wanted mysql-proxy to send traffic to 2 MySQL servers that I had configured in master-master mode. I also wanted one MySQL server to get all traffic, and the 2nd server to be hit only if the first one went down. In short, I wanted active-passive failover mode for my 2 MySQL servers. I found this lua script written by Sheeri Cabral for doing the failover, but I had to modify it slightly -- at least in mysql-proxy 0.8, the proxy.backends variable is now called The rest of the script was fine. I created a directory called /opt/mysql-proxy/scripts and saved my version of the script in a file called failover.lua in that directory.

4) I verified that I could correctly run mysql-proxy in foreground mode by issuing this command (where and are the IP addresses of my active and passive MySQL servers respectively):
/opt/mysql-proxy/bin/mysql-proxy \
--admin-address \
--proxy-address \
--proxy-backend-addresses= \
--proxy-backend-addresses= \

Note that I am specifying the local ports where mysql-proxy should listen for admin traffic (port 43306) and for regular MySQL proxying traffic (port 33306).

5) I created a [program] section in the /etc/supervisord.conf configuration file that looks like this:

command=/opt/mysql-proxy/bin/mysql-proxy \
--admin-address \
--proxy-address \
--proxy-backend-addresses=stgdb101:3306 \
--proxy-backend-addresses=stgdb03:3306 \

Here's a short explanation of each line in this section:
i) The program name can be anything you want. It is what gets displayed in the program list when you run supervisorctl.
ii) The command is the exact commmand you would use to run the process in the foreground.
iii) I do want to redirect stderr to stdout
iv) I do want to capture stdout to a file in /var/log named "program_name".log -- so in my case it's named mysql-proxy-3306.log
v) I want to run the mysql-proxy process as user mysql (if no user is specified, it will run as root)

I made two other modifications to /etc/supervisord.conf in the [supervisord] section:

I also found an init.d startup script example for supervisord, which I modified a bit. My version is here. To start up supervisord, I use '/etc/init.d/supervisord start' or 'service supervisord start'. To make supervisord re-read its configuration file, I use 'service supervisord reload'. I also ran 'chkconfig supervisord on' to have the init.d script start upon reboot.

6) After starting up supervisord, I could see the mysql-proxy process running. Any output that mysql-proxy might have is captured in /var/log/mysql-proxy-3306.log.
Also, by running supervisorctl, I could see something like this:

mysql-proxy-3306 RUNNING pid 1860, uptime 23:10:45

To test out the monitoring and restarting capabilities of supervisord, I did a hard kill -9 of the mysql-proxy process. In a matter of 2 seconds, the process was restarted by supervisord. Here are the lines related to this in /var/log/supervisord.log:

2010-04-22 02:04:59,161 INFO exited: mysql-proxy-3309 (terminated by SIGKILL; not expected)
2010-04-22 02:05:00,177 INFO spawned: 'mysql-proxy-3309' with pid 26137
2010-04-22 02:05:01,325 INFO success: mysql-proxy-3309 entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)

As Borat would say...I LIKE!!!

My intention is to apply this methodology for other processes that I was daemonizing via grizzled.os for example. The advantage of using supervisord are many, but of particular importance to me is that it will restart the process on errors, so I don't need to worry about doing that on my own.

That's about it for now. I will post again about how mysql-proxy behaves in failover mode in production conditions. For now I have it in staging.

1 comment:

Brandon Bowman said...

Win! Great post and very timely for me :)

Modifying EC2 security groups via AWS Lambda functions

One task that comes up again and again is adding, removing or updating source CIDR blocks in various security groups in an EC2 infrastructur...