Shulou(Shulou.com)05/31 Report--

This article introduces you how the core configuration of Mycat is, the content is very detailed, interested friends can use it for reference, hope to be helpful to you. 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 rights, SQL firewall and SQL blocking features, etc.

Schema.xml: used to configure information related to logical libraries and logical tables

Rule.xml: if horizontal sharding is used, you need to use this file to configure sharding rules

Configuration related to log4j2.xml:Mycat logs, 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:

The user_table table does not have any operation permissions

The order_table table has all operation permissions

Other tables only have update and select permissions

Encrypt plaintext password

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 {yyyy-MM-dd HH:mm:ss.SSS}: time format of the log

% 5p: level of output log

% t: thread name of the output log

% l: code location where the log output statement is located

% m: output the log contents specified in the 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:

Slicing rules for horizontal slicing

The fragmentation function corresponding to the slicing rule

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 model, and get the fragment position directly from the column value.

PartitionByHashMod: hash the module. First perform the hash operation on the column values, and then take the module to get the fragment position.

PartitionByFileMap: multipart enumeration, sharding data according to enumerated values, for example, sharding data through regional id in remote and multi-active scenes

PartitionByPrefixPattern: the string range is modularized, and the module is sliced according to the first few bits of the long string.

PartitionByMod

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 columns of integer type, but not for columns of non-integer type

Calculation method: slice column% slice base

Full name of the implementation class: io.mycat.route.function.PartitionByMod

PartitionByHashMod

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 sharding columns of multiple data types, such as strings, dates, etc.

The slicing algorithm is not as uniform as the simple modular algorithm, because there is hash repetition. The value of two identical data after hash operation is the same, then the position of the fragment obtained after taking the module is the same.

Calculation method: hash (sharding column)% sharding cardinality

Full name of the implementation class: io.mycat.route.function.PartitionByHashMod

PartitionByFileMap

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 0

Tips: the index of the data node starts at 0, which should be noted when editing the mapFile

Summary:

You can specify the location of the data store according to the enumerated value

You need to add mapFile to the $MYCAT_HOME/conf directory to configure the mapping between enumerated values and nodes

Calculation method: hash (sharding column)% sharding cardinality

Full name of the implementation class: io.mycat.route.function.PartitionByFileMap

PartitionByPrefixPattern

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:

The storage location can be determined according to the first N characters of the specified string

You need to add mapFile in the $MYCAT_HOME/conf directory to configure the mapping relationship between the module range and the node

Full name of the implementation class: io.mycat.route.function.PartitionByPrefixPattern

Schema.xml file

Purpose:

Configure logic library and logic table

Configure the data nodes stored in the logical table

Configure the physical database server information corresponding to the data node

Schema tag

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

...

The checkSQLschema attribute determines whether to check whether the SQL sent to Mycat contains the library name. If it is true, the library name in SQL will be deleted.

The name attribute defines the name of the logical library, which must be unique and cannot be repeated.

The sqlMaxLimit property is used to limit the number of rows that return a result set, and a value of-1 turns off the limit. If the limit is not enabled, the limit configured in server.xml is selected by default.

The randomDataNode attribute definition sends some random statements to the data node

Table tag

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

The primaryKey attribute specifies the primary key in the logical table, which also needs to be consistent with the primary key of the physical table.

The dataNode attribute specifies the name of the data node on which the physical table resides, configuring multiple data nodes in an indexed order separated by commas, or specifying an index range: dn1 $0-743. Note that after the data node is defined, the order cannot be changed, otherwise it will lead to data confusion.

The rule attribute is used to specify the name of the sharding rule, which corresponds to the name attribute of the tag in rule.xml. If sharding is not required, it may not be specified.

Whether the splitTableNames attribute definition allows the definition of multiple tables

DataNode tag

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, which must be unique

The dataHost attribute specifies the physical host where the shard is located

The database property 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:

The writeHost tag configures the write instance, that is, the master node from the master

The readHost tag configures the read instance, that is, the salve node from the master

ReadHost is a child tag of writeHost and is bound to 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:

Select user () dataHost tag attribute

The name property is used to define the host name and must be unique

The maxCon property specifies the maximum number of connections for each read / write instance connection pool. That is, the writeHost and readHost tags nested within the tag will use the value of this attribute to instantiate the maximum number of connections in the connection pool.

The minCon property specifies the minimum number of connections for each read-write instance connection pool, that is, the size of the initialization connection pool

The dbType attribute specifies the database type of the back-end connection, which currently supports the binary mysql protocol, as well as other databases that use JDBC connections

The dbDriver attribute specifies the driver used to connect to the backend database. Available values are native and JDBC.

The slaveThreshold attribute is used to define the master-slave replication latency threshold. 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.

The balance attribute specifies the type of load balancer for the separation of read and write. Currently, there are 4 values:

0: the read-write separation mechanism is not enabled, and 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 1.4 and later, but not in 1.3.

The writeType attribute specifies 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 fails to cut to the second writeHost that is still alive. After restart, the switch will prevail, and the switch will be recorded in the configuration file: dnindex.properties

1: 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)

The switchType attribute is used to specify how to switch between master and slave:

-1: does not automatically switch

1: default value, automatic switching

2: decide whether to switch based on the status of MySQL master-slave synchronization. The heartbeat detection statement is: show slave status

3: MySQL galary cluster-based handover mechanism (suitable for clusters, 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.

The url property is used to configure the connection address of the database, which is generally in the form of address:port if you use native's dbDriver. If you use JDBC or other dbDriver, you need a special specification. For example, when using JDBC, you can write: jdbc:mysql://localhost:3306/

User attribute configuration database user name

Password attribute configuration database password

The weight attribute configures the weight of a database as a read node in readhost

The usingDecrypt attribute specifies whether to encrypt the password. The default is 0, and if it needs to be enabled, it is configured as 1.

Schema.xml configuration file example select user () on how the core configuration of Mycat is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.