MyCat tutorial 2: Implement primary/secondary mysql replication MyCat tutorial 3: Install and configure MyCat tutorial 4: implement read/write separation MyCat tutorial 5: Implement sub-table MyCat tutorial 6: Implement global serial number – global primary key growth

I. Introduction to MyCat

1. What is MyCat

MyCat is currently the most popular database middleware written based on Java language. It is a server that implements MySQL protocol. Front-end users can regard it as a database agent, which can be accessed by MySQL client tools and command line. The back-end can use MySQL native protocol to communicate with multiple MySQL servers, and can also use JDBC protocol to communicate with most mainstream database servers. Its core function is to divide databases into tables. With the master-slave mode of the database can also achieve read and write separation. MyCat is developed based on Alibaba’s open source Cobar product. The stability, reliability, excellent architecture and performance of Cobar as well as many mature use cases make MyCat very powerful. MyCat is no longer a simple MySQL agent. Its backend can support mainstream databases such as MySQL, SQL Server, Oracle, DB2 and PostgreSQL, as well as MongoDB storage in a new NoSQL mode. More types of storage will be supported in the future. From the end user’s point of view, regardless of the storage mode, MyCat 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 difficulty of development and improve the speed of development. MyCat official website: www.mycat.io/

2. Structure of MyCat

3. Benefits of using MyCat

3.1 Data Level

Mycat can manage several MySQL databases and simultaneously store and manipulate data.

3.2 Open-source Nature

  1. Mycat is middleware written in Java. Open source, free.
  2. There are many people and organizations that develop, maintain, manage, and update Mycat.
  3. Mycat version is upgraded quickly and can follow the environment. If there is a problem, it can be solved quickly.
  4. Mycat has an open source website and community. And there are officially released e-books.
  5. Mycat is alibaba’s original application of CORBA transformation.

3.3 Market Application

MyCat accounts for a very high percentage of Internet applications.

Second, the concept of MyCat

1. The segmentation

Logical segmentation. On the physical level, it is realized by using multiple databases and multiple tables.

1.1 Vertical segmentation/vertical segmentation

Storing data in one repository across multiple repositories. Since all reads and writes to a database are performed on the same library, a single library cannot solve the problem of large-scale concurrent writes. For example, we will set up definition database workDB, commodity database payDB, user database userDB, log database logDB and so on, respectively used to store project data definition table, commodity definition table, user data table, log data table and so on.

advantages

  1. Reduce the impact of locks on queries when incremental data is written.
  2. As the number of single tables decreases, common query operations reduce the number of records that need to be scanned, reducing the number of rows required for a single query, reducing disk I/OS, and shortening the latency.

Disadvantages: Cannot solve the problem of too much data in a single table.

1.2 Horizontal segmentation/horizontal segmentation

Block data stored in one table into multiple tables. When the amount of data in a table is too large, we can divide the data in the table according to certain rules, and then store it in multiple tables with the same structure and in different libraries. For example, in our userDB userTable a large volume of data, you can put the userDB segmentation for the structure of the same multiple userDB: part0DB, part1DB etc, then userDB userTable, segmentation for many userTable: UserTable0, userTable1, etc., and then store these tables to multiple userDB according to certain rules.

advantages

  1. Single-table concurrency is improved and disk I/O performance is improved.
  2. If high concurrency occurs, the master table can divide the concurrency stress into smaller tables for different queries.

Disadvantages: Cannot implement table join queries.

2. Logical library -Schema

The database. Defined in Mycat exists logically. But it doesn’t exist physically. This is mainly the concept provided for vertical segmentation.

3. Logical table-table

The table defined in Mycat exists logically, but not physically. This is mainly the concept provided for horizontal segmentation.

4. Default port

application port
MySQL 3306
Mycat 8066
tomcat 8080
Oracle 1521
nginx 80
http 80
redis 6379

5. DataHost – dataHost

Address of the host where the physical MySQL database is stored. You can use hostname,IP, domain name definition.

6. DataNode – dataNode

Configure physical database. physical nodes for data storage. Is the database.

7. Sharding rules

How to access physical databases and tables when controlling data. It’s the algorithm that accesses dataHost and dataNode. How to access dataHost and dataNode algorithms while Mycat processes specific data CRUD. Such as: hash algorithm, CRC32 algorithm and so on.



Follow wechat official account [Programmer’s Dream, focusing on Java, SpringBoot, SpringCloud, microservices, Docker, and full-stack technologies such as backend separation.