MyCat realizes the separation of reading and writing + sub database and sub table + global table

Preface

MYCAT has powerful functions and simple configuration. As a database middleware, it is a very excellent open source product. For the deployment and installation of MYCAT, please refer to the blog: MyCat deployment installation.

This post will show the following functions of mycat:

  • Separation of reading and writing;
  • Sub warehouse and sub table;
  • Global table;

The environment is as follows:

system IP host name service
Centos 7.5 192.168.20.2 mysql01 MySQL 5.7.24
Centos 7.5 192.168.20.3 mysql02 MySQL 5.7.24
Centos 7.5 192.168.20.3 mysql03 MySQL 5.7.24
Centos 7.5 192.168.20.4 mycat Mycat

Note: the host mysql01 and mysql02 are the primary and secondary replication relationships. Please refer to the blog MySQL high availability solution - dual primary (Note: you only need to refer to the blog to make a master-slave effect. You don't need two masters or kept for high availability). Please refer to the blog for mycat service MyCat deployment installation To build, the newly added host mysql03 is a newly built MySQL server. Please refer to the blog Centos deploy MySQL 5.7 To build.

1. Separation of reading and writing by mycat

The mysql03 host is not used here, and the implementation of read-write separation is relatively simple. Only the following two configuration files of mycat need to be modified. As follows:

1) Modify the server.xml file

[root@mycat mycat]# pwd
/usr/local/mycat   <!--Here is mycat Home directory-->
[root@mycat mycat]# vim conf/server.xml
<!--Appoint client Connect mycat The user name and password of MySQL Database independent-->
[root@mycat mycat]# VIM conf / server.xml <! -- locate to about 80 lines, and modify as follows -- >
        <user name="mycat" defaultAccount="true">  <!--mycat Username-->
                <property name="password">pwd@123</property>   <!--User here mycat Login password for-->
                <property name="schemas">test_mycat</property>    <!--Logical library name here--

                <!-- Table level DML Permission settings -->
                <!--            
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>

2) Modify schema.xml file

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--Underneath schema Must match the schema name(Logical library name).-->
        <schema name="test_mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="test" />    <!--There database It specifies which database to connect to the backend. Here, it connects to the test library-->
                <!--The following is the specified backend real MySQL Host, about balance Value, with three optional values, to be written down below-->
        <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                                <!--Specify which host on the back end is used to write data-->
                <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                                   password="123.com">
                        <!-- can have multi read hosts -->
                                                <!--Specify which host on the back end is used to read data-->
                        <readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
                </writeHost>
        </dataHost>
</mycat:schema>

Note: the data host field balance load balancing type mentioned above has the following four values at present:

  1. balance="0", do not turn on the read-write separation mechanism. All read operations are sent to the currently available writeHost (default).
  2. balance="1", all readhosts and stand by writehosts participate in the load balancing of select statements. In short, when the dual master and dual slave modes (M1 - > S1, M2 - > S2, and M1 and M2 are primary and standby for each other), M2, S1 and S2 normally participate in the load balancing of select statements.
  3. balance="2", all read operations are randomly distributed 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 the reading pressure. Note that balance=3 only exists in 1.4 and later versions, but not in 1.3.

The writeTyep field has three values:

1. writeType="0", all write operations are sent to the first configured writeHost, and the first one is hung to the second surviving writeHost. After restart, the one that has been switched will prevail. The switch is recorded in the configuration file: dnindex.properties.
2. writeType="1", all write operations are randomly sent to the configured writeHost. It is not recommended to discard after 1.5. switchType property

  • -1 means no automatic switching.
  • 1 default, auto switch.
  • 2. Decide whether to switch based on the status of MySQL master-slave synchronization.

The modified full configuration file is as follows (some notes have been deleted to avoid redundancy):

The content of the server.xml configuration file is as follows:

[root@mycat mycat]# vim conf/server.xml 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="useSqlStat">0</property>  <!-- 1 To turn on real-time statistics, 0 to turn off -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1 To enable full overtime consistency detection, 0 to close -->

                <property name="sequnceHandlerType">2</property>
                <property name="processorBufferPoolType">0</property>
                <property name="handleDistributedTransactions">0</property>

                <property name="useOffHeapForMerge">1</property>
                <property name="memoryPageSize">1m</property>
                <property name="spillsFileBufferSize">1k</property>

                <property name="useStreamOutput">0</property>
                <property name="systemReserveMemorySize">384m</property>

                <property name="useZKSwitch">true</property>

        </system>
        <user name="mycat" >
                <property name="password">pwd@123</property>
                <property name="schemas">test_mycat</property>
        </user>
</mycat:server>

The content of the schema.xml configuration file is as follows:

[root@mycat mycat]# vim conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="test_mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="test" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                                   password="123.com">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
                </writeHost>
        </dataHost>
</mycat:schema>

Note: at this point, users with relevant permissions are created on the back-end database by themselves. For example, if the user values of writehost and readhost fields specified in the datahost field above are root, the root account of the database should have corresponding login permissions (by default, root users are only allowed to log in at localhost, so they need to modify root permissions or change to other users with permissions).

Conclusion:
1. The designated back-end database login user must have corresponding permissions;
2. The backend real database specified above is test, so the backend must have this test library;

3) Start mycat and test the effect of read-write separation

[root@mycat mycat]# mycat start       #Start mycat
[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066   #Log in to mycat
#192.168.20.4 is the host IP of mycat, the listening port of mycat is 8066, and the login user is the user created in the server.xml file
#Create a table and insert some test data
mysql> use test_mycat;
mysql> create table t1 (id int,name varchar(4));
mysql> insert into t1 values(1,'Zhang San'),(2,'Li Si'),(3,'Wang Wu');
#At this time, go to the back-end database to view the above data.
#Now you can temporarily shut down the master-slave replication of the back-end database, and then continue to insert data on the mycat host to test whether it can read
#Close the master-slave copy command: stop slave;
#The following operations are still performed on mycat host
mysql> insert into t1 values(4,'Zhao Liu');    #Insert a piece of data
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;      #The data just inserted cannot be queried, indicating that the read-write operation is not performed on the same host
+------+--------+
| id   | name   |
+------+--------+
|    1 | Zhang San   |
|    2 | Li Si   |
|    3 | Wang Wu   |
+------+--------+
#You can go to two back-end databases to view data separately
#The data of mysql01 is as follows (the write operation is in mysql01):
mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Zhang San   |
|    2 | Li Si   |
|    3 | Wang Wu   |
|    4 | Zhao Liu   |
+------+--------+
4 rows in set (0.00 sec)
#The data of mysql02 is as follows:
mysql> select * from t1(Explain that the read operation is in mysql02);
+------+--------+
| id   | name   |
+------+--------+
|    1 | Zhang San   |
|    2 | Li Si   |
|    3 | Wang Wu   |
+------+--------+
3 rows in set (0.00 sec)

This is the end of the read-write separation effect. (in order not to affect the later test, it is better to restore the master-slave replication of mysql01 and mysql02 hosts.).

2. mycat implementation sub Library

A database consists of many tables. Each table corresponds to different businesses. The so-called sub database is to classify the tables according to businesses, but not to separate them into different databases. In this way, the data or pressure will be shared among different databases. The principle of sub database is: tables with close relationship should be in a database, and tables without or not closely related to each other can be divided To a different library.

Example of sub base:

Suppose there are four tables: customer, order, order detail, dict order type. Each table has millions of data. If these four tables are to be divided into databases, you can separate the customer table into one database and the other three tables are in another database.

1) Modify the server.xml file (just change the logical library name)

[root@mycat mycat]# cat conf/server.xml 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
    <property name="useSqlStat">0</property>  <!-- 1 To turn on real-time statistics, 0 to turn off -->
    <property name="useGlobleTableCheck">0</property>  <!-- 1 To enable full overtime consistency detection, 0 to close -->

        <property name="sequnceHandlerType">2</property>
        <property name="processorBufferPoolType">0</property>
        <property name="handleDistributedTransactions">0</property>

        <property name="useOffHeapForMerge">1</property>
        <property name="memoryPageSize">1m</property>
        <property name="spillsFileBufferSize">1k</property>

        <property name="useStreamOutput">0</property>
        <property name="systemReserveMemorySize">384m</property>

        <property name="useZKSwitch">true</property>

    </system>
    <user name="mycat" >
        <property name="password">pwd@123</property>
        <property name="schemas">test_db</property>
    </user>
</mycat:server>

2) Modify schema.xml file

[root@mycat mycat]# cat conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="test_db" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        <table name="customer" dataNode="dn2"/>   <!--Designated here customer stay dn2 Node-->
    </schema>
    <dataNode name="dn1" dataHost="host1" database="test" />
    <dataNode name="dn2" dataHost="host2" database="test" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                   password="123.com">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
        </writeHost>
    </dataHost>
    <!--Add below host2 Host field. The specified host is mysql03-->
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM2" url="192.168.20.21:3306" user="root"
                   password="123.com">
        </writeHost>
    </dataHost>
</mycat:schema>

3) Restart mycat

[root@mycat mycat]# mycat restart

Note: the real database specified by the backend must have a test database, because the dataNode field in the schema.xml file above specifies the test database connected to the backend.

4) Test the effect of sub base

#Log in to mycat
[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066
#Create the corresponding table
mysql> use test_db;
mysql> CREATE TABLE customer(
    -> id_a INT AUTO_INCREMENT,
    -> NAME VARCHAR(200),
    -> PRIMARY KEY(id_a)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE orders(
    -> id_b INT AUTO_INCREMENT,
    -> order_type INT,
    -> customer_id INT,
    -> amount DECIMAL(10,2),
    -> PRIMARY KEY(id_b)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE orders_detail(
    -> id_c INT AUTO_INCREMENT,
    -> detail VARCHAR(2000),
    -> order_id INT,
    -> PRIMARY KEY(id_c)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE dict_order_type(
    -> id_d INT AUTO_INCREMENT,
    -> order_type VARCHAR(200),
    -> PRIMARY KEY(id_d)
    -> );
Query OK, 0 rows affected (0.01 sec)

#Log in to the back-end database to see if the table is split according to the expected effect
[root@mycat mycat]# mysql -uroot -p123.com -h 192.168.20.2
mysql> use test;

mysql> show tables;             #You can see that there are three tables in mysql01
+-----------------+
| Tables_in_test  |
+-----------------+
| dict_order_type |
| orders          |
| orders_detail   |
+-----------------+
3 rows in set (0.00 sec)

#Log in to mysql03 to view
[root@mycat mycat]# mysql -uroot -p123.com -h 192.168.20.21
mysql> use test;

mysql> show tables;     #You can see that there is only the customer table here
+----------------+
| Tables_in_test |
+----------------+
| customer       |
+----------------+
1 row in set (0.00 sec)

So far, the database has been divided and multiple tables have been placed in different libraries (in the chestnut above, multiple tables have been placed on different hosts), but for customers logging in through mycat, it is still a library, with four tables in the library.

3. mycat implementation table

Here, a new table emp is split.

1) The schema.xml file is modified as follows (full configuration file)

[root@mycat mycat]# cat conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="test_db" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        <table name="customer" dataNode="dn2"/>
        <!--Just add the following line to the original configuration file to indicate that emp The table is divided. dn1 and dn2 On these two nodes-->
        <table name="emp" dataNode="dn1,dn2" rule="mod_rule"/>
    </schema>
    <dataNode name="dn1" dataHost="host1" database="test" />
    <dataNode name="dn2" dataHost="host2" database="test" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                   password="123.com">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM2" url="192.168.20.21:3306" user="root"
                   password="123.com">
        </writeHost>
    </dataHost>
</mycat:schema>

2) The rule.xml file adds the following

[root@mycat mycat]# vim conf/rule.xml      #Modify the rule.xml file
        <tableRule name="mod_rule">    <!--Specified here name Must and schema.xml Specified in name Agreement-->
                <rule>
                        <columns>id</columns>    <!--Where are the columns specified for table splitting-->
                        <algorithm>mod_long</algorithm>  <!--The partition method is specified here-->
                </rule>
        </tableRule>
                        ...................
                                                <!--Underneath name It must be consistent with the segmentation method above-->
        <function name="mod_long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>    <!--What's specified here is node Number of nodes-->
        </function>

3) Restart mycat and log in to mycat

[root@mycat mycat]# mycat restart

4) Log in to mycat to insert data to test the effect of table splitting

[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066
mysql> use test_db;
#Create emp table (this table will exist on both backend database nodes)
mysql> create table emp (
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.41 sec)
#Insert multiple pieces of data (for such a sub table, you need to specify the column name to insert data, otherwise an error will be reported)
mysql> insert into emp(id,name) values(1,'Zhang San'),(2,'Li Si'),(3,'Wang Wu'),(4,'Zhao Liu');
#Query newly inserted data
mysql> select * from emp;    
+------+--------+
| id   | name   |
+------+--------+
|    2 | Li Si   |
|    4 | Zhao Liu   |
|    1 | Zhang San   |
|    3 | Wang Wu   |
+------+--------+
4 rows in set (0.04 sec)
mysql> select * from emp order by id;    #Sort results
+------+--------+
| id   | name   |
+------+--------+
|    1 | Zhang San   |
|    2 | Li Si   |
|    3 | Wang Wu   |
|    4 | Zhao Liu   |
+------+--------+
4 rows in set (0.01 sec)

#The data of the table queried by the backend mysql01 host is as follows:
mysql> select * from emp;
+------+--------+
| id   | name   |
+------+--------+
|    2 | Li Si   |
|    4 | Zhao Liu   |
+------+--------+
2 rows in set (0.00 sec)
#The data queried by mysql03 is as follows:
mysql> select * from emp;          
+------+--------+
| id   | name   |
+------+--------+
|    1 | Zhang San   |
|    3 | Wang Wu   |
+------+--------+
2 rows in set (0.00 sec)

It can be seen that the data is stored on different nodes evenly.

However, after fragmentation, ordinary tables cannot join directly with other tables.

To solve the problem of multi table join, we need to modify the schema.xml configuration file, add the child table field under the table field of the sub table, and how to implement it is still under research. However, you can set the type of sub table to global table, which is a relatively simple way to implement.

3. Global table

After splitting tables, how to join with other tables becomes a more difficult problem. We can choose to set the splitting table as a global table, which is a way to solve the join problem (but not the only way), but the global table has some limitations, as follows:

  • The global table changes infrequently;
  • The total amount of data has little change;
  • The data scale is not large, and there are few records over hundreds of thousands.

Global tables have the following properties:

  • The insertion and update of global tables will be performed on all nodes in real time to keep the data consistency of each partition;
  • The query operation of the global table is obtained from only one node;
  • The global table can JOIN with any table.

To create a global table, just add type="global" to the table field. As follows:

1) Define global table

[root@mycat conf]# cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="test_db" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        <table name="customer" dataNode="dn2"/>
        <!--Underneath table Field increment global Attribute can-->
        <table name="emp" dataNode="dn1,dn2" type="global">
        </table>
    </schema>
    <dataNode name="dn1" dataHost="host1" database="test" />
    <dataNode name="dn2" dataHost="host2" database="test" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                   password="123.com">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM2" url="192.168.20.21:3306" user="root"
                   password="123.com">
        </writeHost>
    </dataHost>
</mycat:schema>

2) Validate global table

mysql> drop table emp;    #emp table created before needs to be deleted
Query OK, 0 rows affected (0.03 sec)
#Recreate emp table
mysql> create table emp(
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.38 sec)
#Insert data test
mysql>  insert into emp(id,name) values(1,'Zhang San'),(2,'Li Si'),(3,'Wang Wu'),(4,'Zhao Liu');
#Query the inserted data on mycat as follows:
mysql> select * from emp;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Zhang San   |
|    2 | Li Si   |
|    3 | Wang Wu   |
|    4 | Zhao Liu   |
+------+--------+
4 rows in set (0.02 sec)
#View the data in the emp table on the backend host:
#The data on mysql01 is as follows:
mysql> select * from emp;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Zhang San   |
|    2 | Li Si   |
|    3 | Wang Wu   |
|    4 | Zhao Liu   |
+------+--------+
4 rows in set (0.00 sec)
#The data on mysql03 is as follows:
mysql> select * from emp;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Zhang San   |
|    2 | Li Si   |
|    3 | Wang Wu   |
|    4 | Zhao Liu   |
+------+--------+
4 rows in set (0.00 sec)

Tags: MySQL mycat Database xml

Posted on Tue, 11 Feb 2020 08:59:47 -0500 by mrsocks