MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; and many more.
Commandline Syntax
To use the MySQL Proxy:[code]$ mysql-proxy --help-all
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
Help Options:
-?, --help Show help options
--help-all Show all help options
--help-admin Show options for the admin-module
--help-proxy Show options for the proxy-module
admin module
--admin-address=
proxy-module
--proxy-address=
--proxy-read-only-backend-addresses=
--proxy-backend-addresses=
--proxy-skip-profiling disables profiling of queries (default: enabled)
--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
--proxy-lua-script=
--no-proxy Don't start proxy-server
Application Options:
-V, --version Show version
--daemon Start in daemon-mode
--pid-file=
Connecting
As a simple test, just start it and try to connect to port 4040 with your mysql-client.
[code]$ mysql-proxy &
$ mysql --host=127.0.0.1 --port=4040 --user=... --password[/code]
* The MySQL Proxy will pass the connection through to port 3306 at 127.0.0.1
* IMPORTANT: The MySQL server should be 5.0.x or later. Testing has not been performed with Version 4.1 however feedback is welcome from the community.
Proxy Module
The proxy module is split into two parts:
* a core written in C
* a lua interface
The core handles the basics of packet forwarding tries to be fast and have low latency as possible and handles more than 1000 connections in parallel. Part of the core are:
* config-file handling
* mysql-protocol encoding
* socket handling
* load balancing
* fail over
[code]$ mysql-proxy --help-proxy
Usage:
mysql-proxy [OPTION...] - MySQL Proxy[/code]
[code]proxy-module
--proxy-address=
--proxy-read-only-address=
--proxy-backend-addresses=
--proxy-profiling enable profiling of queries
--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
--proxy-lua-script=
The --proxy-address is the port where mysql connects to get forwarded to one of the backends.
The backends are announced with --proxy-backend-addresses which defaults to 127.0.0.1:3306. You can specify this option several times to add more backends.
Admin Server
The admin-server is the most basic implementation of the MySQL server protocol and can respond to some basic queries. It implements:
* socket handling
* the life-cycle of a connection
* mysql wire-protocol
* len-encoding of some fields
* field-types
* result-sets
While the design is based on the ideas from lighttpd in the way that it is using non-blocking network-io the network-protocol is based on the information available in the internals document from dev.mysql.com
The admin-servers implements 2 basic queries which are issued by the mysql command-line client:
[code]select @@version_comment LIMIT 1;
select USER();[/code]
Using the admin server you can implement the functionality in a way that every mysql client (php, jdbc, odbc, perl, ...) can execute them.
We use it to export the current config and to track the open connections:
[code]> select * from proxy_connections;
+------+--------+-------+-------+
| id | type | state | db |
+------+--------+-------+-------+
| 2 | proxy | 8 | world |
| 3 | server | 8 | |
+------+--------+-------+-------+[/code]
and the config:
[code]> select * from proxy_config;
+---------------------------------+----------------+
| option | value |
+---------------------------------+----------------+
| admin.address | :4041 |
| proxy.address | :4040 |
| proxy.backend_addresses[0] | 127.0.0.1:3306 |
| proxy.backend_addresses[1] | 127.0.0.1:3307 |
| proxy.fix_bug_25371 | 0 |
| repclient.master_address | |
+---------------------------------+----------------+[/code]
Load Balancing & Failover
How about some load-balancing and fail-over?
[code]$ mysql-proxy \
--proxy-backend-addresses=10.0.1.2:3306 \
--proxy-backend-addresses=10.0.1.3:3306 &[/code]
Run your tests, shut down one of the backends and see how the MySQL Proxy sends all traffic to the one which is still alive.
Scripting
MySQL Proxy includes lua script support. Lua is a simple and fast embeddable script language. Tutorial scripts are posted as snippets here; we encourage you to contribute your own! Add new snippets here, and please tag them with mysqlproxy.
We use a state-machine which maps the basic stages of the MySQL protocol:
With the lua scripts you can hook into 3 stages right now:
* connect_server
* read_query
* read_query_result
If you want to write a load balancer you can hook into connect_server which is called before we connect to a backend server. The load-balancer can pick a backend from a list of backends.
read_query is the stage where we read the query from the client before we send it to the server. In this stage you can decide if you want to pass the query on as is, rewrite it, inject more queries or respond directly to the client without forwarding the packet to the server.
For example you can dump all the data which is transfered between client and server (after the authentication stage):
[code] (sqf) taking 127.0.0.1:3306, clients: 0
.--- mysql result packet
| query.len = 13
| query.packet = 03 73 68 6f 77 20 65 6e 67 69 6e 65 73
| .--- query
| | command = COM_QUERY
| | query = "show engines"
| '---
|
| result.len = 1
| result.packet = 06
| .---
| | command = COM_QUERY
| | num-cols = 6
| | field[0] = { type = 253, name = Engine }
| | field[1] = { type = 253, name = Support }
| | field[2] = { type = 253, name = Comment }
| | field[3] = { type = 253, name = Transactions }
| | field[4] = { type = 253, name = XA }
| | field[5] = { type = 253, name = Savepoints }
| | row[0] = { ndbcluster, DISABLED, Clustered, fault-tolerant tables, YES, NO, NO }
| | row[1] = { MRG_MYISAM, YES, Collection of identical MyISAM tables, NO, NO, NO }
| | row[2] = { BLACKHOLE, YES, /dev/null storage engine (anything you write to it disappears), NO, NO, NO }
| | row[3] = { CSV, YES, CSV storage engine, NO, NO, NO }
| | row[4] = { MEMORY, YES, Hash based, stored in memory, useful for temporary tables, NO, NO, NO }
| | row[5] = { FEDERATED, YES, Federated MySQL storage engine, YES, NO, NO }
| | row[6] = { ARCHIVE, YES, Archive storage engine, NO, NO, NO }
| | row[7] = { InnoDB, YES, Supports transactions, row-level locking, and foreign keys, YES, YES, YES }
| | row[8] = { MyISAM, DEFAULT, Default engine as of MySQL 3.23 with great performance, NO, NO, NO }
| '---
'---[/code]