How to Set Up MySQL Read/Write Splitting with MySQLProxy

MySQL Read/Write Splitting Overview

MySQL read/write splitting works by letting the master handle write operations while one or more slaves handle read operations. The master synchronizes write changes to each slave via binary logs.

Why It Improves Performance

Adding physical servers increases processing capacity.

Master and slaves handle only their respective reads or writes, greatly reducing X‑lock and S‑lock contention.

Slaves can use the MyISAM engine to boost query performance and reduce system overhead.

Writes to the master are concurrent; slaves apply changes asynchronously from the master’s binlog.

Slave‑specific parameters can be tuned to improve read performance.

Redundancy is increased, improving availability.

Introducing MySQLProxy

MySQLProxy is an official MySQL database proxy that sits between client requests and MySQL servers, creating a connection pool. All client requests go to MySQLProxy, which parses them, determines whether they are reads or writes, and forwards them to the appropriate server, also providing load balancing for multiple slave nodes.

Configuration Steps

Environment Preparation

Set up the following IP addresses:

Master: 192.168.1.5

Slave: 192.168.1.6

Proxy: 192.168.1.2

MySQL version 5.5.37 and MySQL‑proxy 0.8.4 are used.

Create User and Grant Permissions

mysql> create user libai identified by 'libai'; mysql> grant all on *.* to libai@'192.168.1.%' identified by 'libai';

When replication is configured, these commands executed on the master are replicated to the slave.

Enable MySQL Replication

Stop any existing replication and reset slaves:

mysql> stop slave; mysql> reset slave all;

Configure the master connection:

mysql> change master to master_host='192.168.1.5',master_user='libai',master_password='libai',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=0;

Test Master and Slave

On the master:

# mysql -h localhost -ulibai -plibai mysql> create database d; mysql> use d; mysql> create table t(i int); mysql> insert into t values(1);

On the slave:

mysql> select * from t;

Start MySQLProxy

Create a system user for the proxy and extract the binary:

# groupadd mysql # useradd -g mysql mysql # ./mysql-proxy --daemon --log-level=debug --user=mysql --keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.1.5:3306" --proxy-read-only-backend-addresses="192.168.1.6:3306" --proxy-lua-script="/root/soft/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/root/soft/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

Port 4040 is the proxy port; 4041 is the admin port. Verify the processes with ps -ef | grep mysql and check listening ports with lsof -i:4040 and lsof -i:4041.

Testing the Setup

Connect through the proxy (port 4040) and list databases:

# mysql -h 192.168.1.2 -ulibai -p --port=4040 mysql> show databases;

Insert data via the proxy and query through the slave to observe read/write separation:

mysql> insert into t values(3,'this_is_proxy'); mysql> select * from t;

The query returns rows from the slave, while the inserted row resides on the master, confirming that MySQLProxy correctly separates reads and writes.

After re‑enabling replication, queries through the proxy return data from the slave that now includes the master’s writes, demonstrating successful synchronization.