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

The main purpose of this article is to share the configuration file of mycat. This paper also introduces the calculation process and usage of several slicing algorithms commonly used in mycat. I hope you can get something through this article.

Relationships between common profiles

From the figure above, you can see that the core configuration files of Mycat are all in xml format. The purpose of these configuration files is as follows:

Server.xml: used to configure system parameters, user information, access permissions, SQL firewall and SQL blocking features and other schema.xml: used to configure logical libraries and logical table related information rule.xml: if horizontal sharding is used, you need to use this file to configure log4j2.xml:Mycat log-related configurations of split rules, such as log output format, log level, etc.

When you apply to connect to the Mycat service, Mycat first authenticates the user through the configuration information in server.xml. After the user has passed the verification, the logical library and logical table that the user sees are all configured in schema.xml. When horizontal sharding is used, Mycat will use the rules configured in rule.xml to locate the specific physical database location, thus completing the write / read data.

Server.xml configuration details 1. System tags

The system parameters used to configure Mycat are in the following format:

${value}

Example of configuring a Mycat service port:

3306

Examples of common system parameters:

8066 9066 0.0.0 2048 utf8 8 1800000 104857600 0 1 100300 1 (?: (\ s*next\ s+value\ s+for\ sMYCATSEQQ _ (\ w +)) | |\) |\ s) *) + false io.mycat.route.sequence.handler.HttpIncrSequenceHandler 000 false false 2 true 02, user tags |

The access users and permissions used to configure Mycat are in the following format:

${value}.

Example configuration:

123456 mall_db false

In addition to configuring permissions on the library may not be enough, sometimes we need to configure user access to certain tables. The following is an example:

123456 mall_db,db1,db2

The number configured by the dml attribute is the permission bit, which corresponds to the four permissions of insert,update,select,delete. For example, when the value of dml is 0110, it means that you have update and select permissions, not insert and delete permissions. Therefore, if the permission bit is 1, you have the corresponding operation permission, and if it is 0, you do not have the operation permission.

In this example, the mall user pairs:

User_table table does not have any operation permissions order_table table has all operation permissions other tables only have update and select permissions to encrypt plaintext passwords

In the above example of configuring a user, the password is written in clear text in the configuration file. However, the user's password is security-sensitive and generally does not write the password directly in the configuration file, but a password after encryption. Otherwise, as long as you have permission to view server.xml files, you can easily get each user's password, which is very insecure.

Therefore, Mycat provides a tool for encrypting plaintext passwords, which can be encrypted in an jar package using the following command:

[root@txy-server / usr/local/mycat] # java-cp lib/Mycat-server-1.6.7.4-release.jar io.mycat.util.DecryptUtil 0:root:123456

Parameter description:

0: represents mycat user login password encryption (1 is dataHost encryption) root: user name 123456: plaintext password

After successful execution, you will get an encrypted string:

GO0bnFVWrAuFgr1JMuMZkvfDNyTpoiGU7n/Wlsa151CirHQnANVk3NzE3FErx8v6pAcO0ctX3xFecmSr+976QA==

Copy the string and replace the plaintext password in the configuration file, as shown in the following example:

1 GO0bnFVWrAuFgr1JMuMZkvfDNyTpoiGU7n/Wlsa151CirHQnANVk3NzE3FErx8v6pAcO0ctX3xFecmSr+976QA==... log4j2.xml profile

We all know that Mycat is developed using Java, so its logging framework also uses log4j2 within the Java ecosystem. The configuration related to the Mycat log is in the log4j2.xml file, and this section describes some common configuration items.

1. Pattern tag

It is used to configure the output format of Mycat logs. Default is as follows:

% d {yyyy-MM-dd HH:mm:ss.SSS}% 5p [% t] (% l) -% m%n%d {log}: log time format% 5p: output log level% t: output log thread name% l: log output statement code location% m: log content specified in the output code% n: output a newline character

For more Pattern values, please see the official documentation.

Here, we have intercepted a log of Mycat, and we can see that it corresponds to Pattern one by one:

2020-01-09 15 INFO 22 PhysicalDatasource.java:564 57.960 INFO [Timer1] (io.mycat.backend.datasource.PhysicalDatasource.getConnection (PhysicalDatasource.java:564))-no ilde connection in pool 1838161857, create new connection for hostM1 of schema db3 totalConnectionCount: 0 increamentCount: 12, level attribute

The log output level used to configure Mycat. The default is info level:

For more information about the built-in log level of log4j2, please see the official documentation.

Detailed explanation of rule.xml file

When we need to implement data sharding through Mycat, we need to use the rule.xml configuration file, which is used to configure:

The slicing function corresponding to the slicing rule of horizontal slicing

This is an example of a sharding rule configuration:

Id hash-mod-4 Tips: the name of the sharding rule is as meaningful as possible. For more information on the naming format, please see {sharding algorithm name}-{number of shards} _ {sharding column}. This naming format is used in the above example.

The tag mentioned in the above example is a sharding algorithm or sharding function used to configure a table, as shown in the following example:

four

The required parameters of each slicing algorithm may be different, so there can be multiple property tags, and their attributes vary according to the specific slicing algorithm. The following is an example:

Several slicing algorithms commonly used in yyyy-MM-dd 0 2014-01-01 2014-01-31 10

Mycat has a lot of built-in sharding algorithms, and we can also develop our own slicing algorithms according to the actual situation. Let's take a look at what common slicing algorithms are:

PartitionByMod: simply take the module, and get the sharding position PartitionByHashMod: hash module directly through the column value. First, perform the hash operation on the column value, and then take the module to get the sharding position PartitionByFileMap: slicing the data according to the enumerated value. For example, in the scene where data is sliced through the regional id in remote scenes, PartitionByPrefixPattern: string range modulo is taken, and the module PartitionByMod is taken according to the first few bits of the long string.

The working principle of the simple module slicing algorithm:

There are two databases in the diagram, and each database is a shard, so you can get the shard position by modulating 2 with the value of the shard column.

Example configuration:

Id mod-long 2

Summary:

Simple, uniform data distribution, suitable for integer type columns, can not be used for non-integer column calculation: shard column% shard cardinality implementation class full name: io.mycat.route.function.PartitionByModPartitionByHashMod

When you need to take a module as a non-integer, you can use the hash module slicing algorithm. Its working principle is shown in the following figure:

Perform hash operation on the value of the fragment column to get an integer value, and then take the module of the number "count" of the fragment to get the position of the fragment.

Example configuration:

Login_name hash-mod 2

Summary:

Can be used for a variety of data types of sharding columns, such as strings, dates and other sharding is not as uniform as a simple modular algorithm, because there is hash repetition. If the values of two identical data are the same after hash operation, then the shard position obtained after taking the module is also calculated in the same way: hash (sharding column)% shard cardinality implementation class full name: io.mycat.route.function.PartitionByHashModPartitionByFileMap

The first two slicing algorithms calculate the slicing position through the algorithm itself, which can not be controlled manually. If you need to manually control the position of fragments, you can use the fragment enumeration algorithm. This algorithm enables us to specify some enumerated values to control the sharding position, which is equivalent to manually specifying which shard some data should go to.

For example, if there is a column in a table that stores the user's region id, we want to map the region id to the region where the database is located, so that the data with area id 1 is sharded into the area 1 database, the data with region id 2 is shredded into the area 2 database, and so on. In this scenario, you can use the multipart enumeration algorithm, which works as shown in the following figure:

Configure a fragment relational mapping through mapFile in the format of key-value,key for enumeration and value for the index of the data node. The enumeration in the figure is area_id, where an area_id corresponds to a data node. Area_id without configuration mapping will be shredded to the corresponding data node of DEFAULT_NODE.

Example configuration:

Area_id hash-int partition-hash-int.txt 0 0Tips: the index of the data node starts at 0, which should be noted when editing mapFile

Summary:

You can specify the location of the data store according to the enumerated value. You need to add mapFile under the $MYCAT_HOME/conf directory to configure the mapping between the enumerated value and the node: hash (shard column)% shard cardinality implementation class full name: io.mycat.route.function.PartitionByFileMapPartitionByPrefixPattern

The slicing algorithms introduced above are all sliced according to the column. In practical work, we may encounter such a demand: we need to slice through the first few bits or the last few bits of the string. For example, it is calculated by the first five digits of the order number, or by the user's last name. In this scenario, the string range modular slicing algorithm can be used.

For example, the first three bits of the string ABCDEFGHI need to be sliced, as shown in the following figure:

The specified string range is calculated and summed by ascii code respectively, and then the configured module cardinality is calculated. Finally, the segmented data node is obtained according to the mapping relationship between the value range configured in mapFile and the data node index. Therefore, mapFile needs to configure all possible ranges of values, otherwise an error will be reported if the corresponding data node is not found.

How it works:

Example configuration:

Login_name sharding-by-prefix-pattern prefix-partition-pattern.txt 128 2

Summary:

You can determine the storage location according to the first N characters of the specified string. You need to add mapFile under the $MYCAT_HOME/conf directory to configure the mapping relationship between the module range and the node to implement the full name of the class: io.mycat.route.function.PartitionByPrefixPatternschema.xml file

Purpose:

Configure logical libraries and logical tables configure data nodes stored in logical tables configure physical database server information schema tags corresponding to data nodes

The schema tag is used to define a logical library, for example:

The checkSQL schema attribute determines whether to check whether the SQL sent to Mycat contains the library name. If it is true, the library name in the SQL will be deleted and the name attribute will be deleted to define the name of the logical library. The sqlMaxLimit attribute must be unique and cannot be used to limit the number of rows that return the result set. A value of-1 means that the restriction is turned off. If the restriction is not enabled, then the limit randomDataNode attribute configured in server.xml is taken by default and some random statements are sent to the table tag in the data node.

After using the schema tag to define the logical library, you also need to use the table tag to define the logical table. Example:

The name attribute defines the name of the logical table, which must be unique, unique, and consistent with the physical table name in the database. Use comma division to configure multiple tables, that is, multiple tables use this configuration primaryKey attribute to specify the primary key in the logical table, and the dataNode attribute also needs to be consistent with the primary key of the physical table to specify the name of the data node where the physical table resides. When configuring multiple data nodes, you need to specify an index range: dn1 $0,743. Note that after the data node is defined, the order cannot be changed, otherwise the data will be confused. The rule attribute is used to specify the sharding rule name, which corresponds to the name attribute of the tag in rule.xml. If sharding is not required, you can not specify whether the splitTableNames attribute definition allows the definition of dataNode tags for multiple tables.

The dataNode tag is used to define the data node, which points to the physical database where logical tables are stored. Example:

The name attribute defines the name of the data node. The unique dataHost attribute must specify the physical host where the shard is located. The database attribute specifies the name of the physical database dataHost tag.

The dataHost tag is used to define the back-end physical database host information, which has two child tags and can define a set of database host information. For example, define a set of database host information for the master-slave cluster structure:

WriteHost tag configuration write instance, that is, the master node readHost tag configuration read instance, that is, the salve node readHost from the master is a child tag of writeHost and has a binding relationship with writeHost

Multiple writeHost and readHost can be defined within a dataHost. However, if the back-end database specified by writeHost goes down, then all readHost bound to this writeHost will be unavailable. On the other hand, due to the writeHost outage system will automatically detect and switch to the backup writeHost.

Example configuration:

The select user () dataHost tag attribute name attribute is used to define the host name, and the unique maxCon attribute must specify the maximum number of connections for each read / write instance connection pool. In other words, the writeHost and readHost tags nested within the tag all use the value of this attribute to instantiate the maximum number of connections of the connection pool. the minCon attribute specifies the minimum number of connections of each read-write instance connection pool, that is, the size of the connection pool dbType attribute specifies the database type of the back-end connection, which currently supports the binary mysql protocol, and there are other database dbDriver attributes that use JDBC connections to specify the driver used to connect to the back-end database. Available values are native and JDBCslaveThreshold attributes to define the master-slave replication delay threshold. When Seconds_Behind_Master > slaveThreshold, the read-write separation filter will filter out the Slave machine to prevent the read-write separation balance attribute from specifying the type of load balance for read-write separation. Currently, there are four values: 0: do not enable read-write separation mechanism. All read operations are sent to the currently available writeHost 1: all readHost and stand by writeHost participate in the load balancing of select statements 2: all read operations are randomly distributed on writeHost and readhost 3: all read requests are randomly distributed to the corresponding readhost of wiriterHost for execution. That is, writerHost does not bear the read pressure, and all read requests are performed by readhost. Note that this value is only available in versions 1.4 and later. 1.3 there is no writeType attribute to specify the type of load balancer to write to the instance. Currently, there are four values:-1: does not automatically switch 0: all write operations are sent to the first writeHost of the configuration, and the first one is hung up to the second writeHost that still exists. After the restart, the switch shall prevail. The switch is recorded in the configuration file: dnindex.properties1: all write operations are randomly sent to the configured writeHost,1.5 and then discarded and not recommended. 2: decide whether to switch based on the status of MySQL master-slave synchronization. (1.4 added) switchType attribute is used to specify the mode of master-slave switching:-1: does not automatically switch 1: default Automatic switching 2: decide whether to switch based on the status of MySQL master-slave synchronization. Heartbeat detection statement is: show slave status3: MySQL galary cluster-based switching mechanism (suitable for cluster, added in 1.4.1), heartbeat detection statement is show status like 'wsrep%'

In addition, the slaveThreshold attribute is used to cooperate with the writeType attribute implementation to switch between master and slave according to the master-slave delay. The official document is described as follows:

1.4.The read-write separation mechanism of MySQL master-slave replication state binding is supported to make the read more secure and reliable. The configuration is as follows: MyCAT heartbeat check statement is configured as show slave status, and two new attributes are defined on dataHost: switchType= "2" and slaveThreshold= "100". This means that the read-write separation and switching mechanism of MySQL master-slave replication state binding is enabled. The Mycat heartbeat mechanism determines the current master-slave synchronization status and Seconds_Behind_Master master-slave replication delay by detecting the three fields "Seconds_Behind_Master", "Slave_IO_Running" and "Slave_SQL_Running" in show slave status. When Seconds_Behind_Master > slaveThreshold, the read-write separation filter filters out the Slave machine to prevent reading old data from a long time ago, and when the master node goes down. The switching logic checks whether the Seconds_Behind_Master on the Slave is 0, which indicates master-slave synchronization and can be safely switched, otherwise it will not be switched.

Heartbeat tag

The heartbeat tag indicates the statement used for heartbeat checking with the back-end database. For example, MySQL can use select user (), Oracle can use select 1 from dual, and so on.

This tag also has a connectionInitSql attribute, which is mainly put into the initialization SQL statement that needs to be executed when using the Oracla database. For example: alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Note: if the statement to configure master-slave switch must be: show slave status after 1.4,

Writehost tag, readHost tag

Both tags are used to configure a set of information about the master-slave database, and Mycat instantiates the back-end connection pool with the connection information configured by these two tags. The only difference is that writeHost configures write instances (master), readHost configures read instances (salve), and readHost is a child tag of writeHost. Through these two tags, you can combine read / write instances to meet the requirements of the system.

Multiple writeHost and readHost can be defined within a dataHost. However, if the back-end database specified by writeHost goes down, then all readHost bound to this writeHost will be unavailable. On the other hand, when a writeHost goes down, the system automatically detects it and switches to a backup writeHost.

Attributes of writehost tags and readHost tags

The attributes of these two tags are the same, so let's introduce them together:

The host attribute is used to identify different instance names. The general writeHost name uses M1 as the suffix, and readHost uses S1 as the suffix url attribute to configure the connection address of the database. If native's dbDriver is used, it is generally in the form of address:port. If you use JDBC or other dbDriver, you need a special specification. For example, when using JDBC, you can write this: jdbc:mysql://localhost:3306/user attribute configure database username password attribute configure database password weight attribute configure a database as the weight usingDecrypt attribute of a read node in readhost specify whether to encrypt the password, default is 0, and configure it as 1schema.xml profile example if it needs to be turned on

Select user ()

After reading the above, do you have any further understanding of mycat's configuration file? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel. Thank you for reading.

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.