If you download the binary packages then you need only to extract the package and then copy the mysql-proxy file to your desired location. For example:

$ tar zxf mysql-proxy-0.5.0.tar.gz
$ cp ./mysql-proxy-0.5.0/sbin/mysql-proxy /usr/local/sbin

If you have downloaded the source package then you will need to compile the MySQL Proxy before using it. To build you will need to have the following installed:

Once these components are installed, you need to configure and then build:

$ tar zxf mysql-proxy-0.5.0.tar.gz
$ cd mysql-proxy-0.5.0
$ ./configure
$ make

If you want to test the build, then use the check target to make:

$ make check

The tests try to connect to localhost using the root user. If you need to provide a password, set the MYSQL_PASSWORD environment variable:

$ MYSQL_PASSWORD=root_pwd make check

You can install using the install target:

$ make install

By default mysql-proxy is installed into /usr/local/sbin/mysql-proxy. The Lua example scripts are copied into /usr/local/share.

The MySQL Proxy source is available through a public Subversion repository and is the quickest way to get hold of the latest releases and fixes.

To build from the Subversion repository, you need the following components already installed:

To checkout a local copy of the Subversion repository, use svn:

$ svn co http://svn.MySQL.com/svnpublic/mysql-proxy/ mysql-proxy

The above command will download a complete version of the Subversion repository for mysql-proxy. The main source files are located within the trunk subdirectory. The configuration scripts need to be generated before you can configure and build mysql-proxy. The autogen.sh script will generate the configuration scripts for you:

$ sh ./autogen.sh

The script creates the standard configure script, which you can then use to configure and build with make:

$ ./configure
$ make
$ make install

If you want to create a standalone source distribution, identical to the source distribution available for download:

$ make distcheck

The above will create the file mysql-proxy-0.5.0.tar.gz within the current directory.

The figure below gives an example of how the proxy might be used when injecting queries into the query queue. Because the proxy sits between the client and MySQL server, what the proxy sends to the server, and the information that the proxy ultimately returns to the client do not have to match or correlate. Once the client has connected to the proxy, the following sequence occurs for each individual query sent by the client.

For example, you can queue additional queries into the global query queue to be processed by the server. This can be used to add statistical information by adding queries before and after the original query, changing the original query:

SELECT * FROM City;

Into a sequence of queries:

SELECT NOW();
SELECT * FROM City;
SELECT NOW();

You can also modify the original statement, for example to add EXPLAIN to each statement executed to get information on how the statement was processed, again altering our original SQL statement into a number of statements:

SELECT * FROM City;
EXPLAIN SELECT * FROM City;

In both of these examples, the client would have received more result sets than expected. Regardless of how you manipulate the incoming query and the returned result, the number of queries returned by the proxy must match the number of original queries sent by the client.

You could adjust the client to handle the multiple result sets sent by the proxy, but in most cases you will want the existence of the proxy to remain transparent. To ensure that the number of queries and result sets match, you can use the MySQL Proxy read_query_result() to extract the additional result set information and return only the result set the client originally requested back to the client. You can achieve this by giving each query that you add to the query queue a unique ID, and then filter out queries that do not match the original query ID when processing them with read_query_result().

There are a number of internal structures within the scripting element of MySQL Proxy. The primary structure is proxy and this provides an interface to the many common structures used throughout the script, such as connection lists and configured backend servers. Other structures, such as the incoming packet from the client and result sets are only available within the context of one of the scriptable functions.

proxy.connection

The proxy.connection object is read only, and provides information about the current connection.

proxy.backends

The proxy.backends table is partially writable and contains an array of all the configured backend servers and the server metadata (IP address, status, etc.). You can determine the array index of the current connection using proxy.connection["backend_ndx"] which is the index into this table of the backend server being used by the active connection.

The attributes for each entry within the proxy.backends table are shown in this table.

proxy.queries

The proxy.queries object is a queue representing the list of queries to be sent to the server. The queue is not populated automatically, but if you do not explicitly populate the queue then queries are passed on to the backend server verbatim. Also, if you do not populate the query queue by hand, then the read_query_result() function is not triggered.

The following methods are supported for populating the proxy.queries object.

For example, you could append a query packet to the proxy.queries queue by using the append():

proxy.queries:append(1,packet)

proxy.response

The proxy.response structure is used when you want to return your own MySQL response, instead of forwarding a packet that you have received a backend server. The structure holds the response type information, an optional error message, and the result set (rows/columns) that you want to return.

When using proxy.response you either set proxy.response.type to proxy.MYSQLD_PACKET_OK and then build resultset to contain the results that you want to return, or set proxy.response.type to proxy.MYSQLD_PACKET_ERR and set the proxy.response.errmsg to a string with the error message. To send the completed resultset or error message, you should return the proxy.PROXY_SEND_RESULT to trigger the return of the packet information.

An example of this can be seen in the tutorial-resultset.lua script within the MySQL Proxy package:

if string.lower(command) == "show" and string.lower(option) == "querycounter" then
        ---
        -- proxy.PROXY_SEND_RESULT requires
        --
        -- proxy.response.type to be either
        -- * proxy.MYSQLD_PACKET_OK or
        -- * proxy.MYSQLD_PACKET_ERR
        --
        -- for proxy.MYSQLD_PACKET_OK you need a resultset
        -- * fields
        -- * rows
        --
        -- for proxy.MYSQLD_PACKET_ERR
        -- * errmsg
        proxy.response.type = proxy.MYSQLD_PACKET_OK
        proxy.response.resultset = {
                fields = {
                        { type = proxy.MYSQL_TYPE_LONG, name = "global_query_counter", },
                        { type = proxy.MYSQL_TYPE_LONG, name = "query_counter", },
                },
                rows = {
                        { proxy.global.query_counter, query_counter }
                }
        }

        -- we have our result, send it back
        return proxy.PROXY_SEND_RESULT
elseif string.lower(command) == "show" and string.lower(option) == "myerror" then
        proxy.response.type = proxy.MYSQLD_PACKET_ERR
        proxy.response.errmsg = "my first error"

        return proxy.PROXY_SEND_RESULT

proxy.response.resultset

The proxy.response.resultset structure should be populated with the rows and columns of data that you want to return. The structure contains the information about the entire result set, with the individual elements of the data shown in the table below.

For an example of the population of this table, see Section 16.4.2, “Internal Structures”.

Proxy Return State Constants

The following constants are used internally by the proxy to specify the response to send to the client or server. All constants are exposed as values within the main proxy table.

As constants, these entities are available without qualification in the Lua scripts. For example, at the end of the read_query_result() you might return PROXY_IGNORE_RESULT:

return proxy.PROXY_IGNORE_RESULT

Packet State Constants

The following states describe the status of a network packet. These items are entries within the main proxy table.

Backend State/Type Constants

The following constants are used either to define the status of the backend server (the MySQL server to which the proxy is connected) or the type of backend server. These items are entries within the main proxy table.

Server Command Constants

The following values are used in the packets exchanged between the client and server to identify the information in the rest of the packet. These items are entries within the main proxy table. The packet type is defined as the first character in the sent packet. For example, when intercepting packets from the client to edit or monitor a query you would check that the first byte of the packet was of type proxy.COM_QUERY.

MySQL Type Constants

These constants are used to identify the field types in the query result data returned to clients from the result of a query. These items are entries within the main proxy table.

When the proxy accepts a connection from a MySQL client, the connect_server() function is called.

There are no arguments to the function, but you can use and if necessary manipulate the information in the proxy.connection table, which is unique to each client session.

For example, if you have multiple backend servers then you can set the server to be used by that connection by setting the value of proxy.connection.backend_ndx to a valid server number. The code below will choose between two servers based on whether the current time in minutes is odd or even:

function connect_server()
        print("--> a client really wants to talk to a server")
        if (tonumber(os.date("%M")) % 2 == 0) then
                proxy.connection.backend_ndx = 2
                print("Choosing backend 2")
        else
                proxy.connection.backend_ndx = 1
                print("Choosing backend 1")
        end
        print("Using " .. proxy.backends[proxy.connection.backend_ndx].address)
end

In this example the IP address/port combination is also displayed by accessing the information from the internal proxy.backends table.

Handshake information is sent by the server to the client after the initial connection (through connect_server()) has been made. The handshake information contains details about the MySQL version, the ID of the thread that will handle the connection information, and the IP address of the client and server. This information is exposed through a Lua table as the only argument to the function.

For example, you can print out the handshake data and refuse clients by IP address with the following function:

function read_handshake( auth )
        print("<-- let's send him some information about us")
        print("    mysqld-version: " .. auth.mysqld_version)
        print("    thread-id     : " .. auth.thread_id)
        print("    scramble-buf  : " .. string.format("%q", auth.scramble))
        print("    server-addr   : " .. auth.server_addr)
        print("    client-addr   : " .. auth.client_addr)

        if not auth.client_addr:match("^127.0.0.1:") then
                proxy.response.type = proxy.MYSQLD_PACKET_ERR
                proxy.response.errmsg = "only local connects are allowed"

                print("we don't like this client");

                return proxy.PROXY_SEND_RESULT
        end
end

Note that you have to return an error packet to the client by using proxy.PROXY_SEND_RESULT.

The read_auth() function is triggered when an authentication handshake is initiated by the client. In the execution sequence, read_auth() occurs immediately after read_handshake(), so the server selection has already been made, but the connection and authorization information has not yet been provided to the backend server.

The function accepts a single argument, an Lua table containing the authorization information for the handshake process. The entries in the table are:

For example, you can print the username and password supplied during authorization using:

function read_auth( auth )
        print("    username      : " .. auth.username)
        print("    password      : " .. string.format("%q", auth.password))
end

You can interrupt the authentication process within this function and return an error packet back to the client by constructing a new packet and returning proxy.PROXY_SEND_RESULT:

proxy.response.type = proxy.MYSQLD_PACKET_ERR
proxy.response.errmsg = "Logins are not allowed"
return proxy.PROXY_SEND_RESULT

The return packet from the server during authentication is captured by read_auth_result(). The only argument to this function is the authentication packet returned by the server. As the packet is a raw MySQL network protocol packet, you must access the first byte to identify the packet type and contents. The MYSQLD_PACKET_ERR and MYSQLD_PACKET_OK constants can be used to identify whether the authentication was successful:

function read_auth_result( auth )
        local state = auth.packet:byte()

        if state == proxy.MYSQLD_PACKET_OK then
                print("<-- auth ok");
        elseif state == proxy.MYSQLD_PACKET_ERR then
                print("<-- auth failed");
        else
                print("<-- auth ... don't know: " .. string.format("%q", auth.packet));
        end
end

The read_query() function is called once for each query submitted by the client and accepts a single argument, the query packet that was provided. To access the content of the packet you must parse the packet contents manually.

For example, you can intercept a query packet and print out the contents using the following function definition:

function read_query( packet )
        if packet:byte() == proxy.COM_QUERY then
                print("we got a normal query: " .. packet:sub(2))
        end
end

This example checks the first byte of the packet to determine the type. If the type is COM_QUERY (see Section 16.4.2, “Internal Structures”), then we extract the query from the packet and print it out. The structure of the packet type supplied is important. In the case of a COM_QUERY packet, the remaining contents of the packet are the text of the query string. In this example, no changes have been made to the query or the list of queries that will ultimately be sent to the MySQL server.

To modify a query, or add new queries, you must populate the query queue (proxy.queries) and then execute the queries that you have placed into the queue. If you do not modify the original query or the queue, then the query received from the client is sent to the MySQL server verbatim.

When adding queries to the queue, you should follow these guidelines:

Normally, the read_query() and read_query_result() function are used in conjunction with each other to inject additional queries and remove the additional result sets. However, read_query_result() is only called if you populate the query queue within read_query().

The read_query_result() is called for each result set returned by the server only if you have manually injected queries into the query queue. If you have not manipulated the query queue then this function is not called. The function supports a single argument, the result packet, which provides a number of properties:

By accessing the result information from the MySQL server you can extract the results that match the queries that you injected, return different result sets (for example, from a modified query), and even create your own result sets.

The Lua script below, for example, will output the query, followed by the query time and response time (i.e. the time to execute the query and the time to return the data for the query) for each query sent to the server:

function read_query( packet )
        if packet:byte() == proxy.COM_QUERY then
                print("we got a normal query: " .. packet:sub(2))

                proxy.queries:append(1, packet )

                return proxy.PROXY_SEND_QUERY
        end
end

function read_query_result(inj)
        print("query-time: " .. (inj.query_time / 1000) .. "ms")
        print("response-time: " .. (inj.response_time / 1000) .. "ms")
end

You can access the rows of returned results from the resultset by accessing the rows property of the resultset property of the result that is exposed through read_query_result(). For example, you can iterate over the results showing the first column from each row using this Lua fragment:

for row in inj.resultset.rows do
        print("injected query returned: " .. row[1])
end

Just like read_query(), read_query_result() can return different values for each result according to the result returned. If you have injected additional queries into the query queue, for example, then you will want to remove the results returned from those additional queries and only return the results from the query originally submitted by the client.

The example below injects additional SELECT NOW() statements into the query queue, giving them a different ID to the ID of the original query. Within read_query_result(), if the ID for the injected queries is identified, we display the result row, and return the proxy.PROXY_IGNORE_RESULT from the function so that the result is not returned to the client. If the result is from any other query, we print out the query time information for the query and return the default, which passes on the result set unchanged. We could also have explicitly returned proxy.PROXY_IGNORE_RESULT to the MySQL client.

function read_query( packet )
        if packet:byte() == proxy.COM_QUERY then
                proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "SELECT NOW()" )
                proxy.queries:append(1, packet )
                proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "SELECT NOW()" )

                return proxy.PROXY_SEND_QUERY
        end
end


function read_query_result(inj)
        if inj.id == 2 then
                for row in inj.resultset.rows do
                        print("injected query returned: " .. row[1])
                end
                return proxy.PROXY_IGNORE_RESULT
        else
                print("query-time: " .. (inj.query_time / 1000) .. "ms")
                print("response-time: " .. (inj.response_time / 1000) .. "ms")
        end
end

For further examples, see Section 16.5, “Using MySQL Proxy”.

The mysql-proxy administration interface can be accessed using any MySQL client using the standard protocols. You can use the administration interface to gain information about the proxy server as a whole - standard connections to the proxy are isolated to operate as if you were connected directly to the backend MySQL server. Currently, the interface supports a limited set of functionality designed to provide connection and configuration information.

Because connectivity is provided over the standard MySQL protocol, you must access this information using SQL syntax. By default, the administration port is configured as 4041. You can change this port number using the --admin-address command-line option.

To get a list of the currently active connections to the proxy:

mysql> select * from proxy_connections;
+------+--------+-------+------+
| id   | type   | state | db   |
+------+--------+-------+------+
|    0 | server | 0     |      | 
|    1 | proxy  | 0     |      | 
|    2 | server | 10    |      | 
+------+--------+-------+------+
3 rows in set (0.00 sec)

To get the current configuration:

mysql> select * from proxy_config;
+----------------------------+----------------------+
| option                     | value                |
+----------------------------+----------------------+
| admin.address              | :4041                | 
| proxy.address              | :4040                | 
| proxy.lua_script           | mc.lua               | 
| proxy.backend_addresses[0] | mysql:3306           | 
| proxy.fix_bug_25371        | 0                    | 
| proxy.profiling            | 1                    | 
+----------------------------+----------------------+
6 rows in set (0.01 sec)