MySQL Forge

Resources for the MySQL Community

MySQL Proxy

Introduction

Welcome to the MySQL Proxy project page. MySQL Proxy is created & maintained as a MySQL Enterprise Tools project by Jan Kneschke and Kay Röpke.

NEWS

MySQL Proxy's code has moved from Subversion to Bazaar/Launchpad. Please check out the new project page for it.

What is MySQL Proxy?

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.

Coming Soon!

MySQL Proxy is released under a GPL license; in addition, MySQL Enterprise will include several products based on the MySQL Proxy core technology. More information coming soon.

Downloads

• On the official download pages at https://dev.mysql.com/downloads/mysql-proxy/ you'll find:
  • Binary packages for all MySQL Enterprise supported platforms - binaries for the following platforms are currently missing but will be coming soon:
    • Windows (libevent build problem, will resolve quickly)
    • HP/UX (libevent patch needed, will take a little longer)
  • Source code releases including build and installation instructions can be found in the Source downloads package near the end of the download list. Also check the Compiling Instructions for details.
• Alternative RPM packages of the MySQL Proxy (including experimental SVN snapshots) for various Linux distributions (e.g. Fedora 5/6, SLES 9/10, SuSE Linux) can also be obtained from the openSUSE Build Service at the following location: https://download.opensuse.org/repositories/server:/database/ - see the Instructions on the openSUSE Wiki on how to use the Build Service.
• mySQL Proxy init script to RedHat
• Debian contains a package of MySQL Proxy in the official Debian repository for unstable and testing, and an up-to-date package for stable on backports.org.

Development source code

See the separate Wiki page about MySQL Proxy Compiling for more information on how to check out the development source tree and compile the sources for various operating systems.

Getting Started

Check the official documentation in the MySQL Reference manual.

You can also start with this article about Getting started with MySQL Proxy

Also take a look at this MySQL University session: MySQL Proxy Overview

Commandline Syntax

To use the MySQL Proxy:

$ 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=<host:port>                         listening address:port of internal admin-server (default: :4041)
proxy-module
  --proxy-address=<host:port>                         listening address:port of the proxy-server (default: :4040)
  --proxy-read-only-backend-addresses=<host:port>     address:port of the remote slave-server (default: not set)
  --proxy-backend-addresses=<host:port>               address:port of the remote backend-servers (default: 127.0.0.1:3306)
  --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=<file>                           filename of the lua script (default: not set)
  --no-proxy                                          Don't start proxy-server
Application Options:
  -V, --version                                       Show version
  --daemon                                            Start in daemon-mode
  --pid-file=<file>                                   PID file in case we are started as daemon

Connecting

As a simple test, just start it and try to connect to port 4040 with your mysql-client.

 $ mysql-proxy &
 $ mysql --host=127.0.0.1 --port=4040 --user=... --password

• 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

 $ mysql-proxy --help-proxy
 Usage:
   mysql-proxy [OPTION...] - MySQL Proxy

 proxy-module
   --proxy-address=<ip:port>               listening address:port of the proxy-server (default: :4040)
   --proxy-read-only-address=<ip:port>     listening address:port of the proxy-server for read-only connection (default: :4042)
   --proxy-backend-addresses=<ip:port>     address:port of the remote backend-servers (default: not set)
   --proxy-profiling                       enable profiling of queries
   --proxy-fix-bug-25371                   fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
   --proxy-lua-script=<file>               filename of the lua script (default: not set)

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:

select @@version_comment LIMIT 1;
select USER();

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:

> select * from proxy_connections;
+------+--------+-------+-------+
| id   | type   | state | db    |
+------+--------+-------+-------+
|    2 | proxy  | 8     | world |
|    3 | server | 8     |       |
+------+--------+-------+-------+

and the config:

> 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        |                |
+---------------------------------+----------------+

Load Balancing & Failover

How about some load-balancing and fail-over?

 $ mysql-proxy \
    --proxy-backend-addresses=10.0.1.2:3306 \
    --proxy-backend-addresses=10.0.1.3:3306 &

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

 (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 }
 | '---
 '---

The Proxy Cookbook

The proxy is a revolutionary way of solving problems. There is a growing collection of MySQL Forge snippets. Their description, categorization, and documentation is kept in the Proxy Cookbook

Articles and blog posts about MySQL Proxy

Articles

• Getting started with MySQL Proxy

Blog posts

• MySQL Proxy 0.6.0 released
• Binary builds of MySQL Proxy available via the openSUSE build service
• MySQL Proxy: Tracking Parallel Queries
• MySQL Proxy: more R/W splitting
• MySQL Proxy: Query Stats
• Another step forward for MySQL Proxy
• mysql-proxy on ubuntu (and debian)
• mysql-proxy on ubuntu 7.04 feisty
• MySQL Proxy and a Global Transaction ID
• MySQL Proxy learns R/W Splitting
• MySQL Proxy 0.5.0 released
• MySQL Proxy - An excellent excuse to learn a new language
• Got MySQL Proxy yet!
• Your first macros with MySQL Proxy
• MySQL Proxy - Playing with the tutorials
• Packaging and Installing the MySQL Proxy with RPM
• Building MySQL Proxy on Mac OS X

FAQ

Visit the MySQL Proxy Frequently Asked Questions page

Getting Help

• Discuss ideas & issues on the MySQL Proxy Forum
• View & report bugs on https://bugs.mysql.com (set the Category to MySQL Proxy: Core, Scripts or Docs)

Who's who

name	role	picture
Jan Kneschke	main author design,implementation,scripts
Lenz Grimmer	RPM packaging, openSUSE packages
Martin "MC" Brown	documentation
Giuseppe Maxia	testing, test scripts, tutorial/application scripts,
Kay Röpke	implementation, load balancer, scripts

This page is under construction... If there are problems with this page, please send us a problem description.