I. Introduction to Mycat

Mycat is an open source database middleware, which can solve most problems in distributed database environment, such as read/write separation, library and table, etc. In addition, it also has the following features:

  • Supports SQL syntax common to MySQL, Oracle, DB2, SQL Server, and PostgreSQL databases.
  • Automatic failover based on heartbeat, read/write separation, MySQL master-slave architecture, and Galera Cluster.
  • Support multi-slice automatic routing and aggregation of data, support sum, count, Max and other commonly used aggregation functions, support cross-library paging.
  • Supports arbitrary join within a single library, supports cross-library two-table join, and supports multi-table join based on Caltlet.
  • Multi-table join query can be realized by sharding strategies such as global table and ER relational table.
  • Support for XA distributed transactions (1.6.5 +).
  • Support global serial number, can solve the problem of primary key generation in distributed environment.
  • Zookeeper is used to coordinate primary/secondary switchover, centrally manage configuration data, and generate globally unique ids. (+ 1.6)
  • Provides a Web monitoring interface to monitor the status of Mycat, MySQL and the server.

Second, Mycat core concept

After the introduction of Mycat, all client requests need to be forwarded through the middleware. At this time, the client directly faces the logical library or logical table on Mycat:

Logic library

Defined in the Mycat configuration file, it corresponds to one or more actual databases or database clusters.

Logical table

It can correspond to an actual table, or it can be represented as a collection of multiple sharded tables. According to its characteristics, it can be divided into the following four categories:

  • Sharded table: Tables are split by specified keys and scattered across multiple database instances to solve the problem of large single tables.
  • Non-sharded tables: In general, only large tables need to be sharded. Small tables do not need to be sharded, so they are also called non-sharded tables in Mycat, mainly to distinguish them conceptually from sharded tables.
  • ER table: A table sharded based on the entity relationship model. For example, order tables and order details are usually large tables. In this case, ER table can be sharded according to the order number to ensure that the order records and details of the same order number are in the same shard, thus avoiding cross-shard query.
  • Global tables: Also used to avoid cross-shard queries. For example, when querying the order details, it is necessary to query the classification of products (such as household appliances and daily necessities). The product category table is usually a small table. In this case, it can be declared as a global table, and Mycat will copy it to all shards to avoid cross-shard query.

Shard node

After the table is sharded according to the shard key, all data in a table is distributed to different databases. These database nodes are called shard nodes.

Three, Mycat installation

4. Mycat basic configuration

In the conf directory of Mycat’s installation directory, there are the following three core configuration files:

  • Server.xml: mainly used to define user information, global SQL firewall, and configurations related to Mycat itself.
  • Schema. XML: used to define information about logical libraries, logical tables, logical data nodes, and actual physical databases.
  • Rule-xml: used to define sharding rules and provide them to the sharding table in the schema. XML tag.

4.1 server. XML

An example configuration of server.xml is as follows, consisting mainly of the following tags:


  • : defines Mycat related configurations.

  • : configures a global SQL firewall under which you can define an IP address whitelist and blacklist using sub-labels.

  • : Used to set default or normal accounts and assign permissions to them on logical libraries or logical tables. Permissions are specified by DML attributes. For example, DML =”0110″. Values in the four positions correspond to insert, UPDATE, SELECT, and delete permissions respectively.
<system> 
    <! -- 0 indicates password login, 1 indicates no password login, default is 0, set to 1, need to specify the default account -->
    <property name="nonePasswordLogin">0</property> 
    <property name="charset">utf8</property>
</system>

<firewall>
    <whitehost>
        <host host="1 * 7.0.0. *" user="root"/>
    </whitehost>
    <blacklist check="false">
    </blacklist>
</firewall>

<! -- Define default account -->
<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">TESTDB</property>
    <! -- Permission Settings -->
    <privileges check="true">
        <schema name="TESTDB" dml="0110" >
            <table name="tb01" dml="0000"></table>
            <table name="tb02" dml="1111"></table>
        </schema>
    </privileges>
</user>

<! -- Define default account -->
<user name="user">
    <property name="password">123456</property>
    <property name="schemas">TESTDB</property>
    <property name="readOnly">true</property>
</user>
Copy the code

In the example above, the user configured a plaintext password, which may cause security risks, so Mycat also supports password encryption, as shown in the following example:

#The jar package is in the lib directory of the Mycat installation directoryShell > Java - cp Mycat - server - 1.6.7.1 - the jar IO. Mycat. Util. DecryptUtil 0: root: 123456 GO0bnFVWrAuFgr1JMuMZkvfDNyTpoiGU7n/Wlsa151CirHQnANVk3NzE3FErx8v6pAcO0ctX3xFecmSr+976QA==Copy the code

You can replace the plaintext password with the encryption password. In this case, you need to add the following configuration under the user TAB of the corresponding user to enable the encryption function:

<property name="usingDecrypt">1</property> 
Copy the code

4.2 schema. The XML

Here is an example schema.xml configuration file that contains the following tags:

  • <schema>CheckSQLschema specifies whether the schema is checked, if true, if sentselect * from db.table1The system automatically changes the value toselect * from table1. SqlMaxLimit is used to limit the number of rows of returned data.
  • : defines logical tables. In this paper, the sharding node and sharding rule of order table are defined by taking sharding table as an example.

  • : Defines logical data nodes. Each node in the following example configuration corresponds to a database instance on a master/slave replication cluster.

  • : the actual single physical database or database cluster, which corresponds to a Maser-slave cluster in the following example configuration.
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
    <! -- mod-long Sharding rules are defined in rule-xml below -->
    <table name="order" dataNode="dn1,dn2,dn3" rule="mod-long" />
</schema>

<dataNode name="dn1" dataHost="cluster01" database="order_db" />
<dataNode name="dn2" dataHost="cluster02" database="order_db" />
<dataNode name="dn3" dataHost="cluster03" database="order_db" />

<dataHost name="cluster01" maxCon="1000" minCon="10" balance="0"
          dbType="mysql" dbDriver="native" switchType="1">
    <! -- Heartbeat check -->
    <heartbeat>select user()</heartbeat>
    <writeHost host="master" url="192.168.0.226:3306" user="root"  password="123456">
        <readHost host="salve" url="192.168.0.227:3306" user="root" password="123456" />
    </writeHost>
</dataHost>. Omit Cluster02, Cluster03Copy the code

MaxCon indicates the maximum number of connections, minCon indicates the minimum number of connections, dbType indicates the type of database (e.g. Mysql, Oracle, etc.), and other attributes have multiple optional values as follows:

  • DbDriver: Database type. The value can be native or JDBC. If you use mysql, Maridb, or PostgresQL, you can use native. For other databases, copy the driver package to the lib directory of the Mycat installation directory and write the complete driver class name.

  • SwitchType:

    • The default value is 1, indicating automatic switchover.
    • If the value is set to 2, the switchover is determined based on the primary/secondary synchronization status of MySQL.
    • If the value is set to 3, it represents the switchover mechanism based on MySQL Galary Cluster.
    • If your cluster has automatic switching based on high availability architectures such as MMM or MHA, you can set this value to -1 to indicate no switching.

  • The balance:

    • balance="0": The read/write separation mechanism is disabled and all read requests are sent to the currently available writeHost.
    • balance="1": All readhosts and stand by writeHost participate in the read operation. Stand by writeHost usually refers to the master node in the Stand state in the dual master replication, that is, assume that the cluster replication architecture is Master1 -> Slave1, Master2 -> Slave2, and M1 and M2 are in active/standby mode), Master2, Slave1, and Slave2 all participate in the read load.
    • balance="2": All read requests are distributed randomly on writeHost and readhost.
    • balance="3": All read requests are randomly distributed to readhost corresponding to writeHost. WriterHost does not bear the read pressure.

Rule 4.3. The XML

The rule-xml file defines sharding rules and contains the following tags:

  • : Defines the name of the sharding rule, sharding key, and sharding algorithm. The following sharding rule indicates that the order Id is sharded according to the modulus extraction algorithm.

  • : defines the implementation class of the sharding algorithm and the required parameters.
<tableRule name="mod-long">
    <rule>
        <columns>order_id</columns>
        <algorithm>mod-long</algorithm>
    </rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <! -- Number of nodes -->
    <property name="count">3</property>
</function>
Copy the code

Mycat supports more than a dozen sharding algorithms, such as modular sharding, enumeration sharding, range sharding, string hash sharding, consistent Hash sharding, date sharding, etc. A detailed description of these sharding algorithms can be found in the official documentation: Mycat Official Guide

5, Mycat read and write separation

Mycat read/write separation configuration is very simple, just need to configure balance, writeHost and readHost, as shown in the following example:

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
          writeType="0" dbType="mysql" dbDriver="native">
    <heartbeat>select user()</heartbeat>
    <! WriteHost -->
    <writeHost host="Master" url="hostname1:3306" user="root"
               password="123456">
        <! ReadHost -->
        <readHost host="Slave" url="hostname2:3306" user="root"
                  password="123456" />
    </writeHost>
</dataHost>
Copy the code

However, it should be noted that the above configuration still has a single point problem, because there is only one writeHost, Mycat supports multiple writehosts, as shown in the following example:

<writeHost host="Master" url="hostname1:3306" user="root"
           password="123456">
    <! ReadHost -->
    <readHost host="Slave1" url="hostname2:3306" user="root"
              password="123456" />
    <readHost host="Slave2" url="hostname3:3306" user="root"
              password="123456" />
</writeHost>
<writeHost host="Slave3" url="hostname4:3306" user="root" password="123456" />
Copy the code

The above configuration is given in the official guide of Mycat, that is, in the replication architecture of one Master and three slaves, one Slave can be selected as the standby write node. In this case, when the Master node is down, the write operation will continue on the standby node. There are two problems with this configuration and architecture:

  • First, Mycat does not allow Slave 1 and Slave 2 to automatically change their replication master to Slave 3. You still need to do this yourself.
  • In the above configuration, we set Slave 3 as the standby node. However, after the master node goes down, maybe the replication offsets of both Slave 1 and Slave 2 will be larger than Slave 3. Obviously, they are more suitable to be the new master node.

For the above two reasons, it is not recommended to configure multiple WriteHosts to achieve high availability. Instead, configure one writeHost that points to a virtual read IP address. In this case, the replication architecture is implemented by MMM or MHA, which provides read IP addresses for VMS.

Vi. Mycat database table

To sum up all the above, here is an example of sub-library sub-table, its architecture is as follows:

As shown in the figure above, what is simulated here is an e-commerce database, and the operation of database and table is performed on it:

  • Split user related table, order related table and commodity related table into separate database;
  • Split the order table and the order list horizontally into different databases. At the same time, to avoid cross-shard query when the order table is associated with the order list, it needs to be configured as an ER table.
  • Because the address table is used to query user information (such as home address), order information (shipping address), and product information (product origin), it is declared as a global table and exists on all of the above shards to avoid cross-shard queries.

To save space, all the above test table and test library establishment statements are sorted into: ec_shop.sql. The specific operations of database and table are as follows:

6.1 server. XML

Create a Mycat user and define the logical database it manages as ec_shop, and use fakeMySQLVersion to define the version of the MySQL database you want to emulate. The rest of the configuration in Mycat’s server.xml can be modified without special requirements:

<system>
    <property name="fakeMySQLVersion">5.7.20</property>
</system>

<user name="heibaiying">
    <property name="password">
        B+BlA/U17pjyzHslglpDgYUxpgqK8qC62IRt/S74RBW6R7dZFJAXVb5tJDgmhzM4fj14MMhLnNmvKko6D73+iA==
    </property>
    <property name="schemas">ec_shop</property>
    <property name="usingDecrypt">1</property>
</user>
Copy the code

6.2 schema. The XML

ChildTable is used here to define order tables and order details as ER tables to avoid cross-shard queries. Address table area_info is declared global with type=”global”, also to avoid cross-shard query:

<?xml version="1.0"? >

      
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="ec_shop" checkSQLschema="false" sqlMaxLimit="100">
        <table name="customers" primaryKey="customer_id" dataNode="dn01"/>
        <table name="products" primaryKey="product_id" dataNode="dn02"/>
        <table name="orders" primaryKey="order_id" dataNode="dn03,dn04" rule="mod-long">
            <childTable name="order_detail" primaryKey="order_detail_id" joinKey="order_id"
                        parentKey="order_id"/>
        </table>
        <table name="area_info" primaryKey="area_id" type="global" dataNode="dn01,dn02,dn03,dn04"/>
    </schema>


    <dataNode name="dn01" dataHost="host01" database="ec_shop_customer"/>
    <dataNode name="dn02" dataHost="host02" database="ec_shop_product"/>
    <dataNode name="dn03" dataHost="host03" database="ec_shop_order"/>
    <dataNode name="dn04" dataHost="host04" database="ec_shop_order"/>

    <dataHost name="host01" maxCon="1000" minCon="10" balance="0"
              dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="192.168.0.226:3306" user="root" password="123456"/>
    </dataHost>

    <dataHost name="host02" maxCon="1000" minCon="10" balance="0"
              dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="192.168.0.227:3306" user="root" password="123456"/>
    </dataHost>

    <dataHost name="host03" maxCon="1000" minCon="10" balance="0"
              dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="192.168.0.228:3306" user="root" password="123456"/>
    </dataHost>

    <dataHost name="host04" maxCon="1000" minCon="10" balance="0"
              dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="192.168.0.229:3306" user="root" password="123456"/>
    </dataHost>

</mycat:schema>
Copy the code

Rule 6.3. The XML

Define the sharding rules used by the order table, here using the modular algorithm as an example:

<?xml version="1.0" encoding="UTF-8"? >

      
<mycat:rule xmlns:mycat="http://io.mycat/">
    <tableRule name="mod-long">
        <rule>
            <columns>order_id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <! -- Number of nodes -->
        <property name="count">2</property>
    </function>
</mycat:rule>
Copy the code

Mycat and MySQL 8.0

Here I am using MySQL 8.0.17 as the back-end database. There are a few more considerations than using MySQL 5.6 or 5.7 to integrate Mycat, mainly as follows:

7.1 Incorrect Password

Even if you’re on the server. The XML configuration of the user name and password correctly, but in the use of mysql connect Mycat shell, or sell password error exception:

Access denied for user 'xxx', because password is error
Copy the code

This is due to the fact that caching_sha2_password has been used as the authentication plug-in since MySQL 8.0.4. The previous version was mysql_native_password. Mycat version I used in my tests was 1.6.7. Caching_sha2_password is not supported. Therefore, you need to specify the use of the original authentication plug-in with –default_auth at login time:

#The default connection port number for Mycat in 1.6.7 is 8066Mysql -uheibaiying -p -h127.0.0.1 -p8066 --default_auth=mysql_native_passwordCopy the code

7.2 Database Connection Fails

Mycat and MySQL are started normally, but failed to execute SQL statement on Mycat. At this point, you can view the Mycat logs file mycat.log under the Mycat logs directory. The following exception usually occurs:

(io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91) - can't connect to  mysql server ,errmsg:Client does not support authentication protocol requested by server; consider upgrading MySQL client MySQLConnectionCopy the code

This is the same problem as above, because of the authentication plug-in. In this case, you need to modify the authentication plug-in used by the account:

ALTER USER 'root'@The '%' IDENTIFIED WITH mysql_native_password BY 'xxxx';
FLUSH PRIVILEGES;
Copy the code

You can run the following command to view the modification:

mysql> SELECT Host,User,plugin FROM mysql.user; +---------------+------------------+-----------------------+ | Host | User | plugin | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | % root | mysql_native_password | | 192.168.200. % | repl  | mysql_native_password | | localhost | mysql.infoschema | caching_sha2_password | | localhost | mysql.session | caching_sha2_password | | localhost | mysql.sys | caching_sha2_password | | localhost | root | mysql_native_password | +---------------+------------------+-----------------------+Copy the code

Then restart the Mycat service and the connection will be normal.

The resources

Mycat official Guide: www.mycat.io/document/my…

For more articles, please visit the full stack Engineer manual at GitHub.Github.com/heibaiying/…