MyCat – Database Middleware and Distributed Database Implementation (II)

You can run it directly by clicking startup_nowrap.bat

Core concepts of MyCat

Schema(Logical library)

Business developers often don’t need to know in the process of practical application of the existence of middleware, only need to pay attention to the database, so the database middleware can be regarded as one or more database clusters constitute the logic of the library, in the era of cloud computing, database middleware can be in the form of multi-tenancy provide services for one or more enterprises, Each application may access a separate or shared physical library

Table logical table

In contrast to a logical library, in a distributed database, the table that reads and writes data to the application is a logical table, which can be distributed in one or more shard libraries or not

Subdivision table

A table with a large amount of data is shard into multiple database instances, and all the shards are combined to form a complete table

The subdivision table

A table equivalent to a slice table that does not require data sharding

ER table

Relational database is based on entity relation model, and ER table in MyCat is derived from this. MyCat proposes the data sharding strategy of E-R relationship. The records of the child table and its associated parent table will exist in the same data sharding, that is, the child table depends on the parent table, and the data associated query will not operate across the library through table grouping

Global table

Assume that there are a large number of dictionary tables in a business scenario. The data of these dictionary tables changes infrequently and the scale is not large. When service tables are fragmented due to the scale problem, the associated fields of these tables can be solved through data redundancy (all the fragments copy the same data at the same time), these tables are called global tables

Shard node

Large tables are allocated to different shard databases, each of which has a shard node

The host node

After data is sharded, each shard node does not necessarily monopolize a machine. The same machine can have multiple shard databases. In this way, the machine occupied by one or more shard nodes is the node host

MyCat principle introduction

Intercept Sql statements, perform specific analysis on Sql statements (fragment analysis, route analysis, read/write separation analysis, cache analysis), and then send THE Sql to the real back-end database, and process the results appropriately, and finally return to the user

MyCat configuration details

• Schema.xml -> Manage MyCat important files, mainly manage logic library, shard library, shard node and shard host
• Server.xml -> system parameters configuration file, master Mysql optimization methods
• Sequence. XML -> global Sequence configuration file
• Log4j.xml -> log output configuration file

MyCat supports two configurations

ZooKeeper and local XML Mode By default, MyCat is started by locally loading XML. To configure ZK, set loadZk in myid.properties in the conf directory to true

# true loadZk=false # zkURL=127.0.0.1:2181 clusterId=mycat-cluster-1 myID =mycat_fz_01 clusterSize=3 clusterNodes=mycat_fz_01,mycat_fz_02,mycat_fz_04 #server booster ; booster install on db same server,will reset all minCon to 2 type=server boosterDataHosts=dataHost1Copy the code

Here are just a few examples:

Server.xml file

• The user tag, which defines the users and permissions for MyCat

<user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">TESTDB</property> <property name="defaultSchema">TESTDB</property> <! --No MyCAT Database selected schema will be used as schema. If not set, null will be used. -- 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>Copy the code

• The System label

Schema.xml

<! -- Define the logical library, there can be more than one logical library -->
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
		<! -- auto sharding by id (long) -->
		<! --splitTableNames enable the <table name attribute to use commas to separate multiple tables, i.e. multiple tables use this configuration -->
<! --fetchStoreNodeByJdbc Enable ER table to obtain DataNode in JDBC mode -->
		<table name="customer" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-intfile" autoIncrement="true" fetchStoreNodeByJdbc="true">
			<childTable name="customer_addr" primaryKey="id" joinKey="customer_id" parentKey="id"> </childTable>
		</table>

	</schema>
Copy the code

Sequence configuration file

• Local file mode
• Database mode
• Local timestamp

Example:

Modulus shard

<tableRule name="mod-long">
		<rule>
			<columns>id</columns>
			<algorithm>mod-long</algorithm>
		</rule>
	</tableRule>
    
Copy the code

Enumeration shard

<tableRule name="sharding-by-intfile">
		<rule>
			<columns>sharding_id</columns>
			<algorithm>hash-int</algorithm>
		</rule>
</tableRule>

<! -- -- -- > separately
	<function name="hash-int"
			  class="io.mycat.route.function.PartitionByFileMap">
		<property name="mapFile">partition-hash-int.txt</property>
	</function>
    
Copy the code

Partition -hash-int. TXT indicates the partition configuration

10000 = 0 10010 = 1Copy the code

The scope of fragmentation

<tableRule name="auto-sharding-long"> <rule> <columns>id</columns> <algorithm>rang-long</algorithm> </rule> </tableRule>  # -------------------------------- <function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong"> <property name="mapFile">autopartition-long.txt</property> </function>Copy the code

Range modulus algorithm

<tableRule name="auto-sharding-rang-mod">
		<rule>
			<columns>id</columns>
			<algorithm>rang-mod</algorithm>
		</rule>
</tableRule>

 <function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
		<property name="mapFile">partition-range-mod.txt</property>
	</function>
Copy the code

There are also different sharding algorithms, such as the fixed sharding Hash algorithm (Rule1), consistent hash algorithm, and so on

MyCat management command

MyCat has two default ports: data management port 8066 and management port 9066 login:

-h host -u server. XML configuration user -p management port number -p password mysql -uroot -p9066 -h127.0.0.1 -p123456Copy the code

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: Ini, add skip-grant-tables under [mysqld], skip the authorization table, and restart the service

Mysql > show @@help; # output is as follows:  +--------------------------------------------------------------+------------------------+ | STATEMENT | DESCRIPTION | +--------------------------------------------------------------+------------------------+ | show @@time.current | Report  current timestamp | .....Copy the code

Reload command

Mysql > reload @@config; Query OK, 1 row affected (0.14 SEC) Reload config successCopy the code

Reload @@sqlstat=open; Query OK, 1 row affected (0.00 SEC) Reset @@sqlstat successCopy the code

Mysql > reload @@sqlslow=10; Query OK, 1 row affected (0.00 SEC) Reset show @@sql. Slow time successCopy the code

Mysql > reload @@user_stat; Query OK, 1 row affected (0.00 SEC) Reset show @@sql @@sql.sum @@sql.slow successCopy the code

The show commands

Mysql > show @@database; # the results are as follows: + -- -- -- -- -- -- -- -- -- -- + | DATABASE | + -- -- -- -- -- -- -- -- -- -- + | TESTDB | + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Mysql > show @@datanode; DATHOST Data host ACTIVE Number of ACTIVE connections +------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+------------ ---+ | NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME | +------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+------------ ---+ | dn1 | localhost1/db1 | 0 | mysql | 0 | 0 | 1000 | 3 | 0 | 0 | 0 | -1 | | dn2 | localhost1/db2 | 0 | mysql | 0 | 3  | 1000 | 6 | 0 | 0 | 0 | -1 | | dn3 | localhost1/db3 | 0 | mysql | 0 | 5 | 1000 | 9 | 0 | 0 | 0 | -1 | +------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+------------ --+ 3 rows in set (0.00 SEC)Copy the code

Mysql > show @@heartbeat; # display:  +--------+-------+-----------+------+---------+-------+--------+---------+------ ------+---------------------+-------+ | NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECU _TIME | LAST_ACTIVE_TIME | STOP | +--------+-------+-----------+------+---------+-------+--------+---------+------ ------+---------------------+-------+ | HostM1 | mysql | localhost | 3306 | | 1 | 0 idle | | 0 2,2,2 17:05:33 | 2020-08-02 | | false + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 Row in set (0.01sec)Copy the code

Mysql > show @@version; # output is as follows:  +---------------------------------------------+ | VERSION | +---------------------------------------------+ | 5.6.29 mycat - 1.6.7.5 - release - 20200422133810 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC)Copy the code

MyCat has a show @@backend for MyCat connections. Mysql > show @@connection; # output is as follows:  +------------+------+-----------+------+------------+------+--------+----------+--------+---------+---------------+---- ---------+------------+---------+------------+ | PROCESSOR | ID | HOST | PORT | LOCAL_PORT | USER | SCHEMA | CHARSET | NET_IN | NET_OUT | ALIVE_TIME(S) | RECV_BUFFER | SEND_QUEUE | txlevel | autocommit | +------------+------+-----------+------+------------+------+--------+----------+--------+---------+---------------+----- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Processor2 | 2 | 127.0.0.1 | | 9066 | 65138 root | NULL | latin1:8 | 626 | 5821 | 1291 | 4096 | 0 | | | +------------+------+-----------+------+------------+------+--------+----------+--------+---------+---------------+----- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.35 SEC)Copy the code

Mysql > show @@cache; ER # ER_SQL2PARENTID: cache fragmentation in the child table's relationship with the parent table # TableID2DataNodeCache. TESTDB_ORDERS: cache key # ER_SQL2PARENTID: the corresponding relationship between subdivision of Sql routing cacheCopy the code

Mysql > show @@datasource;Copy the code

SQL statistics command

Mysql > show @@sql;Copy the code

Mysql > show @@sql.slow;Copy the code

Mysql > show @@sql.sum;Copy the code