Joshua Zhu’s Blog

I write this tutorial, simply because it is undocumented.

MySQL Proxy has only long options. Therefore, it is a little bit inconvenient to type the long command line. For example, suppose we are going to setup a MySQL Proxy server forwarding connections to two MySQL backends. We also want to 1) use a Lua script, 2) start the proxy in daemon mode, 3) log all messages of level debug and higher to a specific file, and 4) write the proxy’s pid to its pid file. What would the command line look like?

$ mysql-proxy --proxy-address=192.168.0.189:3307 \
--proxy-backend-addresses=192.168.0.189:3306 \
--proxy-backend-addresses=192.168.0.192:3306 \
--proxy-lua-script=/home/josh/mysql-proxy/dispatch-by-client-address.lua \
--daemon \
--log-file=/home/josh/mysql-proxy/mysql-proxy.log --log-level=debug \
--pid-file=/home/josh/mysql-proxy/mysql-proxy.pid

As you can see, it’s quite long. Not to mention, MySQL Proxy can be plugged into many plugins, which could import more options.

Is there any way to make it less repetitive? Yes. By taking advantage of MySQL Proxy’s configuration file, we no longer need to type such long command line.

MySQL Proxy uses GLib’s key file (GKeyFile) as its configuration file format. A key file is very similar to a .ini file. It consists of groups of key value pairs which can be strings, booleans, integers and lists of these. Each key value pair must be contained in a group, where the name appears between enclosed square brackets.

Here goes the proxy’s configuration file of the example above:

# MySQL Proxy's configuration file (mysql-proxy.cnf)
 
[mysql-proxy]
daemon = true
pid-file = /home/josh/mysql-proxy/mysql-proxy.pid
log-file = /home/josh/mysql-proxy/mysql-proxy.log
log-level = debug
proxy-address = 192.168.0.189:3307
proxy-backend-addresses = 192.168.0.192:3306,192.168.0.189:3306
proxy-lua-script = /home/josh/mysql-proxy/dispatch-by-client-address.lua

And the command line:

$ mysql-proxy --defaults-file=mysql-proxy.cnf

It is apparently much shorter. We now just need to specify the name of the configuration file!

Some notes:
1) GLib uses the ‘;’ character as the default list separator, while MySQL Proxy uses ‘,’.
2) Ungrouped keys are not allowed in key files.
3) The –version and –defaults-file options can not appear in MySQL Proxy’s configuration file.
4) A handy feature: the values in MySQL Proxy’s configuration file can be overridden by the command line options.

To learn more about GKeyFile, please visit:
http://www.gtkbook.com/tutorial.php?page=keyfile
http://library.gnome.org/devel/glib/2.18/glib-Key-value-file-parser.html
http://standards.freedesktop.org/desktop-entry-spec/desktop-entry-spec-1.0.html

Below is a full step by step guide to compiling and installing MySQL Proxy from Bazaar on a CentOS 5.2 box. It should also work for CentOS 5.

First, if you don’t have the EPEL (Extra Packages for Enterprise Linux) repository enabled, you should enable it:

# rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-2.noarch.rpm

Make sure you have GNU Autotools, flex, pkg-config, and bazaar, as well as MySQL client libraries installed. If not, please follow this command:

# yum install autoconf automake libtool flex pkgconfig bzr mysql-devel

Since the version of libevent that CentOS ships is too old, you need to build a newer one (>= 1.4.0, for better threading support):

$ wget http://monkey.org/~provos/libevent-1.4.9-stable.tar.gz
$ tar zvfx libevent-1.4.9-stable.tar.gz
$ cd libevent-1.4.9-stable
$ ./configure
$ make
# make install

Again, CentOS 5.2 ships with an old version of GLib, a newer one is required (>= 2.16.0, for the GLib testing framework):

$ wget http://ftp.gnome.org/pub/gnome/sources/glib/2.18/glib-2.18.4.tar.gz
$ tar zvfx glib-2.18.4.tar.gz
$ cd glib-2.18.4
$ ./configure
$ make
# make install

And Lua 5.1 should be installed:

$ wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
$ tar zvfx lua-5.1.4.tar.gz
$ cd lua-5.1.4
$ make linux
# make install
# cp etc/lua.pc /usr/local/lib/pkgconfig/

Important: you should make pkg-config know where the libraries are!

$ export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/local/lib/pkgconfig

Now, check out the latest source code of MySQL Proxy and build:

$ bzr branch lp:mysql-proxy
$ cd mysql-proxy
$ ./autogen.sh
$ ./configure
$ make
# make install

Run it and see if everything is okay:

$ mysql-proxy -V

Done!

Pierre asked if clients could be redirected to different servers by client address in MySQL Proxy. The answer is clearly yes, and that’s one of the reasons why MySQL Proxy is designed for. Moreover, you can set the connection’s backend (proxy.connection.backend_ndx) to what you want in the connect_server() stage, according to your own rules.

I wrote a simple Lua script to do this job. Here is the scirpt:

--
-- author: Joshua Zhu (http://www.zhuzhaoyuan.com)
-- 
 
local string = require("string")
 
local is_debug = true
 
local cli_svr_map
 
function get_server(client)
    -- initialize the map table
    if not cli_svr_map then
        -- note: you should modify the client IPs and the backend addresses below!!!
        cli_svr_map = {
            ["192.168.0.189"] = {
                address = "192.168.0.192:3306",
                backend_ndx = -1 },
            ["192.168.0.192"] = {
                address = "192.168.0.189:3306",
                backend_ndx = -1 },
         }
 
        for _, v in pairs(cli_svr_map) do
            for i = 1, #proxy.global.backends do
                local backend = proxy.global.backends[i]
                if v.address == backend.address then
                    v.backend_ndx = i
                    break
                end
            end
        end
 
        if is_debug then
            print("map table: ")
            for k, v in pairs(cli_svr_map) do
                print("   [" .. k .. "] = {" .. v.address .. ", " .. v.backend_ndx .. "}")
            end
        end
    end
 
    local host = string.match(client, "([^:]+):")
    if cli_svr_map[host] then
        return cli_svr_map[host].backend_ndx
    end
 
    return -1
end
 
function connect_server()
    if is_debug then
        print("[connect_server] " .. proxy.connection.client.address)
        print("we have " .. #proxy.global.backends .. " backends:")
 
        for i = 1, #proxy.global.backends do
            local backend = proxy.global.backends[i]
            print("   [" .. i .. "].connected_clients = " .. backend.connected_clients)
            print("   [" .. i .. "].address = " .. backend.address)
            print("   [" .. i .. "].state = " .. backend.state)
            print("   [" .. i .. "].type = " .. backend.type)
            print("   [" .. i .. "].uuid = " .. (backend.uuid or "(nil)"))
        end
    end
 
    local index = get_server(proxy.connection.client.address)
    if index > 0 and index <= #proxy.global.backends then
        if proxy.global.backends[index].state ~= proxy.BACKEND_STATE_DOWN then
            if is_debug then
                print("redirect the client to backends[" .. proxy.global.backends[index].address .. "]")
            end
 
            proxy.connection.backend_ndx = index
        end
    end
end

Download the script.
If you find bugs in this script, please correct me

That’s really GOOD NEWS to the whole community.

After a long wait (half a year or so), the latest source of MySQL Proxy was finally published yesterday, along with an increase of about 10K lines of C code and 2.6K lines of Lua code. Yes, it is not only alive but also under busy construction, exactly the same as what Kay promised. Thanks to Jan and Kay for developing this sexy software and enduring complaints from guys like me who are not patient enough!

The project’s repository has been moved from svn.mysql.com to launchpad.net at: https://launchpad.net/mysql-proxy. So the version control system for MySQL Proxy has been switched to Bazaar, which is considered more suitable for distributed development.

You can now checkout a copy of the latest source code by using the following command:
$ bzr branch lp:mysql-proxy

There is also a mailing list at https://launchpad.net/~mysql-proxy-discuss, where you can ask questions about MySQL Proxy.

For more details, please visit https://launchpad.net/mysql-proxy.

See also:
Kay’s blog post: MySQL Proxy code now live
Jan’s blog post: MySQL Proxy: public repo moved to launchpad

P.S. I’ll keep an eye on this project and post my suggestions/ideas on the discussion list when I have some.

Cheers!

Some days ago, I installed a copy of MySQL Proxy that I compiled from the latest source (SVN revision 511). To my surprise, the program exited silently after a few days of use. By analyzing the core dump (see below) and digging into MySQL Proxy’s source code, I eventually located the line where the bug was in. It seems the bug could be caused when a client closes the connection before the server (backend) sending back the query result of the command COM_STMT_PREPARE. The data_free handler of command COM_STMT_PREPARE was not set up correctly. And the definition of function network_mysqld_com_stmt_prepare_result_free was missing, too.

Here’s the core dump,
# gdb mysql-proxy core.10423
GNU gdb Red Hat Linux (6.5-16.el5rh)
Copyright (C) 2006 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type “show copying” to see the conditions.
There is absolutely no warranty for GDB.  Type “show warranty” for details.
This GDB was configured as “x86_64-redhat-linux-gnu”…Using host libthread_db library
“/lib64/libthread_db.so.1″.
warning: Can’t read pathname for load map: Input/output error.
Reading symbols from /usr/local/lib/mysql-proxy/libmysql-chassis.so.0…done.
Loaded symbols for /usr/local/lib/mysql-proxy/libmysql-chassis.so.0
Reading symbols from /usr/local/lib/libgthread-2.0.so.0…done.
Loaded symbols for /usr/local/lib/libgthread-2.0.so.0
Reading symbols from /lib64/libpthread.so.0…done.
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /usr/local/lib/mysql-proxy/libmysql-proxy.so.0…done.
Loaded symbols for /usr/local/lib/mysql-proxy/libmysql-proxy.so.0
Reading symbols from /usr/local/lib/libevent-1.3e.so.1…done.
Loaded symbols for /usr/local/lib/libevent-1.3e.so.1
Reading symbols from /lib64/libnsl.so.1…done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/librt.so.1…done.
Loaded symbols for /lib64/librt.so.1
Reading symbols from /lib64/libresolv.so.2…done.
Loaded symbols for /lib64/libresolv.so.2
Reading symbols from /lib64/libm.so.6…done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /usr/local/lib/libgmodule-2.0.so.0…done.
Loaded symbols for /usr/local/lib/libgmodule-2.0.so.0
Reading symbols from /lib64/libdl.so.2…done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /usr/local/lib/libglib-2.0.so.0…done.
Loaded symbols for /usr/local/lib/libglib-2.0.so.0
Reading symbols from /lib64/libc.so.6…done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2…done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /usr/local/lib/mysql-proxy/libadmin.so…done.
Loaded symbols for /usr/local/lib/mysql-proxy/libadmin.so
Reading symbols from /usr/local/lib/mysql-proxy/libproxy.so…done.
Loaded symbols for /usr/local/lib/mysql-proxy/libproxy.so
warning: no loadable sections found in added symbol-file system-supplied DSO at 0×7fff5f9fd000
Core was generated by `mysql-proxy –proxy-backend-addresses=192.168.0.243:3307 –proxy-address=192.16′.
Program terminated with signal 11, Segmentation fault.
#0  IA__g_string_free (string=0×1, free_segment=1) at gstring.c:473
473           g_free (string->str);
(gdb) bt
#0  IA__g_string_free (string=0×1, free_segment=1) at gstring.c:473
#1  0×00002ab94b5bf9e6 in network_mysqld_com_init_db_result_free (udata=0×6b0830) at network-mysqld-packet.c:284
#2  0×00002ab94b5bbde3 in network_mysqld_con_free (con=0×69de60) at network-mysqld.c:307
#3  0×00002ab94b5bbf2d in network_mysqld_con_handle (event_fd=28, events=2, user_data=0×69de60) at network-mysqld.c:735
#4  0×00002ab94b7d56d9 in event_base_loop (base=0×610df0, flags=0) at event.c:331
#5  0×00002ab94b15850a in chassis_mainloop (_chas=<value optimized out>) at chassis-mainloop.c:163
#6  0×00000000004023c7 in main (argc=1, argv=0×7fff5f95cf88) at chassis.c:594

A little tip: If you run MySQL Proxy in daemon mode and it crashes, you should check the directory “/” to see whether there is a core dump file generated in it. Because MySQL Proxy chdir(2) to the root directory when daemonize()ing. Of course you should turn on core dumps (ulimit(1)) first.

Although I failed to reproduce this bug, I wrote a bug report to MySQL’s bug repository finally. I also submitted two patches to fix this problem.

The bug has been verified by Kay Roepke, one of MySQL Proxy’s developers. “Thank you for your submission. We are in the process of updating the public repository, so verifying against the current source will be easier in the future. However, this particular problem is still present in the latest version.”, Kay replied. Hmmm, so I’ve no idea when the patches would be approved and applied, but I hope we don’t wait too long to see the patches being merged into the trunk. (Oh, Jan and Kay, it seems there were no activities of MySQL Proxy quite a few months

For those living on the bleeding edge, i.e. who are using the latest SVN version of MySQL Proxy and suffering from the same problem I came across, maybe you can give these patches a try, and see if it works for you. I hope this helps, though.

You can download the patches here,
network-mysqld-packet.c.patch network-mysqld.c.patch

Regards.

Updated Jan 16, 2009: latest patches for the latest MySQL Proxy (Bazaar revision 561):
network-mysqld-packet.c.patch network-mysqld.c.patch

Updated Feb 6, 2009: the patches have been merged into the trunk (Bazaar revision 566)! So the bug now has been fixed