There are a number of different ways to use MySQL Proxy. At the most basic level, you can allow MySQL Proxy to pass on queries from clients to a single server. To use MySQL proxy in this mode, you just have to specify the backend server that the proxy should connect to on the command line:
$ mysql-proxy --proxy-backend-addresses=sakila:3306
If you specify multiple backend MySQL servers then the proxy will connect each client to each server in a round-robin fashion. For example, imagine you have two MySQL servers, A and B. The first client to connect will be connected to server A, the second to server B, the third to server C. For example:
$ mysql-proxy \
--proxy-backend-addresses=narcissus:3306 \
--proxy-backend-addresses=nostromo:3306When you have specified multiple servers in this way, the proxy will automatically identify when a MySQL server has become unavailable and mark it accordingly. New connections will automatically be attached to a server that is available, and a warning will be reported to the standard output from mysql-proxy:
network-mysqld.c.367: connect(nostromo:3306) failed: Connection refused network-mysqld-proxy.c.2405: connecting to backend (nostromo:3306) failed, marking it as down for ...
Lua scripts enable a finer level of control, both over the
connections and their distribution and how queries and result sets
are processed. When using an Lua script, you must specify the name
of the script on the command line using the
--proxy-lua-script option:
$ mysql-proxy --proxy-lua-script=mc.lua --proxy-backend-addresses=sakila:3306
When you specify a script, the script is not executed until a connection is made. This means that faults with the script will not be raised until the script is executed. Script faults will not affect the distribution of queries to backend MySQL servers.
Because the script is not read until the connection is made, you can modify the contents of the Lua script file while the proxy is still running and the script will automatically be used for the next connection. This ensures that MySQL Proxy remains available because it does not have to be restarted for the changes to take effect.
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)