Mycat installation and application example (Docker)

1. Pull image
docker pull longhronshens/mycat-docker
docker pull fify/mycat

2. Create mount directory
mkdir -p /D/docker/mycat

Modify and copy the server.xml and schema.xml in the attachment to the / home/mycat directory;

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <!-- 1 To enable real-time statistics, 0 to disable -->
        <property name="useSqlStat">0</property>  
        <!-- 1 To enable all overtime consistency detection, and 0 to close -->
        <property name="useGlobleTableCheck">0</property>  
        <property name="sequnceHandlerType">2</property>       
        <!--Default to type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
        <property name="processorBufferPoolType">0</property>   
        <!--Distributed transaction switch: 0 means not filtering distributed transactions, 1 means filtering distributed transactions (if only global tables are involved in distributed transactions, then not filtering), and 2 means not filtering distributed transactions,However, distributed transaction logs are recorded-->
        <property name="handleDistributedTransactions">0</property>
        <!-- off heap for merge/order/group/limit   1 On 0 off -->
        <property name="useOffHeapForMerge">1</property>
        <!-- Unit is m -->
        <property name="memoryPageSize">1m</property>
        <!-- Unit is k -->
        <property name="spillsFileBufferSize">1k</property>
        <property name="useStreamOutput">0</property>
        <!-- Unit is m -->
        <property name="systemReserveMemorySize">384m</property>

    <user name="root">
        <property name="password">123456</property>
        <property name="schemas">test</property>



<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema>
    <!-- dataNode -->
    <dataNode name="dn1" dataHost="dh1" database="test" />
    <dataHost name="dh1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <!-- Heartbeat statement -->
        <heartbeat>select user()</heartbeat>
        <!-- Write instance -->
        <writeHost host="hostM1" url="" user="root" password="P@SSMYqwert">
            <!-- Read instance -->
            <readHost host="hostS1" url="" user="root" password="P@SSYFBqwert" />


3. Create container
docker run --name mycat --net=host -v /D/docker/mycat/server.xml:/usr/local/mycat/conf/server.xml -v /D/docker/mycat/schema.xml:/usr/local/mycat/conf/schema.xml --privileged=true -d fify/mycat

docker run --name mycat -d -p 8066:8066 -v /D/docker/mycat/server.xml:/usr/local/mycat/conf/server.xml -v /D/docker/mycat/schema.xml:/usr/local/mycat/conf/schema.xml --privileged=true -d fify/mycat

docker run --name mycat -d -p 8066:8066 --privileged=true -d fify/mycat

– net=host function:
After adding - net=host, you don't need to do port mapping, and you can directly access the service port

mycat listens on two ports:
8066 and 9066, respectively; The default data port of mycat service is 8066, while port 9066 is the mycat management port, which is used to manage the whole cluster state of mycat. The listening port can be modified in the server.xml configuration file.

Copy schema.xml server.xml ehcache.xml rule.xml to the host
docker cp mycat:/usr/local/mycat/conf/schema.xml d:/docker/mycat
docker cp mycat:/usr/local/mycat/conf/server.xml d:/docker/mycat
docker cp mycat:/usr/local/mycat/conf/ehcache.xml d:/docker/mycat
docker cp mycat:/usr/local/mycat/conf/rule.xml d:/docker/mycat

4. Profile resolution
Common tags in server.xml file

  1. user tag
    <user name="root">
        <property name="password">123456</property>
        <property name="schemas">test</property>

Name: attribute indicates the user name,
property: the tag is used to set the login password and the sub library script to be used
2. system label
Indicates that all configurations are system related.
3. Filrewarl label
Firewall settings label
schema.xml configuration file
The schema.xml configuration file is mainly used to configure our logical library and physical library, that is, our allocation library script.

  1. schema tag
    The schema tag represents our logic library. It has three properties
    <schema name="test" checkSQLschema="false" sqlMaxLimit="100">
        <table name="user"   primaryKey="id"  autoIncrement="true"  dataNode="dn1" rule="userrule" />

Name: logical library name.
checksqlSchema: check whether the sql statement has a table name. This is a strict sql statement standard proposed by MySQL 5.7. It is recommended to set false.
sqlMaxLimit: check whether the query statement contains limit. If not, limit X will be added automatically, and X is the set value.

  1. table label
    The table tag represents the logical table of mycat. Its attributes are:

Name: logical table name
dataNode: data node
Rule: fragmentation rule
primaryKey: primary key

  1. dataNode tag
    The dataNode tag configures our specific data node, which represents a specific physical host and a specific database
 <dataNode name="dn1" dataHost="localhost1" database="db1" />

Its properties are:
Name: node name
datahost: node host
Database: node database (actual physical library)
2. datahost
datahost is configured as our node host. In addition to being the script for configuring the logical library, it can also configure the master-slave replication of mysql

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="" user="root" password="root">
            <readHost host="hostS2" url="" user="root" password="root" />

Name: host name
maxCon: maximum number of host connections
minCon: minimum number of host connections
balance: read / write separation switch, which is a configuration item when copying as master / slave
writeType: load balancing policy. If the value is 0, it will be sent to the first primary server first; When the value is 1, the write operation is performed randomly on multiple write servers.
dbtype: database type, such as mysql
dbdriver: drive mode. If it is a mysql database, use native
switchType: when the host is unexpectedly down, switch to a slave (high availability)
slaveThreshold: time delay, which is the configuration of master-slave replication. If the time difference between the data received by the slave and the current time is too large, the data will not be received, and the deviation value is the value we set
3. wirteHost label
The writeHost tag is our physical library. Its properties include:

url: database connection address
user: database account
Password: password

5. Example
(1) Sub Library
schema.xml configuration table tb_test rule = "sharding by murmur" use random splitting

Create MYCAT in, and databases respectively_ DB table
Table tb in Mycat's logical database_ New data on test:

Sub database (physical database) results:

6. Configuration parameter description
schema: it is the configuration of logical libraries. Multiple schemas represent multiple logical libraries (that is, the database you want to maintain)

    table : Is the configuration of the logical table (the data table you want to maintain)
  1. Name: represents the table name
  2. primaryKey: primary key
  3. autoIncrement: enable automatic growth. Do not add if automatic growth is not used
  4. dataNode: represents the partition corresponding to a table, that is, a table is mapped to different libraries, so as to realize the partition of tables and libraries.
  5. Rule: represents the rule of partitioning, how to distribute tables to different libraries, and the name corresponds to the name attribute of the tableRule tag in rule.xml.

dataNode: it is the partition corresponding to the logical library. If multiple partitions are configured, only multiple datanodes are required

  1. Name: the name of the datanode
  2. dataHost: is the actual physical library configuration address. Used to map to mysql physical database.
  3. Database: map the actual physical database, that is, the added data in mycat will be stored in the configured mysql database. You need to create these databases before starting.

dataHost: configure physical library slice mapping
A balance: load balancing type of read

  1. balance = "0": the read-write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost
  2. balance = "1": all readhosts and stand by writeHost participate in the load balancing of the select statement. In short, in the dual master and dual slave mode (M1 - > S1, M2 - > S2, and M1 and M2 are both active and standby), under normal circumstances, M2, S1 and S2 participate in the load balancing of the select statement
  3. balance = "2": all read operations are randomly distributed on writeHost and readhost
  4. balance = "3": all read requests are randomly distributed to the readhost corresponding to the wiriterHost for execution. The writerHost does not bear the reading pressure. Note that it is only available in version 1.4 and later
    B writeType: load balancing type to write
  5. writeType = "0": all write operations are sent to the first writeHost of the configuration. The first one is suspended and the second one is still alive. The switchover after restart shall prevail. The switchover is recorded in the configuration file:
  6. writeType = "1": all write operations are randomly sent to the configured writehost. It is not recommended to discard after 1.5
    C switchType: switching mode
  7. switchType="-1": indicates no automatic switching
  8. switchType = "1": default value, indicating automatic switching
  9. switchType = "2": decide whether to switch based on the status of MySQL master-slave synchronization. The heartbeat statement is show slave status;
  10. switchType = "3": the heartbeat statement of the MySQL galaxy cluster based switching mechanism (suitable for the cluster) is show status like 'wsrep%';
    D dbDriver specifies the driver to be used after connecting to the database. Currently, the optional values are native and JDBC. If native is used, because this value executes the binary mysql protocol, mysql and maridb can be used. Other types need to be supported by jdbc driver.
    E writeHost: the mysql master server corresponding to the logical host (dataHost)
    F readhost: MySQL slave server


	<tableRule name="sharding-by-murmur">
	<function name="murmur"
		<property name="seed">3</property><!-- The default is 0 -->
		<property name="count">4</property><!-- The number of database nodes to be partitioned must be specified, otherwise it cannot be partitioned -->
		<property name="virtualBucketTimes">160</property>

  1. Name: the name set by rule in schema.xml corresponds to the name in tableRule. It must be unique.
  2. columns: partition according to the fields of the database.
  3. Algorithm: partition algorithm name, corresponding to the following function, must be unique.
  4. function: slicing algorithm.
    name = "count": the number of database nodes to be partitioned must be specified, otherwise it cannot be partitioned, and it needs to be consistent with the number of databases.

Keywords: mycat

Added by bdata28 on Sat, 04 Sep 2021 07:48:49 +0300