MySQL Proxy is an official middleware solution from MySQL that provides load balancing, read-write splitting, and failover capabilities. It acts as a connection pool between the application and the data base servers, forwarding frontend requests to the appropriate backend databases. By using Lua scripts, it can implement complex connection control and filtering, enabling read-write separation and load balancing. From the application’s perspective, MySQL Proxy is completely transparent—the application simply connects to the proxy’s listening port.

One concern is that the proxy machine could become a single point of failure. However, this can be mitigated by deploying multiple proxy machines for redundancy and configuring the application’s connection pool with multiple proxy addresses.

The SQL queries do not go directly to the master or slave databases. Instead, they reach the proxy, which determines whether the query is a write operation (INSERT, UPDATE, DELETE) or a read operation (SELECT). Write queries are forwarded to the master server, while read queries are directed to a slave server.

Use Case: In development, certain SQL statements might lock tables, temporarily preventing read operations and impacting business. With master-slave replication and read-write splitting, the master handles writes and the slave handles reads. Even if the master experiences a table lock, reading from the slave keeps the business running.

Deploying MySQL Proxy

Before starting, ensure a master-slave replication setup exists between two MySQL servers. In this example, the master (write) is at IP 192.168.1.118, the slave (read) is at 192.168.1.117, and the proxy will be installed on IP 192.168.1.116.

1. Install MySQL Proxy

tar -zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local
mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy

2. Create Configuration File

The configuraton file must contain no comments, as comments may cause startup errors.

vi /etc/mysql-proxy.conf
[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-backend-addresses=192.168.1.118:3306
proxy-read-only-backend-addresses=192.168.1.117:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
plugins=proxy
log-level=debug
keepalive=true
daemon=true

Explanation of parameters:

• proxy-address: Listen on all IPs of this machine on port 3306.
• proxy-backend-addresses: The master database server for write operations.
• proxy-read-only-backend-addresses: The slave database server for read operations.
• proxy-lua-script: Path to the Lua script handling read-write splitting.
• pid-file and log-file: Paths for PID and log files.
• plugins=proxy: Enable the proxy plugin.
• log-level=debug: Set log level for debugging.
• keepalive=true: Enable heartbeat check.
• daemon=true: Run as a daemon.

3. Create Log Directory

mkdir /usr/local/mysql-proxy/log

4. Modify Lua Script for Read-Write Splitting

By default, read-write splitting activates only after more than 4 connections. Adjust the thresholds to start after 2 connections.

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
# Change these values:
min_idle_connections = 1,
max_idle_connections = 2,

5. Start MySQL Proxy

Set proper permissions on the config file and start the proxy.

chmod 660 /etc/mysql-proxy.conf
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.conf

6. Test Remote Connection

Verify that you can connect to the proxy from a remote client using the proxy’s IP.

mysql -h 192.168.1.119 -uroot -proot

Testing Read-Write Splitting

To verify that splitting works:

1. Temporarily stop replication between master and slave.
2. Open several connections to the proxy (more than the configured idle thresholds).
3. Execute a write operation (e.g., INSERT) from one connection.
4. Execute read operations (SELECT) from other connections.
5. If the reads return data that does not reflect the write just performed (due to the stopped replication), read-write splitting is working correctly.

Considerations

Read-write splitting relies on master-slave replication, which introduces potential replication lag. When large batch operations occur, the slave may fall behind the master, causing read operations to return stale data. This inconsistency must be accounted for in application design, especially when immediate consistency is required.