Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
By jeremy at 2007-12-13 10:15
Understanding MySQL Proxy
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:
$ svn co http://svn.mysql.com/\
$ cd mysql-proxy/trunk
$ ./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.
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.
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:
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.