Archive for February, 2009

Using MySQL Proxy’s Configuration File

February 20, 2009 at 11:33 am · Filed under MySQL Proxy

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

Comments (3)

How to Compile and Install MySQL Proxy from Bazaar on CentOS 5.2

February 12, 2009 at 6:29 pm · Filed under MySQL Proxy

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!

Comments (5)

Client To Server Redirection in MySQL Proxy

February 10, 2009 at 6:00 pm · Filed under MySQL Proxy

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 :-)

Comments (1)