I. Overview:

After the master-slave structure of MySQL database is configured, under normal circumstances, all the read-write operations of the database are on the master database. The slave database is only used as data backup. Obviously, the server resources can not be used effectively, so the requirement of realizing the read-write separation is unavoidable.

2. Description of the topology diagram:

MySQL002[1].png

As shown in the figure above, this paper realizes the write operation (add, delete and change) of reading MySQL database on Master server (192.168.4.10), while the read operation (query) of MySQL database on Slave server (192.168.4.20).

If a programmer creates two database Connection Connections while programming, one database Connection is used to read the database in the program, and another database Connection is used to write the database, the read-write separation of the database can also be realized. But obviously this way of implementation is not flexible enough, so it is not generally used.

In this paper, MaxScale software will be used to realize the separation of reading and writing of MySQL database. MaxScale is a MySQL database middleware developed by http://www.skysql.com/. It supports high availability, load balancing, good scalability, high performance event-driven middleware with agent and management functions. The software will be deployed on MySQL proxy server (192.168.4.100) in the topology diagram. The client's access request will point to MySQL proxy server. The server will point the write operation to the database to 192.168.4.10 and the read operation to the database to 192.168.4.20.

3. Configuration description:

1. MaxScale configuration

(1) Modify the configuration file:

  [root@100 ~]# vim /etc/maxscale.cnf

      A,Specify a database server
         [server1]
            type=server
            address=192.168.4.10        
            port=3306
            protocol=MySQLBackend

         [server2]
            type=server
            address=192.168.4.20
            port=3306
            protocol=MySQLBackend

      B,Specify the database server to monitor
         [MySQL Monitor]
           type=monitor
           module=mysqlmon
           servers=server1,server2
           user=scalemon                #User name of proxy server monitoring service connecting to database server (need to be authorized on database server)
           passwd=123456                #Proxy Server Monitoring Service Connecting to Database Server Password

           monitor_interval=10000        //Time interval for active check, 10,000 milliseconds = 10 seconds
      C,Specify between which hosts read-write separation is performed
         [Read-Write Service]
             type=service
             router=readwritesplit
             servers=server1,server2
             user=maxscale               #User name used to check whether the authorized user is legitimate when the user connects to the database (you need to have select privileges for the mysql database)
             passwd=123456               #User name used to check whether the authorized user is legitimate when the user connects to the database          

             max_slave_connections=100%
      D,Define management services and approaches (no change)
         [MaxAdmin Service]
             type=service
             router=cli
      E,Read-only server configuration options (no, all commented out)
         [Read-Only Service]
             #type=service
             #router=readconnroute
             #servers=server1
             #user=myuser
             #passwd=mypwd
             #router_options=slave

             #[Read-Only Listener]
             #type=listener
             #service=Read-Only Service
             #protocol=MySQLClient
             #port=4008
      F,Listening Port Configuration for Read-Write Separation
         [Read-Write Listener]
             type=listener
             service=Read-Write Service
             protocol=MySQLClient
             port=4006                    //Client Read-Write Separation Service Access Port
     G,Monitoring Port Configuration for Management Services
        [MaxAdmin Listener]
             type=listener
             service=MaxAdmin Service
             protocol=maxscaled
             socket=default
             port=4009                    //Ports used by management services

2. Adding Authorization to the Main Database Server
mysql > grant replication slave,replication client on . to scalemon@"%" identified by "123456";
mysql > grant select on mysql.* to maxscale@"%" identified by "123456";

3. Start the service on the proxy server and check the status of the database server

(1) Start up service:

    [root@100 ~]# maxscale --config=/etc/maxscale.cnf

(2) Check status

    [root@100 ~]# maxadmin -uadmin -pmariadb -P4009
        MaxScale> list servers
        -------------------+-----------------+-------+-------------+--------------------
        Server             | Address         | Port  | Connections | Status              
        -------------------+-----------------+-------+-------------+--------------------
        server1            | 192.168.4.11    |  3306 |           0 | Master, Running
        server2            | 192.168.4.12    |  3306 |           0 | Slave, Running
        -------------------+-----------------+-------+-------------+-------------------- 

4. Verification test

In 192.168.4.120, the authorized user was used to connect the proxy server (192.168.4.100:4006), and some data of Insert was read from the database server (192.168.4.20) on the database server (192.168.4.20), which indicated that the client read the data from the database (192.168.4.20); in 192.168.4.120, the authorized user was used to connect the proxy server (192.168.4.100:4006), Insert. Some data are successful, as can be seen from the master and slave databases, indicating that the client writes data on the master database server (192.168.4.10).