Read-write separation with mycat: master-slave replication based on MySQL

Posted by Corin on Mon, 01 Jul 2019 00:21:11 +0200

Based on MySQL master-slave replication, this paper uses mycat to realize read-write separation.

The role of mycat in applications can be seen in the following figure:

mycat allows programmers to only care about business code writing, without worrying about load balancing of back-end database cluster, separation of reading and writing, data fragmentation logic writing of sub-database and sub-table, as long as they connect mycat directly.

First we prepare a clean centos machine and install jdk

Unzip the installation package of mycat to / user/local / below

Setting environment variables for mycat

vi /etc/profile

Make the configuration file take effect immediately

source /etc/profile

Modify configuration files

Configure server.xml first

<?xml version="1.0" encoding="UTF-8"?>  
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");   
    - you may not use this file except in compliance with the License. - You   
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0   
    - - Unless required by applicable law or agreed to in writing, software -   
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT   
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the   
    License for the specific language governing permissions and - limitations   
    under the License. -->  
<!DOCTYPE mycat:server SYSTEM "server.dtd">  
<mycat:server xmlns:mycat="http://org.opencloudb/">  
    <system>  
    <!-- Here are some system attributes that you can view for yourself. mycat File -->  
    <property name="defaultSqlParser">druidparser</property>  
    <property name="charset">utf8mb4</property>  
    </system>  
                              
    <!-- User 1, corresponding MyCat Host-slave replication cluster corresponding to data nodes connected by logical database -->  
    <user name="user1">  
        <property name="password">root</property>  
        <property name="schemas">pcx_schema</property>  
    </user>  
                              
    <!-- User 2, read-only permission-->  
    <user name="user2">  
        <property name="password">root</property>  
        <property name="schemas">pcx_schema</property>  
        <property name="readOnly">true</property>  
    </user>  
  
</mycat:server>

Two users are configured here to connect to.
user1 password root gives this user permission to pcx_schema database
user2 password root gives this user read-only access to the pcx_schema database
Note that the pcx_schema here is not necessarily the real library name on your database. You can specify it at will. Just unify it with the library name in the next schema.xml configuration file.

Next, configure schema.xml

<?xml version="1.0"?>  
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://org.opencloudb/">  
      
    <!-- Definition MyCat Logic Library -->  
    <schema name="pcx_schema" checkSQLschema="false" sqlMaxLimit="100" dataNode="pcxNode"></schema>  
  
    <!-- Definition MyCat Data Node -->  
    <dataNode name="pcxNode" dataHost="dtHost" database="pcx" />  
  
      
    <!-- Define data host dtHost，connection to MySQL Read-Write Separation Cluster ,schema Each of them dataHost Medium host Attribute values must be unique-->  
    <!-- dataHost In fact, the configuration is the database cluster in the background, one datahost Represents a database cluster -->  
    <!-- balance="1"，All-out readHost and stand by writeHost participate in select Statement load balancing-->  
    <!-- writeType="0"，All write operations are sent to the first configuration writeHost，This is ours. hostmaster，The first one hangs until the second one survives. writeHost-->  
    <dataHost name="dtHost" maxCon="500" minCon="20" balance="1"  
        writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">  
        <!--runtastic Heart Rate PRO -->  
        <heartbeat>show slave status</heartbeat>  
          
        <!--Configuring the background database IP Address and port number, and account password -->  
        <writeHost host="hostMaster" url="192.168.1.6:3306" user="root" password="root" />  
        <writeHost host="hostSlave" url="192.168.1.7:3306" user="root" password="root" />  
    </dataHost>  
      
  
</mycat:schema>

First, <schema name= "TESTDB" checkSQL schema= "false" sqlMaxLimit= "100" dataNode= "dn1">
Here TESTDB is our external claim that we have the database name, which must be consistent with the database name specified by the user in server.xml
Adding a dataNode="dn1" specifies that our library is only on dn1. No repositories are performed.

Second, <dataNode name="dn1" dataHost="localhost1" database="db1"/>
All you need to do here is change the name db1 of the database to be the database name of your real database service.
Modify it according to your own database name.

Third, <dataHost name= "localhost1" maxCon= "1000" minCon= "10" balance= "0" writeType= "0" dbType= "mysql" dbDriver= "native" switchType= "1" slave Threshold= "100">

There are only three places to configure: balance="1" and writeType = "0" and switchType = "1".
a. balance attribute load balancing type, currently there are four values:
1. balance="0", without opening the read-write separation mechanism, all read operations are sent to the currently available writeHost.
2. balance="1". All readHost and stand by writeHost participate in load balancing of select statements. In short, when the dual master-slave mode (M1 - > S1, M2 - > S2, and M1 and M2 are mutually preponderant), under normal circumstances, M2,S1,S2 all participate in load balancing of select statements.
3. balance="2", all reading operations are distributed randomly on writeHost and readhost.
4. balance="3", all read requests are randomly distributed to the corresponding readhost of wiriterHost for execution. writerHost does not bear reading pressure. Note that balance=3 is only available in 1.4 and later versions, but not in 1.3.
b. writeType attribute
There are three types of load balancing:
1. writeType="0", all write operations are sent to the first writeHost configured, the first hangs to the second surviving
WritteHost, which has been switched after restart, is recorded in the configuration file: dnindex.properties.
2. writeType="1" and all write operations are randomly sent to the configured writeHost.
3. WritteType= "2", not implemented.
c. switchType attribute
- - 1 means no automatic switching
- 1 default value, automatic switching
- 2 Decide whether to switch based on the status of MySQL master-slave synchronization

Fourth, <writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="123456">
<readHost host="hostS1" url="192.168.1.101:3306" user="root" password="123456" />
</writeHost>

Here are the IP address access ports and user names and passwords of our two Read-Write servers.

Start mycat