Shulou(Shulou.com)06/03 Report--

This article focuses on "how to master Mycat middleware", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to master Mycat middleware.

Distributed data Storage and Mycat

Introduction to Mycat Architecture

Core concept and configuration of Mycat

Distributed data Storage and Mycat

As we all know, in the use scenario of distributed database, the storage of data is not stored in a database of a server like a centralized database, but distributed in different databases of different servers. The application will be accessed according to different databases, as shown in figure 1, the application needs to access different databases: database 1, database 2, and database 3 use different database connections. if a data table exists in all three databases, it also needs to be accessed in a specific way, which can be said to be troublesome at this end of the application.

Figure 1

The distributed database team increases the complexity of application access.

Because applications encounter similar problems when accessing distributed databases, database middleware is introduced to simplify the problems that applications need to face. As shown in figure 2, after introducing Mycat database middleware between the application and the database, it makes the programming of the application more convenient. Instead of facing the distributed database, the corresponding data operation can be completed only by database programming for Mycat middleware, and it is suitable for the following scenarios:

Read-write separation: you can write to database 1 through Mycat and read to databases 2 and 3. If the write server is down, Mycat can temporarily transfer the write request to another database server to ensure the availability of the database.

Vertical sub-table sub-library: store the order table in databases 1 and 2, and the commodity table in database 3. That is, put different tables in different libraries.

Horizontal sub-table sub-library: different records of the order table are stored in databases 1 and 2 through a slicing algorithm (for example, hash modeling). That is, put different data from the same table into tables in different libraries.

Access different databases: databases 1 and 2 access the MySQL database, while Database 3 accesses the Oracle database.

Control the number of connections from the application to the database: each database connection takes up the resources of the database server, and the number of requests that each database server can connect is limited. Here Mycat can control the number of connections requested by the front-end application to the database server to ensure the high availability of the database server.

In other words, when the application initiates a SQL statement, you don't have to worry about which server the database is stored in and whether the database table is divided into tables and libraries, just tell Mycat the data to be checked. Mycat will return the results to the application after considering the above scenario, so that developers can manipulate the data more easily and quickly.

Figure 2

Mycat is introduced to assist the application to realize the separation of reading and writing, table and database operation.

The data operations described above require a large amount of code to be maintained on the application side without Mycat middleware, but with Mycat, the application only needs to be configured for Mycat and can complete these operations.

Mycat is an open source database middleware for enterprise application development, supporting large database clusters, things, and ACID. It is suitable for high availability, database read-write separation, data hierarchical storage guarantee, sub-table and sub-database of large databases, parallel computing, database routing and integration of databases such as MySQL, Oracle, SQL Server and so on. As Mycat open source middleware has a process of development, each version of the iteration will encounter some problems, has been updated to Mycat 2.0 is a relatively stable version.

If qualified students try to use the latest version, or use Mycat version 1.6.5 or above, it fixes some common SQL functions. However, Mycat is not omnipotent, there will still be some problems, we here to list its advantages and disadvantages of the following table for your reference.

Advantages

Shortcoming

After the split, the business is clear and the split rules are clear.

Some business tables cannot be join, and can only be called through the API.

Easy expansion and integration between systems

Cross-database transactions are difficult to handle

Data maintenance is simple

After vertical segmentation, some business data are too large, and there is still a single performance bottleneck.

The performance bottleneck of single library big data and high concurrency is solved.

Split rules are difficult to abstract

The splitting rules are encapsulated and almost transparent to the application side, and developers do not need to care about the details of the split.

Sharding transaction consistency is difficult to solve, which needs to be solved through XA or with the help of other distributed transaction methods, such as Redis, Zookeeper.

Improve the stability and load capacity of the system.

During the second expansion, it is difficult to migrate and maintain the data.

Introduction to Mycat Architecture

The above mentioned the role of Mycat in the distributed database, making the development of the application focus on the business itself, leaving the problems of dividing tables and libraries, separating reading and writing, accessing different data and controlling database connections to Mycat to deal with. So what architecture does Mycat use to achieve the above functions? let's take a look at the components and architecture of Mycat with me.

Logically divide the following six modules.

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Communication protocol module: mainly responsible for the underlying communication functions, such as sending and receiving data, thread callback and so on. Using Reactor and Proctor mode, NIO and AIO are realized in the network IO architecture.

SQL execution module: as the name implies, after receiving the SQL to be executed, it connects to the database through the database connection module, obtains the target database through the connection pool, and then distributes the SQL statements to the database node for execution through SQL route parsing.

Route parsing module: when Mycat implements the function of sub-table and sub-database, routing is very important. After receiving the query statement requested by the application, the module will connect the back-end data and route the request to the table of the corresponding database for query through the rule settings of the configuration file. At the same time, the module also shoulders the function of SQL parsing, and needs to parse the EXPLAIN, SET, SHOW, SELECT and other commands of SQL statements, so as to obtain table names, conditions, field lists and other information. At the same time, the SQL statement can be rewritten.

Database connection module: mainly responsible for creating, managing and maintaining the back-end database and the corresponding database connection pool. And through the connection pool mechanism to manage the life cycle of database connections.

Result set processing module: the query results need to be summarized and sorted before they are returned. Especially in the case of data fragmentation, the returned data comes from different database servers and tables, and the data needs to be summarized before it can be returned to the application.

Monitoring and management module: mainly monitor and manage the connection, memory and other resources in Mycat. Monitoring parameters include: number of connections, cache hits and so on.

The above six modules look abstract, but it is convenient to remember them if you sort out the logical order. The communication protocol module receives the SQL request from the application and gives it to the SQL execution module. After parsing the SQL, the execution module needs to know which nodes to access the database. Therefore, it requests the route parsing module to know which databases to access, establishes a database connection through the database connection module, distributes SQL to the corresponding database for execution, and summarizes and sorts the returned data through the result set processing module, and finally returns it to the application. The monitoring and management module is like a butler monitoring the use of connections, memory and other resources in the Mycat.

If the above components are shown through the processing flow of Mycat:

Figure 3

Mycat data processing flow

As shown in figure 3, the whole diagram consists of the upper, middle and lower steps.

The above section represents the client, which hosts multiple applications that initiate SQL requests to the Mycat service in the middle. The intermediate Mycat service, which contains the six components mentioned above, is responsible for processing and responding to application requests. The following storage layer can interface with different data sources to provide storage services for Mycat. The whole request processing process is divided into two parts: request and response, which are represented by "top-down" and "bottom-up" arrows, respectively. Here, we divide the processing of Mycat into six steps as follows:

1. When the client application initiates a SQL request to the Mycat service, the communication protocol module submits the request to the route resolution module through NIO/AIO.

two。 The routing parsing module includes the functions of SQL parsing, optimization and routing. The main function is to parse the SQL into the command to be executed, optimize it, and then find the database server to execute through the routing rules.

3. The request is then handed over to the SQL execution module and distributed to the database server for execution according to the results of the route resolution.

4. Before execution, the corresponding database connection needs to be obtained through the database connection module, and the database connection pool needs to be managed.

5. Similarly, the communication protocol module is also needed to transmit information when distributing and executing SQL.

6. After executing the SQL request, the database will aggregate, sort and summarize the data through the result processing module, and finally return the data to the client application to complete the whole request process.

Core concept and configuration of Mycat

After describing the architecture and workflow of Mycat, I believe everyone has a certain understanding of the implementation mechanism and structure of Mycat. From the perspective of architecture, it will be helpful to understand the following core concepts. If the above is about Mycat logical architecture, then the core concepts and configuration described below relate to the scope of specific operations.

After all, Mycat needs to provide configuration tools to complete the developer's functions, and then through the architecture to implement these rules, and finally achieve transparency to distributed database development. The next step is to introduce the core concepts encountered in the process of using Mycat, as well as how these concepts are configured and the corresponding configuration files. It is convenient for us to apply Mycat middleware to the ground.

Figure 4

Mycat core concept map

As shown in figure 4, the renovations describe the relationships between Mycat core concepts and describe the configuration files in which these core concepts are configured.

Let's follow the serial number from top to bottom to look at these concepts:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

The system information and user information in the Mycat are configured in the Server.xml file, where the configuration of the user (user) contains the basic information of the user and the permissions to access the schema (logical library).

Schema.xml contains Mycat's definition of data, and Schema (logical library) contains one or more table (logical tables).

Table (logical table) describes the server and physical database where the data is stored through the included datanode (data node).

Datahost and database are included in datanode. Where datahost represents the server cluster where the database resides, and database is the description of the physical database.

The fragment definition rules for each table are defined in the rule.xml file. When defining the rules, rule will call function to describe the rule algorithm.

Above we give you an overall introduction to the core concepts of Mycat and their corresponding configuration files, and then we will describe the above concepts in depth.

1. User (User)

The user in Mycat is a logical user who defines the user information used to connect to the Mycat middleware and stores that information in the server.xml file. As shown in figure 5, this is the definition of user information in the server.xml file.

Figure 5

User's definition Server.xml file

You can see that the user name is defined as "test" and the password "password" is 123456. The "test_db" is defined in the schemas tag, which is the logical database that the user can access. Defining the true description in the privileges tag also gives the user specific permissions to use the logical library. The dml to operation for "test_db" is defined here as four digits, which are "0110" respectively.

In fact, each bit represents an operation, and the four corresponding operations are "insert, update, select, delete". If the corresponding number of digits is "1", the operation is supported, and if it is "0", the corresponding operation is not supported.

"0110" means that insert operation, update operation, select operation and delete operation are not supported. And so on, there is a table tag under the schema tag that sets permissions on logical tables. For example, the dml of table01 is 0000, which means that four operations cannot be performed.

two。 Logical Library (schema)

Developers need to operate on a specific database when developing a database, such as finding, updating, deleting, and so on. However, in the era of distributed databases, databases are deployed in different server nodes in a distributed way, so it is more difficult to access these databases. After the intervention of Mycat, the concept of logical library is introduced. Logical library is a conceptual definition, which corresponds to one or more physical databases, and its definition is carried out in the schema.xml file.

As shown in figure 6:

Figure 6

Definition of logical Library in schema.xml File

Defines a logical database named "test_db".

SqlMaxLimit is used to limit the maximum number of rows that can be returned per query. Here, it is set to "1000". When it is "- 1", there is no limit.

CheckSQLschema is a switch on whether to access the database by library name and table name, which is "fasle" by default.

3. Logical table (table)

Since there is a logical library, there must be a logical table, usually the logical table corresponds to the physical table, it may be a logical table corresponding to multiple physical tables distributed in different data nodes, or it may correspond to a physical table without slicing. Again, these configurations are done in the schema.xml configuration file.

As shown in figure 7:

Figure 7

Define logical tables in a logical library

In the logical library "test_db" defined above, a logical table is defined. The name of the table is "order". The name of the logical table needs to be consistent with the name of the physical table. Because the relationship between logical and physical tables is no longer defined in Mycat, this implicit approach is used to make them correspond.

The "primaryKey" tag defines the primary key of the logical table, and the primary key of the logical table is consistent with the primary key of the physical table. The purpose of setting the primary key is that if the primary key is not selected when the data is shredded, Mycat will query the physical table through the definition of the primary key, thus improving the efficiency of the query.

The name of the physical database is defined in the dataNode tag. If a physical table is fragmented and stored in multiple physical databases, each physical database needs to be segmented by a comma. Here the order of the physical nodes is the order of the indexes. As shown in the figure, the index of "orderdb01" is 0 and the index of "orderdb02" is 1.

Finally, the rules of horizontal slicing are defined in the Rule tag, and the specific definition method will be explained in detail later.

4. Data Node (DataNode)

Define the physical database corresponding to the logical storage, which contains two important information:

Name of the database host (cluster) that holds the data

Name of the physical database

Also configured under the dataNode tag of the schema.xml configuration file.

As shown in figure 8:

Figure 8

Data node definition

The "dataHost" tag describes the database server (server node / cluster) of the physical database.

The "database" tag describes the name of the client physical database, which must exist on the server node.

From the definition of two data nodes ("orderdb01", "orderdb02") to a "mysql01" datahost, you can see that their databases are all stored in the same database cluster.

5. Data Host Cluster (DataHost)

Data host cluster mainly describes the host information that stores the database, and usually describes the cluster of a database server.

As shown in figure 9:

Figure 9

Data host cluster

The database host cluster of "mysql01" is defined in dataHost, including "maxCon" (maximum number of connections), "minCon" (minimum number of connections), "balance" (read-write separation switch), "dbType" (database type) and other tags.

Importantly, three database servers are defined in this cluster. "192.168.0.1" this is a write server, as opposed to a read server, it is "192.168.0.2". In addition, there is a write server that is "192.168.0.3".

6. Slicing rule (TableRule) & function (Function)

In the schema.xml file, it is basically the definition of data, in which there is a definition for rule in the logic table, which is used to define sharding rules. Generally speaking, the definition of sharding rules is completed in the rule.xml file.

As shown in figure 10:

Figure 10

TableRule and function definition

The sharding rule is defined in tableRule as "mod_3_order_id", which is used for rule tags in logical tables. As can be seen from the meaning of the name, the order_id field is calculated by taking module 3, so as to realize the slicing operation.

The order_id field is defined as the module in the columns tag.

The specific slicing algorithm is defined in the algorithm tag. The algorithm name here can be found in the corresponding definition in the function node below.

The name in Function corresponds to the algorithm name in tablerule above, which is also "mod_3", that is, by taking the module 3.

The namespace and the corresponding class name of the Class class implemented by the algorithm are defined in the class tag.

The 3 defined by the Count tag is the number of the specific module. Here, the module is taken from 3.

According to the definition of the configuration rule given above, the slicing rule shown in figure 11 can be obtained.

Figure 11

Slicing rules define routing

When the application initiates an SQL query on the order table, it queries the order_id=1 data.

When the request is submitted to the Mycat, the order_id pair 3 is modularized to get 1 in a simple way.

Because the order table is stored in DB0, DB1 and DB2 libraries, the value of the module is routed to the order table of DB1 and the SQL statement is executed to complete the distributed query.

At this point, I believe you have a deeper understanding of "how to master Mycat middleware". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.