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

#!/bin/bash
#
# mysql-proxy   This shell script takes care of starting and stopping
#               the MySQL Proxy (mysql-proxy).
#
# chkconfig: - 64 36
# description:  MySQL Proxy server.
# processname: mysql-proxy
# requires: socat which may need to be installed

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

prog="MySQL-Proxy"

# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysql-proxy proxy-backend-addresses "127.0.0.1:3306"
proxybackendaddresses="$result"
get_mysql_option mysql-proxy proxy-address "127.0.0.1:3306"
proxyaddress="$result"
get_mysql_option mysql-proxy socket "/var/lib/mysql/mysql.sock"
socketfile="$result"
get_mysql_option mysql-proxy pid-file "/var/run/mysqld/mysqld.pid"
mypidfile="$result"
mysqllock="/var/lock/subsys/mysqld"
mysqlproxylock="/var/lock/subsys/mysql-socket"

start(){
if [ ! -f $socketfile -a ! -f $mypidfile -a ! -f $mysqllock -a ! -f $mysqlproxylock ]; then
/usr/sbin/mysql-proxy --proxy-backend-addresses="$proxybackendaddresses" --proxy-skip-profiling \
--proxy-address="$proxyaddress"  \
>/dev/null 2>&1 &
result1=$?
[ $result1 -eq 0 ] && touch $mysqllock

/usr/bin/socat UNIX-LISTEN:$socketfile,fork,reuseaddr,unlink-early,user=mysql,group=mysql,mode=777 \
TCP:$proxyaddress >/dev/null 2>&1 &
result2=$?
[ $result2 -eq 0 ] && touch $mysqlproxylock
if [ $result1 -eq 0 -a $result2 -eq 0 ]; then
ret=0
action $"Starting  $prog: " /bin/true

else
ret=1
action $"Failed Stopping $prog: " /bin/false
fi

else
ret=1
echo "Failed: Lock files or sockets already exist. Check MySQL is not already running"

fi
return $ret
}

stop(){
MYSQLPROXYPID=`ps aux | grep proxy | grep -v grep | awk {'print $2'} | head -1`
SOCATPID=`ps aux | grep socat | grep -v grep | awk {'print $2'}`
if [ -n "$MYSQLPROXYPID" ]; then
/bin/kill "$MYSQLPROXYPID" >/dev/null 2>&1
result1=$?
fi

if [ -n "SOCATPID" ]; then
/bin/kill "$SOCATPID"  >/dev/null 2>&1
result2=$?
fi

if [ $result1 -eq 0 -a $result2 -eq 0 ]; then
rm -f $mysqllock
rm -f $mysqlproxylock
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
ret=0

else
ret=1
action $"Failed Stopping $prog: " /bin/false
fi
return $ret
}

restart(){
stop
start
}

# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
echo $"Usage: $0 {start|stop|restart}"
exit 1
esac

exit $?

Set the MySQL-Proxy to start on boot:

chkconfig mysql-proxy on