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 with 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 this 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

MySQL query output to text or CSV

I needed to output the results of a MySQL query today for further analysis and found the answer here. For future reference…

SELECT * FROM errors WHERE mailfrom LIKE ‘a.n.other%’ INTO OUTFILE ‘/tmp/a.n.other.txt’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;

It is also possible to enclose the fields with quotes using:

ENCLOSED BY ‘”‘

Set MySQL to Read-Only

For a backup server which we may want to run as read-only for short maintenance periods the following may be useful.
Method 1: Global read_only
Login to MySQL and set the read_only setting to true:

mysql -u root -p
set GLOBAL read_only = true;

To unset this run:

set GLOBAL read_only = false;

Method 2: Read Lock
Another method which will queue queries while the tables are locked and then run them when the tables have been unlocked involves:

FLUSH TABLES WITH READ LOCK;

To revert back use:

UNLOCK TABLES;

mysqldump

Basic Usage:

mysqldump -u root -pSECRET –quote-names –databases  mydatabasename > mydatabasename.sql

If find it best to include the –databases switch as this will add the ‘DROP DATABASE’ and ‘CREATE DATABASE’ statements when restoring – proceed carefully.
Quote Names:

Quoting the file names can be an insurance against users who may have created field names with spaces in them 🙁

mysqldump -u root -pSECRET –quote-names –databases mydatabasename > mydatabasename.sql

Dump All Databases:

mysqldump -u root -pSECRET –quote-names –all-databases > fullbackup.sql

Zip the ouput file:

nice mysqldump -u root -pSECRET –quote-names –databases dbname1 dbname2 dbname3 | nice gzip -9 > mybackup.sql.gz

Compatibility:

mysqldump -u root -p  –compatible=mysql40 –databases databasename > mybackup.sql

Other compatibility options include:

ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_options

Dump structure-only:

mysqldump -u root -p –no-data –databases dbname1 dbname2 > mybackup.sql

Backup to another MySQL Server

mysqldump -u root -p –host=host1 mydatabase | mysql -u root -p –host=host2 -C newdatabase

The C option specifies to use compression. Note databases must exist already on the target server.

Restore

mysql -u root -p dbname < mybackup.sql

Character Sets / Collation Problems
Convert the database exporting first like this:

mysqldump -u username -p –default-character-set=latin1 –compatible=mysql40 dbname > dump.sql

Then import overwriting the current database (another backup would be wise):

mysql -u username -p –default-character-set=utf8 dbname < dump.sql

MySQL Adding Users
In case you need to access the MySQL database remotely (as in the above example to backup to another server):

mysql -u root -p
grant all privileges on *.* to ‘jonny’@’%’ identified by ‘thepassword’;
flush privileges;
exit

MySQL 4 to MySQL 5 Character Sets and Collation

Moving from MySQL 4 to MySQL 5 caused a few issues on Joomla websites. The problems stem from MySQL 4 using latin1 swedish as default but some applications like Joomla want utf8 so:

1. Re-export the database (it is Ok to do this from the existing MySQL 5 database):

mysqldump -u username -p –default-character-set=latin1 –compatible=mysql40 dbname > dump.sql

2. Import the database again (overwriting the current oddly encoded tables – you do have a backup right!) :

mysql -u username -p –default-character-set=utf8 dbname < dump.sql

Note: It is not enough to just convert the database / tables with:

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Although this will convert the database defaults it does not perform the conversion from latin1 to utf8 on the existing actual content.