MyCAT configuration details

server.xml Mycat configuration file, setting account, parameters, etc
Schema.xml configuration of physical database and database table corresponding to MYCAT
rule.xml Mycat fragment (sub database and sub table) rule

1: wrapper.conf
--Configure jdk
wrapper.java.command=D:/Program Files/Java/jdk1.8.0_131/bin/java.exe

2: server.xml1, user tag

<user name="root"><property name="password"></property><property name="schemas">TESTDB</property></user>

User user configuration node -- name login user name, that is, the user name connecting to Mycat -- password login password, that is, the password connecting to Mycat

--Schema database name, which will be associated with the configuration in schema.xml. Multiple are separated by commas. For example, if the user needs to manage two databases db1 and DB2, configure db1 and dbs2

The privileges tab performs refined DML permission control on users' schema s and tables

<privileges check="false"></privileges>--check Indicates whether it is turned on DML Permission check. The default is off. server.dtd In the file <!ELEMENT privileges (schema)*> Description can have multiple schema Configuration of. --dml Sequence description: insert,update,select,delete <schema name="db1" dml="0110" ><table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table></schema> db1 Your permissions are update,select. tb01 You can't do anything. tb02 Your permissions are insert,update,select,delete. Other tables default to udpate,select.

1. system tag

   All embedded in this label property Labels are related to system configuration. <property name="charset">utf8</property> character set <property name="processors">1</property> Number of processing threads. The default is cpu quantity <property name="processorBufferChunk">4096</property> The quantity left for each reading is 4096 by default. <property name="processorBufferPool">409600</property> Create share buffer The total amount of space that needs to be occupied. processorBufferChunk*processors*100. <property name="processorBufferPoolType">0</property> The default is 0. 0 means DirectByteBufferPool，1 express ByteBufferArena. <property name="processorBufferLocalPercent">100</property> Secondary sharing buffer yes processorBufferPool The percentage is set here. <property name="sequnceHandlerType">100</property> overall situation ID Generation method.(0:Local file mode, 1:Database mode; two:Time stamp sequence mode; three:by ZK generate ID；4:by ZK Increasing ID Generate. <property name="useCompression">1</property> Open mysql Compression protocol. 1 is on, 0 is off, and it is off by default. <property name="packetHeaderSize">4</property> appoint Mysql The length of the message header in the protocol. The default is 4. <property name="maxPacketSize">16M</property> appoint Mysql The maximum length of data that the protocol can carry. Default 16 M. <property name="idleTimeout">1800000</property> Specifies the idle timeout for the connection. When a connection initiates an idle check and finds that it has been idle for more than the last time, the connection will be recycled, that is, it will be closed directly. The default is 30 minutes in milliseconds. <property name="txIsolation">3</property> The initialization transaction isolation level of the front-end connection is only used during initialization. Subsequently, the back-end database connection will be synchronized according to the attributes passed by the client. Default to REPEATED_READ，The setting value is numeric. The default value is 3. READ_UNCOMMITTED = 1; READ_COMMITTED = 2; REPEATED_READ = 3; SERIALIZABLE = 4; <property name="sqlExecuteTimeout">300</property> SQL Execution timeout time, Mycat The last execution on the connection is checked SQL If the time exceeds this time, the connection will be closed directly. The default time is 300 seconds in seconds. <property name="processorCheckPeriod">1000</property> clear NIOProcessor The interval between idle, timeout and connection closing of the front and back ends. The default is 1 second, single Bit milliseconds. <property name="dataNodeIdleCheckPeriod">300000</property> The time interval between idle and timeout checks for back-end connections. The default is 300 seconds, in milliseconds. <property name="dataNodeHeartbeatPeriod">10000</property> The interval between heartbeats for all backend read and write libraries. The default is 10 seconds, in milliseconds. <property name="bindIp">0.0.0.0</property> mycat Service listening IP Address, the default value is 0.0.0.0. <property name="serverPort">8066</property> definition mycat The default value is 8066. <property name="managerPort">9066</property> definition mycat The default value is 9066. <property name="fakeMySQLVersion">5.6</property> mycat Simulated mysql Version number. The default value is 5.6 Version. Do not modify this value unless otherwise required. Currently, setting 5 is supported.5,5.6,5.7 Version, other versions may have problems. <property name="useSqlStat">0</property> Whether to enable real-time statistics. 1 is on; 0 is off. <property name="useGlobleTableCheck">0</property> Whether to enable global table consistency detection. 1 is on; 0 is off. <property name="handleDistributedTransactions">0</property> Distributed transaction switch. 0 is not to filter distributed transactions; 1 is to filter distributed transactions; 2 is not to filter distributed transactions,However, distributed transaction logs are recorded. <property name="maxStringLiteralLength">65535</property> The default is 65535. sixty-four K be used for sql Maximum text length when parsing The properties in the above examples are only part of them. There are many variables that can be configured. You can view the details SystemConfig The property content of this class. System The attributes under the label are usually on-line. They need to be modified according to the actual operation situation.

2. Firewall tag
   As the name suggests, this is about the setting of firewall, that is, limiting the requested address at the network layer, mainly to ensure that Mycat is not accessed by anonymous IP from the perspective of security

   <firewall> <whitehost> <host host="127.0.0.1" user="mycat"/> <host host="127.0.0.2" user="mycat"/> </whitehost> <blacklist check="false"> </blacklist> </firewall>

   The setting is very simple and easy to understand. As long as the white list is set, the firewall is turned on, and only the connection of the white list can be connected.

3: schema.xml
--Schema database settings. This database is a logical database, and the name corresponds to the schema in server.xml
--dataNode partition information, that is, the related configuration of the database
--dataHost physical database, the database that really stores data
1. schema tag

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="10"></schema>

The schema tag is used to define the logical libraries in the mycat instance. mycat can have multiple logical libraries, and each logical library has its own related configuration. Schema tags can be used to partition these different logical libraries
If the schema tag is not configured, the configuration of all tables will belong to the same default logical library. The concept of logical database is the same as that of MySql database. When querying tables in two different logical databases, we need to switch to the logical database for query.

--Name logical database name, corresponding to the schema in server.xml
--checkSQLschema database prefix related settings. When the value is true, for example, we execute the statement select * from TESTDB.company. mycat will change the statement to select * from company and remove TESTDB.
--sqlMaxLimit when this value is set to a certain value, Mycat will automatically add the corresponding value to each executed sql statement if no limit statement is added. If it is not written, all values will be returned by default.
It should be noted that if the running schema is a non split library, this attribute will not take effect. You need to add limit to your own sql statement.
2. table label

<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />

--name Table name, table name in physical database --dataNode The nodes to which the table is stored are separated by commas. The nodes are as follows dataNode Set name --primaryKey The name of the primary key field, which needs to be set when automatically generating the primary key --autoIncrement Self increment or not --rule The name of the fragmentation rule is shown below rule Detailed introduction --type This attribute defines the type of logical table. At present, there are only global table and ordinary table. Global table: global Common table: None Note: the global table queries any node, and the general table queries all nodes, which is inefficient --autoIncrement mysql For non self growing primary keys, use last_insert_id() No result will be returned, only 0 will be returned.Therefore, only tables with self growing primary keys can be used last_insert_id()Returns the primary key value. mycat The self growing primary key function is provided, but the corresponding mysql Data table on node, no auto_increment,So in mycat Layer call last_insert_id()It will not return results. --needAddLimit Specifies whether the table needs to be automatically added after each statement limit Limitation. Due to the use of sub database and sub table, the amount of data is sometimes very large. At this time, execute the query statement, Forget to add limt Will wait a long time, so mycat Automatically added for us limit 100，This property defaults to true，You can set it to false Disabled. If you use this function, it is best to use the global sequence of database schema. --subTables Sub table. Sub table is not supported at present Join.

1. childTable label

<table name="customer" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile"><childTable name="c_a" primaryKey="ID" joinKey="customer_id" parentKey="id" /></table>

--The childTable tag is used to define the child tables of E-R Shards. Associate with the parent table through the attributes on the label.

--Name the name of the child table

--The name of the field in the joinKey sub table

--parentKey field name in parent table

--primaryKey is the same as Table

--needAddLimit is the same as Table3 and dataNode tags

<dataNode name="dn1" dataHost="localhost1" database="db1" />

The datanode tag defines the data node in mycat, which is what we call data fragmentation. A datanode tag is an independent data fragment. The expression in the example means that the db1 physical database on the database instance named localhost1 is used to form a data partition. Finally, we use dn1 to mark the partition.

--Name defines the name of the data node. This name must be unique. We use this name on the table tag to establish the corresponding relationship between the table and the partition

--datahost is used to define which database instance the partition belongs to. The attribute corresponds to the name defined on the datahost tag

--Database is used to define that the partition belongs to a specific database on the database instance.

4. dataHost tag

This tag directly defines the specific database instance, read-write separation configuration and heartbeat statements.

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="123456"> <readHost host="hostS1" url="192.168.1.101:3306" user="root" password="123456" /></writeHost> </dataHost>

--name uniquely identifies the dataHost tag for use by the upper layer

--maxCon specifies the maximum connections per read / write instance connection pool.

--minCon specifies the minimum connection of each read / write instance connection pool and initializes the size of the connection pool

--Balance load is called type balance="0": the read-write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost. balance="1": all readhosts and stand by writeHost participate in the load balancing of the select statement. In short, when the dual master and dual slave mode (M1-S1, M2-S2 and M1 and M2 are both primary and standby), under normal conditions, M2,S1,S2 participates in load balancing of select statements. balance="2": all read operations are randomly distributed on writeHost and readHost. balance="3": all read requests are randomly distributed to the readHost corresponding to writeHst for execution. writeHost does not bear the reading and writing pressure. (version after 1.4)

--writeType load balancing type. writeType="0", all write operations are sent to the first writeHost configured, the first one is suspended, and the second writeHost is still alive. After restart, the switchover shall prevail. The switchover is recorded in the configuration file: dnindex. Properties. writeType = "1". All write operations are sent to the configured writeHost randomly. 1.5 later versions are obsolete and not recommended.

--switchType -1 no automatic switchover 1 default value automatic switchover 2 decide whether to switch based on MySQL master-slave synchronization. The heartbeat statement is show slave status3 the switching mechanism based on MySQL galaxy cluster (suitable for clusters) 1.4.1 the heartbeat statement is show status like 'wsrep%'--dbType specifies the database type of the back-end link. At present, binary MySQL protocol is supported, There are other databases linked by jdbc, such as mongodb, oracle, spark, etc

--dbDriver specifies the driver to be used after connecting to the database. Currently, the optional values are native and JDBC. If native is used, because this value executes the binary MySQL protocol, MySQL and maridb can be used. Other types need to be supported by jdbc driver. If you use JDBC, you need to put the driver jar conforming to the JDBC 4 standard into the mycat\lib directory, and check that the driver jar package includes the following directory structure file META-INF\services\java.sql.Driver. Write a specific driver class name in this file, such as com.mysql.jdbc.DriverwriteHost readHost. Specify the relevant configuration of the back-end database to mycat to instantiate the back-end connection pool.

--tempReadHostAvailable if this property is configured, the readHost under writeHost is still available, and 0 is configurable by default (0, 1). 1) Heartbeat tag this tag indicates the statement used to perform heartbeat check with the back-end database. For example, MYSQL can use select user() and Oracle can use select 1 from dual. 2) The writeHost / readHost tags both specify the configuration of the backend database and are used to instantiate the backend connection pool. The only difference is that writeHost specifies the write instance and readHost specifies the read instance. Multiple writeHost and readHost can be defined in one dataHost. However, if the backend database specified by writeHost goes down, all readhosts bound by this writeHost will be unavailable. On the other hand, because the writeHost is down, the system will automatically detect it and switch to the standby writeHost. The properties of these two tags are the same, which will be introduced here.

--host is used to identify different instances. Generally, we use M1 for writeHost and S1 for readHost.

--url back end instance connection address. Native: Address: Port: url of jdbc

--Password the backend stores the password required by the instance

--User the user name required by the backend storage instance

--Weight weight is configured as the weight of the read node in readhost

--Whether usingDecrypt encrypts the password. The default value is 0. See the official document for the specific encryption method. 4: Rule.xmlule.xml defines the rule definitions involved in splitting the table. We can flexibly use different fragmentation algorithms for the table, or use the same algorithm for the table, but the specific parameters are different. Contains the labels tableRule and function. 1. tableRule label

This tag defines table rules.
Table rules defined in schema.xml:

<tableRule name="rule1"> <rule> <columns>id</columns> <algorithm>func1</algorithm> </rule> </tableRule>

--The name attribute specifies a unique name that identifies different table rules. The embedded rule tag specifies which column in the physical table to split and what routing algorithm to use.
--columns specifies the name of the column to split.
--Algorithm uses the name attribute in the function tag. Join table rules and specific routing algorithms. Of course, multiple table rules can be connected to the same routing algorithm. Used within the table tag. Let the logical table fragment using this rule.

1. function tag

<function name="hash-int"> <property name="mapFile">partition-hash-int.txt</property> </function>

--Name specifies the name of the algorithm.
--Class specifies the specific class name of the routing algorithm.
--property refers to some properties required by specific algorithms.

7 November 2021, 21:15 | Views: 2140