Mycat gets started

Chapter 1 Introduction overview

Summary of Mycat

1. Database middleware

Mycat is database middleware. Database middleware: Connects Java applications to databases

2. Why Mycat?

• Java is tightly coupled to databases.
• The pressure of high traffic and high concurrency on database.
• The read/write request data is inconsistent

3. Comparison of database middleware

① Cobar belongs to Alibaba B2B business group. It started in 2008 and served in Alibaba for more than 3 years. It took over the schema of 3000+ MySQL databases and processed online SQL requests more than 5 billion times a day. Cobar discontinued maintenance due to the departure of the Cobar promoter. ② Mycat is the open source community on the basis of Ali Cobar secondary development to solve the problems of Cobar, and added many new functions in it. The youth is better than the blue. ③ OneProxy is developed by C based on the official proxy idea of MySQL. OneProxy is a commercial charging middleware. Some features were dropped to focus on performance and stability. (4) Kingshard is developed by a small team using go language, which still needs to be developed and improved constantly. ⑤ Vitess is produced and used by Youtube, and its architecture is very complex. MySQL native protocol is not supported and requires a lot of transformation costs. ⑥ Atlas is rewritten by 360 team based on mysql Proxy, and its functions need to be improved. It is unstable under high concurrency. MaxScale is a middleware developed by Mariadb (a version maintained by MySQL’s original author). MySQLRoute is a middleware released by Oracle, the official company of MySQL

Mycat role

1. Read/write separation

2. Data fragmentation

Vertical split (database), horizontal split (table), vertical + horizontal split (database table)

3. Integration of multiple data sources

Principle of Mycat

The most important verb in Mycat’s principle is “intercept”. It intercepts the SQL statement sent by the user. First, it does some specific analysis of the SQL statement: Such as fragment analysis, route analysis, read/write separation analysis, cache analysis, and so on, the SQL is sent to the real database at the back end, and the returned result is processed properly, and finally returned to the user.

This decoubles the distribution of the database from the code, and the programmer can’t tell if Mycat or MySQL is being used in the background.

Chapter 2 Installation and startup

2.1 installation

/usr/local/; /usr/local/; /usr/local/; Server. XML: Defines user and system variables, such as ports

2.2 Build mysql master-slave replication based on Docker

Reference blog.csdn.net/weixin_4563…

Chapter three sets up read-write separation

We set up read and write separation of database through Mycat and MySQL master/slave replication to achieve high availability of MySQL. We will set up two read and write separation modes: one master, one slave, and two master and two slave.

3.1 Set up one master and one slave

One master machine handles all write requests and one slave machine handles all read requests. The architecture is shown below To set up read/write separation, set up primary/secondary replication

3.1.1 Modifying the configuration file server.xml

Change user information to be different from MySQL

<user name="mycat" 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.
</user>
Copy the code

3.1.2 Modifying the Configuration file Schema.xml

      
<! DOCTYPEmycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<! -- Database configuration, this database is logical database, corresponding to the database in server.xml -->
	<! -- name: logical database name, corresponding to schema in server. XML checkSQLschema: database prefix Select * from (select * from (select * from));
	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
		<! -- name: specifies the name of the table in the physical database. DataNode: Specifies the nodes on which the table is stored. Use commas (,) to separate multiple nodes. Name set on the node for the dataNode below primaryKey: indicates the field name of the primaryKey. AutoIncrement: indicates whether the primaryKey is automatically added rule: indicates the name of the sharding rule. SplitTableNames are described in the following rules: Enable the table name attribute to separate multiple tables with commas, i.e. multiple tables with this configuration -->
		<table name="jpa_user" dataNode="dn1"  primaryKey="uid" autoIncrement="true" splitTableNames ="false"/>
	</schema>
	
	<! -- Shard information, that is, shard configuration -->
	<! DataHost: indicates the name of the physical database, dataHost: indicates the name of the physical database -->
	<dataNode name="dn1" dataHost="host1" database="spring_boot" />
	
	<! -- Physical database, real data store database -->
	<! -- name: physical database name, corresponding to dataHost in dataNode Balance: load balancing method 0: Disable read/write separation and send all read operations to the currently available writeHost. 1: All readHost and stand by writeHost participate in the load balancing of SELECT statements. To put it simply, when there are two modes (M1->S1, M2->S2, and M1 and M2 are in active/standby mode), under normal conditions, M2,S1, and S2 all participate in load balancing of select statements. 2: All read operations are distributed randomly on writeHost and readhost. 3: All read requests are randomly distributed to readhost for execution, and writeHost does not bear the read pressure. WriteType: write mode dbType: database type heartbeat: heartbeat detection statement -->
	<dataHost name="host1" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<! -- Can have multiple write hosts -->
		<writeHost host="hostM1" url="192.168.2.128:23306" user="root" password="123456">
			<readHost host="hostS1" url="192.168.2.128:23307" user="root" password="123456"/>
		</writeHost>
	</dataHost>
</mycat:schema>
Copy the code

In order to see the effect of read/write separation, setting balance to 2 will switch the query between the two hosts/usr/local/mycat/binDirectory using./mycat startStart Mycat or use./mycat consoleStart theThe command is used successfullyMysql -umycat -p123456 -h192.168.2.128 -p8066Login, or Navicat login

3.2 Setting up a Dual-master/Slave System

One host, M1, handles all write requests, and its slave, S1, m2, and its slave, S2, handle all read requests. When m1 is down, M2 is responsible for write requests. M1 and M2 are standby. The architecture diagram is as follows

3.2.1 Setting up two Primary and Secondary Servers

Reference blog.csdn.net/weixin_4563…

3.2.2 Modifying the Mycat configuration file schema.xml

Modify the balance property of

to set the type of read/write separation to dual primary/secondary read/write separation and set balance to 1

      
<! DOCTYPEmycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<! -- Database configuration, this database is logical database, corresponding to the database in server.xml -->
	<! -- name: logical database name, corresponding to schema in server. XML checkSQLschema: database prefix Select * from (select * from (select * from));
	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
		<! -- name: specifies the name of the table in the physical database. DataNode: Specifies the nodes on which the table is stored. Use commas (,) to separate multiple nodes. Name set on the node for the dataNode below primaryKey: indicates the field name of the primaryKey. AutoIncrement: indicates whether the primaryKey is automatically added rule: indicates the name of the sharding rule. SplitTableNames are described in the following rules: Enable the table name attribute to separate multiple tables with commas, i.e. multiple tables with this configuration -->
		<table name="jpa_user" dataNode="dn1"  primaryKey="uid" autoIncrement="true" splitTableNames ="false"/>
	</schema>
	
	<! -- Shard information, that is, shard configuration -->
	<! DataHost: indicates the name of the physical database, dataHost: indicates the name of the physical database -->
	<dataNode name="dn1" dataHost="host1" database="spring_boot" />
	
	<! -- Physical database, real data store database -->
	<! -- name: physical database name, corresponding to dataHost in dataNode Balance: load balancing method 0: Disable read/write separation and send all read operations to the currently available writeHost. 1: All readHost and stand by writeHost participate in the load balancing of SELECT statements. To put it simply, when there are two modes (M1->S1, M2->S2, and M1 and M2 are in active/standby mode), under normal conditions, M2,S1, and S2 all participate in load balancing of select statements. 2: All read operations are distributed randomly on writeHost and readhost. 3: All read requests are randomly distributed to readhost and writeHost is not burdened with read pressure. WriteType: write mode dbType: database type Heartbeat: heartbeat detection statement switchType: 1 Default value. -1: the switchover is not automatic. 2: The switchover is determined based on the primary/secondary synchronization status of MySQL. -->
	<dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<! -- Can have multiple write hosts -->
		<writeHost host="hostM1" url="192.168.2.128:23306" user="root" password="123456">
			<readHost host="hostS1" url="192.168.2.128:23307" user="root" password="123456"/>
		</writeHost>
		<writeHost host="hostM2" url="192.168.2.128:23308" user="root" password="123456">
			<readHost host="hostS2" url="192.168.2.128:23309" user="root" password="123456"/>
		</writeHost>
	</dataHost>
</mycat:schema>
Copy the code

Setup successful

Master1 and Master2 work in standby mode. The host responsible for writing data breaks down. The standby host is switched over to perform write operations to ensure high read/write separation of the database

Availability.

Chapter four vertical split — separate libraries

A database consists of many tables, and each table corresponds to different services. Vertical sharding means that the tables are divided according to services

Sort it out and distribute it across different databases, so that you can distribute the data or the pressure to different libraries

The system is segmented into user, order, transaction and payment modules.

4.1 How to Divide tables

Question: Can tables in two databases on two hosts be queried associated? Answer: No associated query. The principle of database separation: tables that are closely related to each other should be placed in one database, and tables that are not related to each other can be placed in different databases.

# client table ROWS: 200000
CREATE TABLE customer(
 id INT AUTO_INCREMENT,
 NAME VARCHAR(200),
 PRIMARY KEY(id));# order table rows:6 million
CREATE TABLE orders(
 id INT AUTO_INCREMENT,
 order_type INT,
 customer_id INT,
 amount DECIMAL(10.2),
 PRIMARY KEY(id));# order detail table ROWS :6 million
CREATE TABLE orders_detail(
 id INT AUTO_INCREMENT,
 detail VARCHAR(2000),
 order_id INT,
 PRIMARY KEY(id));Rows :20
CREATE TABLE dict_order_type(
 id INT AUTO_INCREMENT,
 order_type VARCHAR(200),
 PRIMARY KEY(id));Copy the code

How to separate the above four tables? The customer table is in one database, and the other three require associated queries in another database.

4.2 Implementation of branch library

4.2.1 Modifying a Schema Configuration File

      
<! DOCTYPEmycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<! -- Database configuration, this database is logical database, corresponding to the database in server.xml -->
	<! -- name: logical database name, corresponding to schema in server. XML checkSQLschema: database prefix Select * from (select * from (select * from));
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
		<! -- name: specifies the name of the table in the physical database. DataNode: Specifies the nodes on which the table is stored. Use commas (,) to separate multiple nodes. Name set on the node for the dataNode below primaryKey: indicates the field name of the primaryKey. AutoIncrement: indicates whether the primaryKey is automatically added rule: indicates the name of the sharding rule. SplitTableNames are described in the following rules: Enable the table name attribute to separate multiple tables with commas, i.e. multiple tables with this configuration -->
		<table name="customer" dataNode="dn2"/>
	</schema>
	
	<! -- Shard information, that is, shard configuration -->
	<! DataHost: indicates the name of the physical database, dataHost: indicates the name of the physical database -->
	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />
	
	<! -- Physical database, real data store database -->
	<! -- name: physical database name, corresponding to dataHost in dataNode Balance: load balancing method 0: Disable read/write separation and send all read operations to the currently available writeHost. 1: All readHost and stand by writeHost participate in the load balancing of SELECT statements. To put it simply, when there are two modes (M1->S1, M2->S2, and M1 and M2 are in active/standby mode), under normal conditions, M2,S1, and S2 all participate in load balancing of select statements. 2: All read operations are distributed randomly on writeHost and readhost. 3: All read requests are randomly distributed to readhost and writeHost is not burdened with read pressure. WriteType: write mode dbType: database type Heartbeat: heartbeat detection statement switchType: 1 Default value. -1: the switchover is not automatic. 2: The switchover is determined based on the primary/secondary synchronization status of MySQL. -->
	<dataHost name="host1" 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.2.128:23306" user="root" password="123456">
		</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="hostM2" url="192.168.2.128:23308" user="root" password="123456">
		</writeHost>
	</dataHost>
</mycat:schema>
Copy the code

4.2.2 Add two blank libraries

The database branch operation is not performed on the original old database. You need to install a new database on two machines and create database Orders on data nodes Dn1 and dn2 respectively

Holdings start Mycat

./mycat console
Copy the code

4.2.4 Access Mycat for library sorting

1. Access the Mycat database
2. Create 4 tables (as shown in table creation information)
3. Viewing table information

Chapter 5 Horizontal split – table

Horizontal splitting, as opposed to vertical splitting, does not classify tables, but distributes them among multiple libraries according to certain rules for certain fields.

Each table contains a portion of data. To put it simply, we can think of horizontal data sharding as sharding by row, which means that some rows in a table are shelled into one database and some rows are shelled into other databases

5.1 Implementation of separate tables

5.1.1 Selecting a table to split

MySQL has a bottleneck in the number of data stored in a single table, and when the number of data expressed in a single table reaches 10 million, the bottleneck will affect the query efficiency, and horizontal splitting (sub-table) is needed for optimization.

5.1.2 Sub-table fields

The Orders table, for example, can be partitioned according to different self-fields

Serial number	Table field	The effect
1	Id (primary key, or creation time)	Query orders pay attention to time, historical orders are less times to be queried, Such sharding results in an uneven number of visits from one node to one node.
2	Customer_id (Customer ID)	According to the customer ID to divide, two nodes access average, a customer all orders in the same node
#### 5.1.3 Modifying the configuration file Schema.xml
“`xml

<table name="customer" dataNode="dn2"/> <! > <table name="orders" dataNode="dn1 ",dn2" rule="mod_rule"/>Copy the code

1. The mycat query2. The dn13. Dn2

5.2 Mycat fragment “Join”

The Orders table has been split into tables, and the orderS_detail table associated with it has joined the query. We’re going to shard orders_detail as well. The principle of Join is shown as follows:

5.2.1 ER table

Mycat draws on the design ideas of Foundation DB, a newcomer in the field of NewSQL. Foundation DB innovatively puts forward the concept of Table Group, in which the storage location of sub-tables depends on the main Table and is physically close to each other. Therefore, the efficiency and performance problems of JION are completely solved. According to this idea, a data sharding strategy based on E-R relation is proposed, in which the records of child table and associated parent table are stored in the same data sharding.

1. Modify the configuration file schema.xml

<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
	<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
Copy the code

2. Create the orderS_detail table in dn2
3. Restart the Mycat
4. Access Mycat to insert data into the ORDERS_detail table

INSERT INTO orders_detail(id,detail,order_id) values(1.'detail1'.1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2.'detail1'.2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3.'detail1'.3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4.'detail1'.4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5.'detail1'.5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6.'detail1'.6);
Copy the code

5. Run two table JOIN statements in mycat, dn1, dn2

select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id;
Copy the code

5.2.2 global table

In the case of sharding, when the business table is sharded because of the size, the association between the business table and these affiliated dictionary tables becomes a tricky problem, considering the dictionary table has the following characteristics:

• Infrequent changes
• The amount of data changed little overall
• Data sizes are small, rarely exceeding hundreds of thousands of records

For this reason, Mycat defines a special kind of table called a “global table” that has the following properties:

• The insertion and update of the global table will be performed on all nodes in real time to keep the data consistency of each shard
• Query operations for global tables are retrieved from only one node
• A global table can JOIN any table

Defining the dictionary table or some tables that conform to the characteristics of dictionary table as global table solves the problem of data JOIN well from another aspect. With a global table + sharding strategy based on e-R relationships, Mycat can satisfy more than 80% of enterprise application development

1. Modify the schema. XML configuration file

<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>
Copy the code

2. Create the dict_order_type table at dn2
3. Restart the Mycat
4. Access Mycat to insert data into the dict_ORDER_type table

INSERT INTO dict_order_type(id,order_type) VALUES(101.'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102.'type2');
Copy the code

5. Query table data in Mycat, dn1, dn2

5.3 Common Sharding Rules

5.3.1 modulus

This rule is a touch operation on a fragment field. It is also the most commonly used rule for horizontal subtables. In 5.1 Configuring sub-tables, the Orders table uses this rule.

5.3.2 Fragmented Enumeration

You can configure possible enumeration ids in the configuration file to configure fragments. This rule applies to specific scenarios. For example, some services need to be saved by province or county, but the province or county is fixed.

1. Modify the schema. XML configuration file

<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding-by-intfile" ></table>
Copy the code

2. Modify the rule-xml configuration file

<! -- Columns -- algorithm -- columns -->
<tableRule name="sharding-by-intfile">
	<rule>
		<columns>areacode</columns>
		<algorithm>hash-int</algorithm>
	</rule>
</tableRule>
<! -- mapFile: indicates the name of the configuration file. Type: 0 is an int and non-0 is a String defaultNode: If the value is greater than or equal to 0, the default node is not set. If the value is greater than or equal to 0, the default node is set.
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
	<property name="mapFile">partition-hash-int.txt</property>
	<property name="type">1</property>
	<property name="defaultNode">0</property>
</function>
Copy the code

3. Example Modify the partition-hash-int. TXT configuration file

110 = 0 120 = 1Copy the code

4. Restart the Mycat
5. Access Mycat to create the table

# Order belonging area information tableCREATE TABLE orders_ware_info
(
 `id` INT AUTO_INCREMENT comment 'number',
 `order_id` INT comment 'Order Number',
 `address` VARCHAR(200) comment 'address',
`areacode` VARCHAR(20) comment 'Area Code'.PRIMARY KEY(id)
);
Copy the code

6. Insert data

INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (1.1.'Beijing'.'110');
INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (2.2.'tianjin'.'120');
Copy the code

7. Query Mycat, dn1, and dn2 to see the effect of data sharding

5.3 Scope Agreement

This shard is used to plan in advance which shard field range belongs to.

1. Modify the schema. XML configuration file

<table name="payment_info" dataNode="dn1,dn2" rule="auto-sharding-long" ></table>
Copy the code

2. Modify the rule-xml configuration file

<tableRule name="auto-sharding-long">
	<rule>
		<columns>order_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>
	<property name="defaultNode">0</property>
</function>
Copy the code

3. Example Modify the autopartition-long. TXT configuration file

0-102 = 0. 103-200 = 1Copy the code

4. Restart the Mycat
5. Access Mycat to create the table

# Payment information formCREATE TABLE payment_info
(
 `id` INT AUTO_INCREMENT comment 'number',
 `order_id` INT comment 'Order Number',
 `payment_status` INT comment 'Payment status'.PRIMARY KEY(id)
);
Copy the code

6. Insert data

INSERT INTO payment_info (id,order_id,payment_status) VALUES (1.101.0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (2.102.1);
INSERT INTO payment_info (id,order_id ,payment_status) VALUES (3.103.0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (4.104.1);
Copy the code

7. Query Mycat, dn1, and dn2 to see the effect of data sharding

5.4 Fragments by day

This rule is sharding by day. Set the time format and range

1. Modify the schema. XML configuration file

<table name="login_info" dataNode="dn1,dn2" rule="sharding-by-date" ></table>
Copy the code

2. Modify the rule-xml configuration file

<tableRule name="sharding-by-date">
	<rule>
		<columns>login_date</columns>
		<algorithm>partbyday</algorithm>
	</rule>
</tableRule>
<! -- dateFormat: dateFormat sNaturalDay: sBeginDate: start date sEndDate: end date Partition days, that is, the default from the start date, divided by 2 days a partition -->
<function name="partbyday" class="io.mycat.route.function.PartitionByDate">
	<property name="dateFormat">yyyy-MM-dd</property>
	<property name="sNaturalDay">0</property>
	<property name="sBeginDate">2019-01-01</property>
	<property name="sEndDate">The 2019-01-4</property>
	<property name="sPartionDay">2</property>
</function>
Copy the code

3. Restart the Mycat
4. Access Mycat to create the table

# user information tableCREATE TABLE login_info
(
 `id` INT AUTO_INCREMENT comment 'number',
 `user_id` INT comment 'User number',
 `login_date` date comment 'Login date'.PRIMARY KEY(id)
);
Copy the code

5. Insert data

INSERT INTO login_info(id,user_id,login_date) VALUES (1.101.'2019-01-01');
INSERT INTO login_info(id,user_id,login_date) VALUES (2.102.'2019-01-02');
INSERT INTO login_info(id,user_id,login_date) VALUES (3.103.'2019-01-03');
INSERT INTO login_info(id,user_id,login_date) VALUES (4.104.'2019-01-04');
INSERT INTO login_info(id,user_id,login_date) VALUES (5.103.'2019-01-05');
INSERT INTO login_info(id,user_id,login_date) VALUES (6.104.'2019-01-06');
Copy the code

6. Query Mycat, dn1, and dn2 to see the effect of data sharding

5.4 Global Sequence

In the case of database and table, the global uniqueness of self-added primary key cannot be guaranteed. For this purpose, Mycat provides global sequence and a variety of implementations including local configuration and database configuration.

5.4.1 Local Files

In this way, Mycat will configure the sequence to a file. After using the sequence configuration, Mycat will update the sequence value in the sequence_conf.properties file in classpath.

• Advantages: Local load, fast read
• Disadvantages: Poor risk resistance, unable to read local files after the host where Mycat resides is down.

5.4.2 Database Mode

Use a database table to do count accumulation. But not every time a sequence is generated, the database is read and written, which is inefficient. Mycat preloads a portion of the number segment into Mycat’s memory, so that most of the read and write sequences are done in memory. Mycat will ask the database again if it runs out of numbers in memory.

Q: So if Mycat crashes, doesn't the sequence in memory disappear? Yes. If this is the case, Mycat will start up and request a new number segment from the database, and the old number segment will be discarded. That is, if Mycat restarts, the loss is that the current number segment is not used up, but there is no primary key duplication as a resultCopy the code

1. Build library sequence scripts

Create a global sequence table on dn1CREATE TABLE MYCAT_SEQUENCE (NAME VARCHAR(50) NOT NULL,current_value INT NOT NULL,
increment INT NOT NULL DEFAULT 100.PRIMARY KEY(NAME)) ENGINE=INNODB; DELIMITER $$is the function needed to create the global sequenceCREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC 
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT CONCAT(CAST(current_value AS CHAR), ",,"CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE NAME = seq_name;
RETURN retval;
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS 
VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = VALUE
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) 
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END$$ DELIMITER ; Initialize sequence table recordsINSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS'.400000.100);
Copy the code

2. Modify Mycat configuration

Modify sequence_db_conf. The properties

#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
# means that the order of ORDERS is on the node dn1
ORDERS=dn1
Copy the code

Modify server. XML

<! Global sequence types: 0- local file, 1- database, 2- timestamp. -->
<property name="sequnceHandlerType">1</property>   
Copy the code

Restart the Mycat

3. Validate global sequence

Log in to Mycat and insert data

insert into orders(id,amount,customer_id,order_type) 
values(next value for MYCATSEQ_ORDERS,1000.101.102);
Copy the code

Query data (two inserts)After restarting Mycat, insert data again and query again

5.4.3 Timestamp Mode

Global sequence ID= 64-bit binary (42(ms)+5(machine ID)+5(business code)+12(repeated summation) converted to decimal to an 18-digit LONG type, which can be concurrently summation of 12 binary bits per millisecond.

• Advantages: Simple configuration
• Disadvantages: The 18-bit ID is too long

5.4.4 Generate global sequence autonomously

You can generate your own global sequence in a Java project as follows:

• Group according to business logic
• The single-threaded atomic INCR of Redis can be used to generate sequences, but the self-generated sequences need to be implemented in Java code in a separate project. It is recommended to use Mycat’s own global sequences.

Chapter 6 Mycat security Settings

6.1 Permission Configuration

6.1.1 User Label Permission Control

At present, Mycat does not do too complicated control for the connection control of middleware, and only does read and write permission control at the level of middleware logical library. Is configured through the user tag of server.xml.

<user name="mycat">
	<property name="password">123456</property>
	<property name="schemas">TESTDB</property>
	<property name="defaultSchema">TESTDB</property>
</user>
<user name="user">
	<property name="password">user</property>
	<property name="schemas">TESTDB</property>
	<! -- readerOnly: Permission for the application to connect to the middleware logic library. True: read-only; false: both read and write; default: false -->
	<property name="readOnly">true</property>
	<property name="defaultSchema">TESTDB</property>
</user>
Copy the code

6.1.2 Privileges label Permission control

The PRIVILEGES TAB under the User TAB provides refined DML permission control for logical databases and tables. If the check attribute under the PRIVILEGES tag is true, it is false. If the check attribute is not enabled, it defaults to false. Since the Schemas attribute of Mycat allows multiple logical databases to be configured, the schema nodes of privileges’ subnodes can also be configured to perform fine-grained DML permission control over multiple databases and tables.

<user name="mycat" defaultAccount="true">
	<property name="password">123456</property>
	<property name="schemas">TESTDB</property>
	<property name="defaultSchema">TESTDB</property>
	<! -- Table level DML permission Settings -->
	<privileges check="false">
		<schema name="TESTDB" dml="1111" >
			<! Mysql > alter table Orders not add/delete privileges
			<table name="orders" dml="0000"></table>
			<table name="tb02" dml="1111"></table>
		</schema>
	</privileges>		
</user>
Copy the code

The sequence of DML permissions is as follows: insert(new),update(modify),select(query),delete(delete), 0000- > 1111,0 indicates the forbidden permission, 1 indicates the enabled permission.

6.2 SQL to intercept

The firewall tag is used to define the firewall. Under the firewall, the whitehost label is used to define the IP whitelist, and the blacklist is used to define the SQL blacklist.

6.2.1 white list

You can set a whitelist so that a certain host user can access Mycat, while other host users cannot access Mycat.

<firewall>
   <whitehost>
   	  <! Only 192.168.2.128 host can be accessed by mycat user -->
      <host host="192.168.2.128" user="mycat"/>
   </whitehost>
</firewall>
Copy the code

6.2.2 blacklist

You can set the blacklist to achieve Mycat interception of specific SQL operations, such as add, delete, change and search interception.

<firewall>
	<whitehost>
		<! Only 192.168.2.128 host can be accessed by mycat user -->
		<host host="1 * 7.0.0. *" user="root"/>
	</whitehost>
	<blacklist check="true">
		<! -- Disable mycat from deleting -->
		<property name="deleteAllow">false</property>
	</blacklist>
</firewall>
Copy the code

Configuration items	The default value	describe
selelctAllow	true	Whether SELECT statements are allowed
deleteAllow	true	Whether DELETE statements are allowed
updateAllow	true	Whether UPDATE statements are allowed
insertAllow	true	Whether INSERT statements are allowed
createTableAllow	true	Whether to allow table creation
setAllow	true	Whether the SET syntax is allowed
alterTableAllow	true	Whether to allow the execution of Alter Table statements
dropTableAllow	true	Whether tables are allowed to be modified
commitAllow	true	Whether to allow the COMMIT operation
rollbackAllow	true	Whether to allow the roll back operation

Chapter 7 Mycat monitoring tool

7.1 Mycat – web profile

Mycat- Web is the management and monitoring platform of Mycat visual operation and maintenance, which makes up for the blank of Mycat monitoring. Help Mycat to share statistics and configuration management tasks. Mycat-web introduces ZooKeeper as a configuration center that can manage multiple nodes. Mycat- Web mainly manages and monitors Mycat traffic, connections, active threads and memory, with IP whitelist, mail alarm modules, SQL statistics and analysis of slow SQL and high frequency SQL. Provides a basis for SQL optimization.

7.2 Using mycat-web Configuration