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.

Advantages:
  • The program automatically switches among multiple data sources with ease.
  • Middleware is not required.
  • Theoretically, all databases are supported.
Disadvantages:
  • Manual operations are not supported.
  • Data sources cannot be dynamically added.
You can use one of the following methods to split read and write operations at the system layer:
  • 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.

The following figure shows the principle of MySQL Proxy.

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.

Advantages:
  • Read/write splitting can be implemented without modifying the source program.
  • Data sources can be added dynamically without restarting the program.
Disadvantages:
  • The program relies on the middleware, which makes it difficult to switch databases.
  • Performance decreases because the middleware serves as a forwarding proxy.

Procedure

Perform the following operations to use MySQL Proxy to split read and write operations:
  1. Step 1. Preparations
  2. Step 2. Configure read/write splitting
  3. Step 3. Grant permissions
  4. Step 4. Test read/write splitting

Step 1. Preparations

The following section describes the environment:
  • 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:

  1. Create three ECS instances and install MySQL.
  2. Build primary/secondary databases and ensure data consistency between them.
  3. Modify the MySQL configuration file of the primary/secondary environment.
    • Primary environment: 
      vim /etc/my.cnf
[mysqld]
server-id=202                  #Set the unique ID of the server. The default ID is 1.
log-bin=mysql-bin              # Enable binary logs.
    • Secondary environment: 
      [mysqld]
server-id=203
  4. Restart the MySQL service on the primary/secondary servers. 
    /etc/init.d/mysqld restart
  5. Create an account on the primary server and grant permissions to the secondary server. 
    mysql -uroot -p95c7586783
grant replication slave on *.* to 'syncms'@'Enter secondary-IP address' identified by '123456';
flush privileges;
  6. Check the status of the primary database. 
    mysql> show master status;
  7. Configure the secondary database. 
    change master to master_host='Enter primary-IP address', master_user='syncms',      master_password='123456', master_log_file='mysql-bin.000005', master_log_pos=602;
  8. Start the secondary synchronization process and check the status. 
    start slave;
show slave status\G
  9. Verify the synchronization between the primary and secondary databases.
    1. Write data to the testproxy.test1 table in the primary database. 
      mysql> create database testproxy;
mysql> create table testproxy.test1(ID int primary key,name char(10) not null);
mysql> insert into testproxy.test1 values(1,'one');
mysql> insert into testproxy.test1 values(2,'two');
mysql> select * from testproxy.test1;
    2. Run the following command in the secondary database to query data in the testproxy.test1 table: 
      select * from testproxy.test1;
      If the content in testproxy.test1 is the same as that in the primary database, data is synchronized between the the primary and secondary databases.

Step 2. Configure read/write splitting

Perform the following operations to configure read/write splitting:

  1. Install MySQL Proxy. 
    wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
mkdir /alidata
tar xvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz 
mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/  /alidata/mysql-proxy-0.8.5
  2. Set environment variables. 
    vim /etc/profile                     #Add the following information:
PATH=$PATH:/alidata/mysql-proxy-0.8.5/bin
export $PATH
source /etc/profile                  #Validate the environment variables.
mysql-proxy -V
  3. Set the read/write splitting parameters. 
    cd /alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/
vim  rw-splitting.lua

    MySQL Proxy will detect client connections. If the number of connections does not exceed the preset value of min_idle_connections, read/write splitting will not be performed. By default, read/write splitting will be performed for at least four connections and at most eight connections. To simplify the test of read/write splitting, set the number of connections to one at least and two at most. For the production environment, you can set the number based on the actual conditions.

    Before the modification:
    After the modification:
  4. Copy the Lua administration script admin.lua to the directory where the read/write splitting script rw-splitting.lua is located. 
    cp /alidata/mysql-proxy-0.8.5/lib/mysql-proxy/lua/admin.lua /alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/

Step 3. Grant permissions

Perform the following operations to grant permissions:

  1. Grant permissions in the primary database. The permissions will also be granted in the secondary database due to synchronization between the primary and secondary databases. 
    mysql -uroot -p95c7586783
grant all on *.* to 'mysql-proxy'@'Enter <MySQL Proxy IP>' identified by '123456';
flush privileges;
  2. Start MySQL Proxy. 
    mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins=proxy -b Enter <primary-IP address>:3306 -r Enter secondary-IP:3306 --proxy-lua-script="/alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/admin.lua"
  3. Check the port and related processes. 
    netstat -tpln
    ps -ef | grep mysql

Step 4. Test read/write splitting

Perform the following operations to test read/write splitting:

  1. Disable secondary replication. 
    stop slave;
  2. Log on to the backend of MySQL Proxy. 
    mysql  -u  admin -padmin -P 4041 -h MySQL-Proxy-IP
select * from backends;                #Check the status.
    The first connection will be established to the primary database. 
    mysql -umysql-proxy -p123456 -h 116.62.xx.xx -P 4040
insert into testproxy.test1 values(3,'three');             #Add a data record. Secondary replication is disabled. Therefore, the record exists in the primary database but does not exist in the secondary database.

    Create additional test connections. If the data displayed in the testproxy.test1 table in the primary database is the same as that in the secondary database, read/write splitting is successful. 

    mysql -umysql-proxy -p123456 -h 116.62.xx.xx -P 4040
select * from testproxy.test1;