Typically, system performance decreases when reads and writes occur in the same database server. To improve overall system performance and optimize user experience, you can reduce the load of your primary database through read/write splitting. This topic describes how to use MySQL Proxy to split read and write operations.
Prerequisites
An Alibaba Cloud account is created. To create an Alibaba Cloud account, go to the Alibaba Cloud official website.
Background information
At the application layer, read/write splitting is implemented through coding. Before you enter the service layer, Aspect-Oriented Programming (AOP) is used to determine whether to use the read database or the write database. The method names can be used to implement the target action. For example, the read database is used for method names that start with query, find, or get, and the write database is used for others.
- The program automatically switches among multiple data sources with ease.
- Middleware is not required.
- Theoretically, all databases are supported.
- Manual operations are not supported.
- Data sources cannot be dynamically added.
- Distributed Relational Database Service (DRDS)
- MySQL Proxy
The following section describes how to use MySQL Proxy to split read and write operations.
MySQL Proxy is a simple program that is situated between your client and MySQL server and can monitor, analyze, or transform communication between the server and the client. It can serve a wide variety of purposes, such as load balancing, fault query and analysis, and query filtering and modification.

MySQL Proxy is an intermediate-layer proxy that acts as a connection pool to forward connection requests from frontend applications to the backend database. MySQL Proxy can perform complex connection control and filtering to implement read/write splitting and load balancing by using the Lua script. MySQL Proxy allows applications to access the backend database smoothly. The applications only need to be connected to the listening port of MySQL Proxy. In this case, the proxy server may become a single point of failure (SPOF). You can use multiple proxy servers to implement redundancy. Therefore, you only need to configure multiple proxy connections in the connection pool of the application server.
- Read/write splitting can be implemented without modifying the source program.
- Data sources can be added dynamically without restarting the program.
- The program relies on the middleware, which makes it difficult to switch databases.
- Performance decreases because the middleware serves as a forwarding proxy.
Procedure
Step 1. Preparations
- Primary database IP address: 121.40.xx.xx
- Secondary database IP address: 101.37.xx.xx
- MySQL Proxy IP address: 116.62.xx.xx
Perform the following operations to prepare for the installation:
Step 2. Configure read/write splitting
Perform the following operations to configure read/write splitting:
Step 3. Grant permissions
Perform the following operations to grant permissions:
Step 4. Test read/write splitting
Perform the following operations to test read/write splitting: