MySQL Proxy

Today I wanted to create a straight-forward MySQL Proxy service which would forward MySQL queries from a development machine to another live server which was running MySQL. The aim was to allow people on the development machine to use the same connection string as on the live server, specifying the MySQL host as localhost but actually be retrieving data from the live database server.

  • Added the EPEL repository and set it to a priority higher than RPMforge (if using rpmforge). Otherwise you may get problems with the missing dependencies for liblua.5.1.so
  • Install MySQL-Proxy from EPEL and socat for later:

    yum install mysql-proxy socat

  • Run mysql-proxy with the following (we could add this to /etc/rc.local later):

/usr/sbin/mysql-proxy –proxy-backend-addresses=live.domain.tld:3306 –proxy-address=127.0.0.1:3306 –proxy-skip-profiling  –pid-file=/var/run/mysqld/mysqld.pid &

Now if we test that from the local machine with the following it will not work as the mysql client automatically looks for a local Unix socket rather than TCP/IP port 3306:

mysql -u jonny -p
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

So we could amend /etc/my.cnf adding the following at the bottom:

[client]
protocol=tcp

Now with this setting we can connect on the command line but still not from PHP which appears to be hardwired to using the socket. So we could use socat as follows:

socat UNIX-LISTEN:/var/lib/mysql/mysql.sock,fork,reuseaddr,unlink-early,user=mysql,group=mysql,mode=777 TCP:127.0.0.1:3306 &

With this running socat will forward socket requests to the MySQL port.
It would be nice to wrap this all up into an init startup script. I’m not saying that the one below is a good startup script but it helps make things a little more manageable. First I added the following config options to the MySQL config file:

vi /etc/my.cnf

[mysql-proxy]
proxy-backend-addresses=live.domain.tld:3306
proxy-address=127.0.0.1:3306
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/lib/mysql/mysql.sock

Next the init script which should disallow starting the proxy if MySQL is already running.

vi /etc/init.d/mysql-proxy

Set the MySQL-Proxy to start on boot:

chkconfig mysql-proxy on

2 Responses to “MySQL Proxy”

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 class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">