I brief introduction

MyCat is an open source distributed database system and a server that implements MySQL protocol. Front end users can regard it as a database agent and access it with MySQL client tools and command line, while its back end can communicate with multiple MySQL servers using MySQL native protocol or with most mainstream database servers using JDBC protocol, Its core function is to divide tables and databases, that is, a large table is horizontally divided into N small tables and stored in the back-end MySQL server or other databases.

With the development of the current version, MyCat is no longer a simple MySQL agent. Its back-end can support mainstream databases such as mysql, SQL Server, Oracle, DB2 and PostgreSQL. It also supports the storage of MongoDB, a new NoSQL method, and will support more types of storage in the future. In the view of end users, regardless of the storage mode, in MyCat, it is a traditional database table, which supports standard SQL statements for data operation. In this way, for the front-end business system, it can greatly reduce the development difficulty and improve the development speed.
Summary: Mycat is a well received database middleware. In order to reduce the pressure of single database, it can realize master-slave, hot standby, separate tables and databases, so as to realize the distributed architecture of database.

II Mycat principle

The most important verb in the principle of Mycat is "intercept". It intercepts the SQL statements sent by the user. Firstly, it makes some specific analysis on the SQL statements, such as fragment analysis, routing analysis, read-write separation analysis, cache analysis, etc., then sends the SQL to the real database at the back end, processes the returned results appropriately, and finally returns them to the user.

In the above picture, the application no longer directly accesses the Database, but accesses Mycat. Mycat interacts with the Database, and the Database data is returned to Mycat, which then returns to the application. Three databases are the real Database, also known as three nodes, also known as three Shards.

Summary: as a middleware, Mycat can be accessed directly by the application. Instead of managing the real database, Mycat can interact with the real database. There may be multiple real databases, which is the distributed architecture, that is, multi node (multi partition)

III Usage scenario of Mycat

Since the development of Mycat, the applicable scenarios have been very rich, and new users continue to give new and innovative solutions. The following are several typical application scenarios:

Simple read-write separation. At this time, the configuration is the simplest. It supports read-write separation. The master-slave switches to separate tables and libraries. For more than 10 million tables, it supports a maximum of 100 billion single table fragmentation

For multi tenant applications, each application has a library, but the application only connects to Mycat, so it does not transform the program itself to realize multi tenant

The report system handles the statistics of large-scale reports with the help of the table splitting ability of Mycat

Replace Hbase and analyze big data

As a simple and effective scheme for real-time query of massive data, for example, 10 billion frequently queried records need to query the results within 3 seconds. In addition to the query based on primary key, there may also be range query or other attribute query. At this time, Mycat may be the simplest and effective option

Mycat long term Roadmap

Strengthen the functions of distributed database middleware, make it have rich plug-ins, powerful database intelligent optimization function, comprehensive system monitoring ability, and convenient data operation and maintenance tools, and realize advanced functions such as online data expansion and migration

Further advance into the field of big data computing, deeply combine with distributed real-time stream engines such as Spark Stream and Storm, which can complete the ability of OLAP direction such as fast giant table Association, sorting and grouping aggregation, and integrate some popular and commonly used real-time analysis algorithms, making it easier for engineers and DBA s to realize some advanced data analysis and processing functions with Mycat.

Continuously strengthen the technical level of Mycat open source community, attract more IT technical experts, make Mycat community become China's Apache, push Mycat to Apache foundation, and become the top open source project in China. Finally, some volunteers can become full-time Mycat developers, and enhance their glory and strength together.

Relying on Mycat community, gather 100 CXO level elites and raise funds to build kiss villa. Mycat community + kiss villa = China's largest IT O2O community

IV Mycat installation

Mycat official website: Mycat2

Upload the installation to the linux system and unzip it. Move the unzipped file to / usr/local

V Simple application of Mycat (sub database and sub table)

Requirement: I have installed Mysql on both the ECS and the local machine. Now I want to use these two My sql to provide services to my application, mainly related to Maven_ The user table of SSM data is as follows:

Native sql statements

CREATE DATABASE /*!32312 IF NOT EXISTS*/`maven_ssm` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `maven_ssm`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` char(20) NOT NULL,
  `password` char(33) NOT NULL,
  `address` char(8) NOT NULL,
  `birthday` date DEFAULT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`id`,`username`,`password`,`address`,`birthday`,`department_id`) values (1,'Xiao Yu','123456','Chengdu, Sichuan','2018-06-01',2),(2,'Xiao Yu','982352','Chengdu University ','2015-04-03',3);

sql on server

CREATE DATABASE /*!32312 IF NOT EXISTS*/`maven_ssm` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `maven_ssm`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` char(20) NOT NULL,
  `password` char(33) NOT NULL,
  `address` char(8) NOT NULL,
  `birthday` date DEFAULT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`id`,`username`,`password`,`address`,`birthday`,`department_id`) values (3,'Xiao Wang','982352','Mianyang, Sichuan','1996-08-26',2);

The default port of Mycat is 8066. For applications, the database name is the middleware logical database name of Mycat, which is no longer a real database name

The configuration file of Mycat is under the conf Directory: server xml,schema,rule.xml, and ehcache xml,log4j2.xml, we mainly use the first three

First configure: server XML, delete all the users under the default configuration file, and create a new user test:

Open the server under the conf folder xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
	<system>
	<property name="defaultSqlParser">druidparser</property>
      <!--  <property name="useCompression">1</property>--> <!--1 For on mysql Compression protocol-->
	<!-- <property name="processorBufferChunk">40960</property> -->
	<!-- 
	<property name="processors">1</property> 
	<property name="processorExecutor">32</property> 
	 -->
		<!--The default is 65535 64 K be used for sql Maximum text length when parsing -->
		<!--<property name="maxStringLiteralLength">65535</property>-->
		<!--<property name="sequnceHandlerType">0</property>-->
		<!--<property name="backSocketNoDelay">1</property>-->
		<!--<property name="frontSocketNoDelay">1</property>-->
		<!--<property name="processorExecutor">16</property>-->
		<!-- 
			<property name="mutiNodeLimitType">1</property> 0: Enable decimal order (default); 1: enable 100 million level data sorting
	    	<property name="mutiNodePatchSize">100</property> 100 million order batch
			<property name="processors">32</property> <property name="processorExecutor">32</property> 
			<property name="serverPort">8066</property> <property name="managerPort">9066</property> 
			<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
			<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
	</system>
	<user name="test">
		<property name="password">test</property>
		<property name="schemas">TESTDB</property>
		<property name="readOnly">false</property>		
	</user>
	<!-- <cluster> <node name="cobar1"> <property name="host">127.0.0.1</property> 
		<property name="weight">1</property> </node> </cluster> -->
	<!-- <quarantine> <host name="1.2.3.4"> <property name="user">test</property> 
		</host> </quarantine> -->

</mycat:server>

Then write schema xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
		<!-- auto sharding by id (long) -->
		<table name="user" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2" rule="mod-long" />
	</schema>
	<dataNode name="dn1" dataHost="localhost1" database="maven_ssm" />
	<dataNode name="dn2" dataHost="remotehost" database="maven_ssm" />

	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
		writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="localhost:3306" user="root"
			password="123456">
		</writeHost>
	</dataHost>
	<dataHost name="remotehost" 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.1.101:3306" user="root" password="123456"></writeHost>
    </dataHost>
</mycat:schema>

Finally, modify the count in mod long in the rule to 2

	<function name="mod-long" class="org.opencloudb.route.function.PartitionByMod">
		<!-- how many data nodes -->
		<property name="count">2</property>
	</function>

Finally, switch to the bin directory

Start the mycat command

./mycat start

View native Mysql

View Mysql on the server

Connecting Mycat Middleware

 ​​​​​​​​​​​​​​

Query operation

 

Insert operation 1

insert into user(id,username,password,address,birthday,department_id) values(4,'Handsome 1','123456','Chengdu, Sichuan','2018-06-01',2);

Insert operation 2

insert into user(id,username,password,address,birthday,department_id) values(5,'Handsome 2','123456','Chengdu, Sichuan','2018-06-01',2);

At this time, the databases of the local machine and the ECS are inserted into each other and distributed evenly. Enter Mysql again to verify:

Mysql server

Query again:

Vi Not applicable scenario

Reference blog: mycat does not adapt to the scene (transformation should be avoided as far as possible) _gaobudong1234 blog - CSDN blog _mycatstabilityhttps://blog.csdn.net/gaobudong1234/article/details/79581846

Reference blog: mycat usage details_ Blog of illiterate youth - CSDN blog_ mycat scorehttps://blog.csdn.net/qq_35890572/article/details/81162110