mycat Read-Write Separation+Base Subtable+Global Table

1. mycat Read-Write Separation
Mycat's read-write separation is based on the master-slave replication of Mysq

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

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                             /> -->
        <dataNode name="dn1" dataHost="host1" database="test1" />
        <dataHost name="host1" 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="hostM1" url="192.168.171.135:3306" user="root"
                                   password="123">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.171.146:3306" user="root" password="123" />
                </writeHost>
        </dataHost>
</mycat:schema>

There are two settings, as follows:
(1) Set balance="1" and writeType="0"

Balance parameter settings:
The modified balance attribute, which configures read-write separated type load balancing types, currently has four values:
(1) balance="0", no read-write separation mechanism is enabled, all read operations are sent to the currently available writeHost.
(2) balance="1". All readHost s and stand by writeHost participate in load balancing of the select statement. Simply put, when the dual-master-dual-slave mode (M1->S1, M2->S2, and M1 and M2 are mutually predominant), under normal circumstances, M2,S1,S2 all participate in load balancing of the select statement.
(3) balance="2", all reading operations are randomly distributed on writeHost, readhost.
(4) balance="3". All read requests are randomly distributed to readhost for execution. WterHost does not bear reading pressure

.

WriteType parameter settings:

  1. WteType="0", all writes are sent to the available writeHost.
  2. WteType="1", all writes are randomly sent to readHost.
  3. WteType="2", all writes are sent randomly on the writeHost, readhost scores.

"readHost belongs to writeHost, which means it gets synchronized data from that writeHost, so when its writeHost goes down, it no longer participates in the read-write separation, i.e."Not working", because its data is now"unreliable".For this reason, in the current versions of mycat 1.3 and 1.4, if you want to support the standard MySQL primary-slave configuration and read data from the primary node when it is down, you need to configure two writeHosts and set banlance=1 in Mycat."

(2) Set switchType="2" and slaveThreshold="100"

There are currently three options for switchType:
-1: does not switch automatically
1: Default, auto-switch
2: Decide whether to switch based on the status of MySQL master-slave synchronization

"The Mycat heartbeat check statement is configured to show slave status, and two new properties are defined on the dataHost: switchType="2"and slaveThreshold="100", which means opening the read-write separation and switching mechanism for MySQL master-slave replication state binding.Mycat heartbeat mechanism detects "Seconds_in show slave status"Behind_Master "," "Slave_IO_Running "," "Slave_SQL_Running "Three fields to determine the current master-slave synchronization status and Seconds_Behind_Master master-slave replication latency."
2. Vertical Split--Branch
A database consists of many tables, each corresponding to a different business. Vertical slicing refers to the principle of classifying tables according to business and distributing them to different databases, which also shares data or pressure among different libraries. How to divide tables into libraries is closely related.Tables should be in one library, and tables that are not related to each other can be divided into different libraries.

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

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
         <table name="customer" dataNode="dn2"></table>
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                             /> -->
        <dataNode name="dn1" dataHost="host1" database="test1" />
        <dataNode name="dn2" dataHost="host2" database="test1" />
        <dataHost name="host1" 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="hostM1" url="192.168.171.135:3306" user="root"
                                   password="123">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.171.146:3306" user="root" password="123" />
                </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.171.131:3306" user="root" password="123">  
                </writeHost>
         </dataHost>
</mycat:schema>

#Log in to the database and create tables
[root@mycatconf]#mysql -umycat -p123456 -h192.168.171.134 -P8066
mysql>CREATE TABLE customer( id INT AUTO_INCREMENT, NAME VARCHAR(200), PRIMARY KEY(id) ); 
mysql>CREATE TABLE orders( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id) ); 
mysql>CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) ); 
mysql>CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) ); 
#How are the four tables divided?Customer tables are divided into one database, and the other three require associated queries, which are divided into another database. 
mysql> show tables;
+----+--------------+
|Tables_in_xxq  |
+----+--------------+
|  dict_order_type|
|  orders               |
|  orders_detail    |
|  tab1                    |
+----+---------------+
mysql>show tables;
+----+--------------+
|  customer          |
+----+---------------+

3. Horizontal Split--Split Table
In contrast to vertical splitting, horizontal splitting does not categorize tables, but spreads them across libraries according to some rule of a field, each containing a portion of the data.Simply put, we can think of horizontal slicing of data as slicing by rows of data, that is, splitting some rows of a table into one database and some other rows into other databases.There is a bottleneck in choosing the table to split to achieve the splitting table MySQL form to store the number of data bars.Affecting query efficiency requires horizontal splitting (table splitting) to optimize.Example: orders, orders_Details have reached 6 million rows of data and need to be optimized for tabular breakdowns.Tabulation Fields Take the order table as an example, which can be sorted according to different fields

#Modify ProfileSchema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
<mycat:schema xmlns:mycat="http://io.mycat/">        
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">           
<table name="customer" dataNode="dn2"></table>        
<table name="orders" dataNode="dn1,dn2" rule="mod_rule">      # Set the data nodes to dn1, dn2 for the orders table and specify the fragmentation rule to mod_rule (custom name)

                                    <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />                
                                    </table>        
                                    </schema>        
                                    <dataNode name="dn1" dataHost="host1" database="test1" />      
                                    <dataNode name="dn2" dataHost="host2" database="test1" />       
                                    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">             
                                    <heartbeat>select user()</heartbeat>             
                                    <writeHost host="hostM1" url="192.168.171.135:3306" user="root"                                    password="123">                
                                    <readHost host="hostS1" url="192.168.171.145:3306" user="root" password="123" />               
                                    </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>               
                                                    <writeHost host="hostM1" url="192.168.171.132:3306" user="root"                                    password="123">               
                                                    </writeHost> 

        </dataHost> 
                </mycat:schema> 
#Modify ProfileRule.xml
#Add slicing rule mod_to rule configuration fileRule, and specify that the applicable field of the rule is customer_id, 
#Another option is the slicing algorithm mod-long (modulo operations on fields), customer_id modifies two nodes and slices them according to the result 
#Configuration algorithm mod-long parameter count is 2, two nodes 

<tableRule name="mod_rule"> 
<rule>
<columns>customer_id</columns> 
<algorithm>mod-long</algorithm>
</rule> 
</tableRule> 
...
<function name="mod-long" class="io.mycat.route.function.PartitionByMod"> 
<!-- how many data nodes --> 
<property name="count">2</property> 
</function> 

Build orders table on data node dn2

mysql>CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) ); 

Restart Mycat for configuration to take effect

mysql> insert into orders_detail(id,detail,order_id) values (1,'detail',1); 

mysql> insert into orders_detail(id,detail,order_id) values (2,'detail',2); 

mysql> insert into orders_detail(id,detail,order_id) values (3,'detail',3); 

mysql> insert into orders_detail(id,detail,order_id) values (4,'detail',4); )

mysql> insert into orders_detail(id,detail,order_id) values (5,'detail',5); 

mysql> insert into orders_detail(id,detail,order_id) values (6,'detail',6); 

Accessing Mycat to implement fragmentation

mysql> select o.*,od.detail from orders as   o inner join orders_detail as   od on o.id=od.order_id; 
+----+------------+-------------+-----------+--------+ 
| id | order_type | customer_id | amount    | detail | 
+----+------------+-------------+-----------+--------+ 
|  1 |        101 |         100 | 100100.00 | detail | 
|  2 |        101 |         100 | 100300.00 | detail | 
|  6 |        102 |         100 | 100020.00 | detail | 
|  3 |        101 |         101 | 120000.00 | detail | 
|  4 |        101 |         101 | 103000.00 | detail | 
|  5 |        102 |         101 | 100400.00 | detail | 
+----+------------+-------------+-----------+--------+ 

IV. Global Tables

In the case of fragmentation, when a business table is fragmented due to size, the association between the business table and these affiliated dictionary tables becomes a tricky issue, considering that dictionary tables have the following characteristics:
(1) Change infrequently
(2) The total amount of data does not change much.
(3) Data is small in scale and few records exceed hundreds of thousands

.

Mycat defines a special table called a Global Table, which has the following characteristics:
1. Insertion and update of global tables will be performed on all nodes in real time, keeping data consistency of each slice
(2) Query operations of global tables, obtained from only one node
(3) Global tables can be defined as global tables by JOIN operation with any table. On the other hand, it solves the problem of data JOIN very well.Mycat can meet more than 80% of enterprise application development through global table + fragmentation based on E-R relationship

#modifySchema.xmlconfiguration file 
<table name="customer" dataNode="dn2"></table> 
                              <table name="orders" dataNode="dn1,dn2" rule="mod_rule" > 
                                          <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" /> 
                                                                                               </table> 
                                        <table name="dict_order_type" dataNode="dn1,dn2" type="global"> 
    </table> 
    #Create dict_in DN2Order_Type table 
    mysql>CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) );
    #Restart Mycat 
    #Visit Mycat to dict_order_type table insert data 
    mysql> insert into dict_order_type (id,order_type) values (101,'type1'); 

mysql> insert into dict_order_type (id,order_type) values (102,'type2'); 
 mysql> select * from dict_order_type ; 
 +-----+------------+ 
 | id  | order_type | 
 +-----+------------+ 
 | 101 | type1      | 
 | 102 | type2      | 
 +-----+------------+ 
 2 rows in set (0.05 sec) 

Common slicing rules
1. Modeling This rule is to seek for partitioned fields.It is also the most common rule for horizontal subtables.In the 5.1 configuration sub-table, this rule is used in the orders table.
2. Fragmentation enumeration configures fragmentation by configuring the possible enumeration IDS in the configuration file. This rule applies to specific scenarios, such as when some businesses need to be saved by provinces or counties, while provinces, counties and counties are fixed throughout the country. This rule is used for such businesses.

Tags: MySQL mycat Database xml

Posted on Sun, 21 Jun 2020 18:01:58 -0400 by fellixombc