LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Articles > Jeremy's Magazine Articles
User Name
Password

Notices


By jeremy at 2007-12-13 10:15
Understanding MySQL Proxy
Linux Magazine
By Jeremy Garcia

The new MySQL Proxy is a lightweight program that sits between your client and your MySQL database and monitors, analyzes, and manipulates the communication. Your client connects to the proxy via your usual credentials. From there, an embedded Lua interpreter allows you to perform arbitrary actions on both the incoming query and the result set. Such interception and manipulations provides for a variety of uses, including load balancing, dynamic fail over, query analysis, query filtering, query modification, and more. The power of MySQL Proxy is the flexibility offered by the Lua engine.

MySQL Proxy is licensed under the GNU Public License and is available for download from http://forge.mysql.com/wiki/MySQL_Proxy. Keep in mind that the product is still young, so you may encounter a few rough edges. Additionally, versions MySQL 5.x and up are the only releases officially supported.

While official binaries are available for some platforms, you may prefer to use the Subversion tree, since it contains the bleeding-edge code. To check MySQL Proxy out, compile, and install, perform the following:

Code:
$ svn co http://svn.mysql.com/\
svnpublic/mysql-proxy/ mysql-proxy
$ cd mysql-proxy/trunk
$ ./autogen.sh
$ ./configure && make distcheck
# make install
Once installed, run /usr/local/sbin/mysql-proxy --help-all to get a list of all command-line options. By default, the software's proxy module listens on port 4040 and its administration module listens on port 4041, and all connections are passed to localhost, or 127.0.0.1. The Subversion tree contains some sample Lua scripts to get you started.

The following examples show a mysql-proxy command followed by a brief explanation. The connection string for the mysql client is mysql-h 127.0.0.1-P4040 in all cases unless otherwise noted. For initial testing, include the -D flag, which keeps MySQL Proxy in the foreground.

Code:
$ /usr/local/sbin/mysql-proxy -D \
--proxy-lua-script=tutorial-basic.lua
This starts the program with a sample script (look in the examples subdirectory) that simply identifies SQL statements of type COM_QUERY. Any query command you issue in the MySQL client, mysql, results in a line such as the following:

we got a normal query: show databases

This simple example proves that MySQL Proxy is working. Let's try a slightly more advanced example.

Code:
/usr/local/sbin/mysql-proxy -D \
--proxy-lua-script=tutorial-rewrite.lua
The tutorial-rewrite.lua script transforms a few Unix- like commands in the MySQL client to the proper MySQL syntax. For instance, cd mysql is the functional equivalent to use mysql, and ls issues a SHOW TABLES. You're now starting to get a brief glimpse into the power of MySQL Proxy.

If you take a look at the remaining example scripts, you'll notice three main member functions accessible via the Lua engine. connect_server() is called at connection time and can be used to change connection parameters. read_query(packet) is called before sending the query to the server and can be used to either modify the query or add additional information to it. read_query_result(injection_packet) is called before sending the result to the client and can be used to modify the result set. With this information and a little practice with Lua, the sky is really the limit. In addition to the included sample scripts, there are additional examples and tutorials on the MySQL Proxy site.

Keep in mind that MySQL Proxy is useful even without a Lua script. You can specify one or more backend IP addresses and ports, which makes load balancing and fail over as easy as:

Code:
$ mysql-proxy \
--proxy-backend-addresses=10.0.1.2:3306 \
--proxy-backend-addresses=10.0.1.3:3306 &
With MySQL Proxy, a couple Lua scripts, and a little imagination, you can solve a variety of problems that were very difficult if not impossible to accomplish previously. From fixing common spelling mistakes, to masking passwords, to completely rewriting queries based on your specific requirements, the possibilities are nearly endless.

by acid_kewpie on Thu, 2007-12-13 10:41
Great idea for a project, that's for sure. I use F5 load balancers and they've recently introduced intergrated support for http redirection based on hostname etc... and this is clearly a nice equivalent for that. also means that presumably there's no chance of hopping databases with an insecure model... i was looking for an oracle or mssql equivalent only a few weeks ago but no luck...


  



All times are GMT -5. The time now is 09:44 AM.

Main Menu
Advertisement
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration