Mycat configuration details
schema.xml
Schema. XML, as one of the important configuration files in MyCat, manages the logical libraries, tables, sharding rules, Datanodes and DataSource of MyCat. Understanding these configurations is a prerequisite for using MyCat correctly.
schema The label
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"></schema>
Copy the codeThe schema tag is used to define the logical libraries in MyCat instances. MyCat can have multiple logical libraries, each with its own configuration. You can use the Schema tag to divide these different logical libraries.
If the schema tag is not configured, all table configurations belong to the same default logical library.
As shown in the preceding configuration, two different logical libraries are configured. The concept of a logical library is the same as that of Database in MYSQL Database. When querying tables in these two logical libraries, we need to switch to the logical library to query the required tables.
Related attributes of the schema tag:
dataNode
This property is used to bind the logical library to a specific database as follows:
<schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
Copy the code<! — Configure tables to be sharded –>
< table name = "tuser" dataNode dn1 = "" / > < / schema >Copy the codeNow tuser is bound to the database configured by DN1, and can access the database directly. If the table is not configured, it will go to the default node DN2. Note that the table not configured in the shard tool cannot be displayed, but can be used normally.
checkSQLschema
When this value is set to true, if we execute the statement SELECT * from testdb.travelRecord; MyCat will change the statement to select * from travelRecord; . That is, the characters representing the schema are removed to avoid errors when sent to the back-end database.
However, even if this value is set to true, if the statement has a name other than that specified by the schema, for example: select * from db1.travelRecord; MyCat does not remove the db1 field, and an error will be reported if the library is not defined, so it is best to provide SQL statements without the db1 field.
sqlMaxLimit
When the value is set to a value. For each SQL statement executed without a limit statement, MyCat will automatically add the corresponding value. For example, if the value is 100, run **select * from testdb.travelRecord; Select * from testdb. travelRecord limit 100; * * the same.
If you do not set this value, MyCat will display all the queried information by default, resulting in too much output. Therefore, in normal use, it is recommended to add a value to reduce excessive data returns.
Of course, SQL statements explicitly specify the size of limit, which is not bound by this property.
table The label
The Table tag defines the logical tables in MyCat, and all the tables that need to be split need to be defined in this tag.
Attributes of the table tag:
The name attribute
Define the name of the logical table. This name is the same as the name specified by the create table command in the database. The name must be unique within the same schema tag.
The dataNode properties
Define the dataNode to which the logical table belongs. The value of this attribute needs to correspond to the value of the Name attribute in the dataNode tag. If too many DNS need to be defined, you can use the following methods to reduce the configuration:
Rule properties
This attribute is used to specify the name of the rule to be used by the logical table. The rule name is defined in rule-xml and must correspond to the value of the name attribute in the tableRule tag.
RuleRequired properties
This property is used to specify whether the table is bound to sharding rules. If true is set but no specific rules are configured, the program will report an error.
PrimaryKey properties
The logical table corresponds to the primary key of the real table. For example, if the rules for sharding are non-primary keys, the query statement is sent to all the configured DN when the primary key is used for query. If the primary key of the real table is configured, the query statement is sent to all the configured DN. MyCat caches information about the primary key and the specific DN, so the next time a query is made using a non-primary key, it does not perform a broadcast query and sends statements directly to the specific DN. However, despite the configuration, if the cache does not hit, it still sends statements to the specific DN to retrieve data.
The type attribute
This attribute defines the types of logical tables. Currently, there are only two types of logical tables: Global table and Common table. Corresponding configuration:
· Global table: global.
· Normal table: do not specify all tables whose value is GloBLa.
AutoIncrement properties
Mysql does not return last_insert_id() for a non-auto-growing primary key. Therefore, only tables with self-growing primary keys can return the primary key value from last_insert_id().
Mysql > select last_insert_id() from last_insert_id() where auto_increment is not specified.
Since the shard key is not included in the INSERT operation, mycat first takes the global sequence of the table and assigns it to the shard key. Last_insert_id () returns the value of the inserted fragment key. If you want to use this feature it is best to use the global sequence of database schema.
Use autoIncrement= “true” to specify that this table has a self-growing primary key, so that mycat does not throw an exception if the shard key is not found.
Use autoIncrement= “false” to disable this function, or you can remove it directly. It is disabled by default.
subTables
Add subTables=”t_order$1-2,t_order3″.
Currently, only one dataNode can be configured for a sub-table since sub-table 1.6. The sub-table does not support join statements under various conditions.
NeedAddLimit properties
Specifies whether the table needs to automatically append a limit to each statement. Because of the use of sub-tables, the amount of data can be very large. Execute the query at this point, if you happen to forget to add a number limit. It will take a while to query all the data.
So, myCat automatically assigns us LIMIT 100. Of course, if there is a limit in the statement, it will not be added again. This property defaults to true, but you can disable the default behavior by setting it to false.
childTable The label
The childTable tag is used to define child tables for e-R sharding. Associated with the parent table by attributes on the tag.
Attributes of the childTable tag:
The name attribute
Defines the table name of the child table.
JoinKey properties
When inserting a child table, the value of this column is used to find the data node stored in the parent table.
ParentKey properties
The value specified by the attribute is typically the column name associated with the parent table. The program first obtains the JoinKey value, and then generates a query statement based on the column name specified by the parentKey attribute. The query statement is executed to obtain the shard in which the parent table is stored. To determine where the child table is stored.
PrimaryKey properties
As described in the table tag.
NeedAddLimit properties
As described in the table tag.
dataNode The label
The dataNode tag defines the data nodes in MyCat, which are commonly referred to as data fragments. A dataNode label is an independent data fragment.
In this example, we use the db1 physical database on the database instance named LCH3307 to form a shard. Finally, we identify the shard with the name Dn1.
DataNode tag attributes:
The name attribute
Define the name of the data node. This name needs to be unique. We need to apply this name to the table tag to establish the relationship between the table and the shard.
DataHost properties
This property is used to define which database instance the shard belongs to, and the property value refers to the Name property defined on the dataHost tag.
The database properties
This attribute is used to define the specific library on which specific database instance the sharding attribute belongs to, because sharding is defined in two dimensions: instance + specific library. Because the tables created on each library and table structure are the same. This makes it easy to split the table horizontally.
dataHost The label
As the last tag in schema.xml, this tag also exists as the lowest tag in the mycat logical library, directly defining specific database instances, read/write separation configurations, and heartbeat statements.
Properties of the dataHost tag:
Uniquely identifies the dataHost label for use by upper-layer labels.
MaxCon properties
Specifies the maximum connection for each read/write instance connection pool. That is, both writeHost and readHost tags nested within the tag use the value of this attribute to instantiate the maximum number of connections in the pool.
MinCon properties
Specifies the minimum connection for each read/write instance connection pool, initializing the pool size.
The balance attribute
Load balancing type, current value:
-
Balance =”0″, the read/write separation mechanism is disabled and all read operations are sent to the currently available writeHost.
-
Balance =”1″, all readHost and stand by writeHost participate in the load balancing of select statement, in short, when double
In active/standby mode (M1->S1, M2->S2, and M1 and M2 work in active/standby mode), M2,S1, and S2 all participate in load balancing of SELECT statements.
-
Balance =”2″, all read operations are distributed randomly on writeHost and readhost.
-
Balance =”3″, all read requests are sent to wiriterHost at random. WriterHost is not under read pressure. Note that balance=3 is available only in version 1.4 and later.
WriteType properties
Load balancing type. The value can be:
-
WriteType =”0″, all write operations are sent to the first configured writeHost. The first writeHost fails and is switched to the second writeHost that is still alive. The switch takes effect after the restart, and is recorded in the configuration file :dnindex.properties.
-
If writeType is set to “1”, all write operations are randomly sent to the configured writeHost. After 1.5, this parameter is not recommended.
DbType property
Specifies the type of database to connect to at the back end. Currently, binary mysql protocol is supported, as well as other databases that use JDBC connections. For example, mongodb, Oracle, and Spark.
DbDriver properties
Specifies the Driver used to connect to the back-end database. Currently, the available values are Native and JDBC. With Native, mysql and Maridb can be used because this value executes the binary mysql protocol. Other types of databases require JDBC driver support.
The postgresQL native protocol is supported from version 1.6.
If JDBC is used, place the JAR package that meets the JDBC 4 standard in the MYCAT\lib directory and check that the JAR package contains the following files: meta-INF \services\java.sql.Driver. Write the specific Driver class name in this file, for example:
Com. Mysql. JDBC Driver.
SwitchType attribute
-1 Indicates that the switchover is not automatic
1 Default value: automatic switchover
2 Determine whether to perform a switchover based on the primary/secondary synchronization status of MySQL
The heartbeat statement is show slave status
3 Switching mechanism based on MySQL Galary Cluster (suitable for Cluster) (1.4.1)
Show status like ‘wsrep%’
SlaveThreshold properties
Mycat heartbeat mechanism Check “Seconds_Behind_Master” in show slave status. “Slave_IO_Running”,”Slave_SQL_Running” to determine the status of the current primary/secondary synchronization and Seconds_Behind_Master replication delay. When Seconds_Behind_Master>slaveThreshold, the read/write split filter will filter out the Slave machine to prevent old data from being read long ago.
heartbeat The label
This tag identifies the statements used to perform a heartbeat check with the back-end database. For example,MYSQL can use select user(), Oracle can use select 1 from dual, etc.
This tag also has a connectionInitSql property, which is where the initial SQL statement that needs to be executed when using the Oracla database is placed. Example: ALTER session set NLS_date_format =’ YYYY-MM-DD HH24 :mi:ss’
7.7.1 writeHost Label, readHost The label
Both of these tags specify the configuration of the back-end database to myCAT to instantiate the back-end connection pool. The only difference is that writeHost specifies the write instance and readHost specifies the read instance, which are grouped to meet the requirements of the system.
Multiple writeHosts and readhosts can be defined within a dataHost. However, if the back-end database specified by writeHost goes down, all readHosts bound to this writeHost will become unavailable. On the other hand, the system automatically detects the writeHost outage and switches to the standby writeHost.
These two tags have the same attributes
The host properties
To identify different instances, M1 is used for writeHost and S1 is used for readHost.
The url attribute
The connection address of the back-end instance is generally in the form of Address :port if the native dbDriver is used. Use JDBC or whatever
DbDriver needs to be specified. When using JDBC, so to write: JDBC: mysql: / / localhost: 3306 /.
The user attribute
The user name required by the back-end storage instance.
Password attributes
Password required by the back-end storage instance.
Weight attribute
The weights are configured in readhost as read node weights (after 1.4).
* * * * usingDecrypt attribute
Password encryption The default value is 0. No If you need to enable configuration 1 and use the encryption program to encrypt passwords, run the following command:
Execute mycat jar program (after 1.4.1) :
Java – cp Mycat – server – the 1.4.1 – dev. Jar IO. Mycat. Util. DecryptUtil 1: host: user: password
Jar is the jar of the Mycat download directory
1:host:user:password 1 is the encryption flag of the DB and host is the host name of dataHost
server.xml
Server.xml holds almost all of the system configuration information required by MyCat
user The label
<user name="test">
<property name="password">test</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
<property name="benchmark">11111</property>
<privileges check="false">
<schema name="TESTDB" dml="0010" showTables="custome/mysql">
<table name="tbl_user" dml="0110"></table>
<table name="tbl_dynamic" dml="1111"></table>
</schema>
</privileges>
</user>
Copy the codeIt is used to define users and permissions for logging in to Mycat. For example, in the above example, I have defined a user named test with a password of test, and only TESTDB schema is accessible.
If I define multiple schemas in schema.xml, the user cannot access any other schema.
Nested property tags are specifically declared property values
Benchmark properties
Benchmark: MyCAT connection service degraded:
When the total number of connections on the current end reaches a benchmark value, requests from this account are rejected. 0 or no indicates no limit. Example: 1000
privileges Child nodes
The check attribute in the PRIVILEGES node is used to indicate whether DML permission checking is enabled. The default value is false to indicate that DML permission checking is disabled. Of course, the PRIVILEGES node is not configured. Equivalent to check = false,
Since the Schemas attribute of Mycat allows multiple Schemas to be configured for one user, the schema nodes of privileges can be configured for multiple schemas to perform fine-grained DML permission control for multiple databases and tables
system The label
Charset attribute
Character set Settings.
Processors properties
This property is mainly used to specify the number of threads available on the system. The default value is the number of CPU core threads on the machine.
ProcessorBufferChunk properties
This property specifies the size of the Socket Direct Buffer allocated each time. The default value is 4096 bytes. This property also affects the length of the buffer pool. If the number of buffers obtained at one time is too large and insufficient, warnings are often generated, you can adjust the size appropriately.
ProcessorExecutor properties
This property is primarily used to specify the fixed thread pool size of Business Cube shared on NIOProcessor. Mycat submits tasks to this thread pool when it needs to process some asynchronous logic. This connection pool is not used as much in the new version, so you can set it to a smaller value.
SequnceHandlerType properties
Specifies the type of global sequence to use Mycat. 0 is the local file mode, 1 is the database mode, 2 is the timestamp sequence mode, 3 is the distributed ZK ID generator, 4 is the ZK incremental ID generation.
Added two global ID generation algorithms for ZK from 1.6.
Mysql connection related properties
Initializing the mysql front and back end connections
IdleTimeout: specifies the idleTimeout period of the connection. If a connection has been idle for more than the last time it was used, the connection will be reclaimed. The default is 30 minutes in milliseconds.
Service-related attributes
Here’s a service-related property that affects how external systems perceive MyCat.
BindIp: IP address listened by the mycat service. The default value is 0.0.0.0.
ServerPort: Defines the port used by mycat. The default value is 8066.
ManagerPort: Defines the management port of Mycat. The default value is 9066.
fakeMySQLVersion
Mycat emulates the mysql version number. The default value is 5.6. Do not change this value unless required.
This feature has been supported since version 1.6.
Global table consistency check
<**property name="useGlobleTableCheck"** >0</**property**> <! -- 1 indicates that full overtime consistency check is enabled, 0 indicates that full overtime consistency check is disabled -->Copy the codeThe consistency check is performed by adding the _MYCAT_OP_TIME field to the global table. The type is BigINT. The create statement is executed through myCAT and adds this field automatically.
This feature has been supported since version 1.6.
Distributed transaction switch
<! -- Distributed transaction switch, 0 indicates that distributed transactions are not filtered, 1 indicates that distributed transactions are filtered (if only global tables are involved in distributed transactions, distributed transactions are not filtered), Filter 2 for distributed transactions, but distributed transaction log records - > < property name = "handleDistributedTransactions" > 0 < / property >Copy the codeMain application scenarios, mainly to control whether cross-library transactions are allowed.
This feature has been supported since version 1.6.
Off Heap for Mycat
This feature has been supported since version 1.6.
<property name="useOffHeapForMerge">1</property>
Copy the code1. Merge/order by/group by/limit with Direct Memory.
2. Use the useOffHeapForMerge parameter in server. XML to configure whether to enable non-heap memory to process cross-shard result sets
3.Mycat memory hierarchical management:
A. Result set processing memory;
B. Reserved memory.
C. There are three memory blocks for network processing.
The network processing Memory is all Direct Memory, and the result set Memory is divided into Direct Memory and HeapMemory. However, only Direct Memory is currently used. The system reserves On Heap Memory.
rule.xml
Rule-xml defines the rule definitions involved in splitting the table. We can flexibly use different sharding algorithms for tables, or use the same algorithm for tables with different parameters. This file contains the tableRule and function tags. TableRule and function can be added as required.
TableRule label
This tag defines the table rules.
Define table rules in schema.xml:
<tableRule name="rule1">
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
Copy the codeThe name attribute specifies a unique name that identifies the different table rules.
The embedded rule tag specifies which column in the physical table to split and which routing algorithm to use.
Columns specifies the name of the column to be split.
The algorithm uses the name attribute in the function tag. Join table rules and specific routing algorithms. Of course, multiple table rules can be joined to the same routing algorithm. Used within the table tag. Let the logical table shard using this rule.
The function labels
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
Copy the codeName Specifies the name of the algorithm.
Class Specifies the class name of the routing algorithm.
Property is a set of properties required by the specific algorithm.