This is the fourth day of my participation in the August Text Challenge.More challenges in August

preface

This article will introduce the Mycat configuration file in detail, understand the information of the configuration file, is the premise of the correct use of Mycat.

[Photo source]

When an application connects to the Mycat service, Mycat first authenticates the user through the configuration information in server.xml. After the authentication, the logical libraries and logical tables are configured in schema. XML. When horizontal sharding is used, Mycat uses the rules configured in rule-xml to locate the specific physical database location to write/read data.

Server.xml configuration

Server.xml holds all the system-level configuration information required by Mycat.

The system label

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

<system> 
    <! ${key} specifies the name of the configuration attribute, and ${value} specifies the value of the configuration attribute. 
    <property name="${key}">${value}</property> 
</system>
Copy the code

Common system parameters are as follows:

<system> 
    <! -- Mycat service connection port --> 
    <property name="serverPort">8066</property> 
    <! -- Mycat service management port --> 
    <property name="managerPort">9066</property> 
    <! -- mycat service listening IP --> 
    <property name="bindIp">0.0.0.0</property> 
    <! -- 0 indicates that password login is required, and 1 indicates that password login is not required. The default value is 0. If the value is set to 1, you need to specify the default account. 
    <property name="nonePasswordLogin">0</property> 
    <! Write queue size of front-end connection --> 
    <property name="frontWriteQueueSize">2048</property> 
    <! -- Set character set encoding --> 
    <property name="charset">utf8</property> 
    <! -- Number of processes in mycat --> 
    <property name="processors">8</property> 
    <! -- Idle connection timeout in milliseconds --> 
    <property name="idleTimeout">1800000</property>
    <! -- Default maximum returned data set size --> 
    <property name="defaultMaxLimit">100</property> 
    <! -- Maximum package size allowed --> 
    <property name="maxPacketSize">104857600</property> 
    <! -- 0 Indicates that an error is reported when an Unknown command is received. 1 indicates that the packet is ignored and an OK packet is returned. Mysql > select * from user where user is logged in to mysqld; 
    <property name="ignoreUnknownCommand">0</property> 
    <! -- 1 indicates sending handshake packets -->
    <property name="useHandshakeV10">1</property> 
    <! SQL > disallow SQL from escaping keyword ' 'symbol
    <property name="removeGraveAccent">1</property> 
    <! -- 1: enable real-time statistics, 0: disable --> 
    <property name="useSqlStat">0</property> 
    <! -- 1 indicates that full overtime consistency check is enabled, 0 indicates that full overtime consistency check is disabled --> 
    <property name="useGlobleTableCheck">0</property> 
    <! SQL execution timeout unit: seconds --> 
    <property name="sqlExecuteTimeout">300</property> <property name="sequnceHandlerType">1</property> 
    <! MYCATSEQ_ must be entered with MYCATSEQ_ or MYCATSEQ_. 
    <property name="sequnceHandlerPattern">(? :(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property> 
    <! -- If the sub-query contains associated query, check whether the associated field contains fragment fields. The default false -- -- > 
    <property name="subqueryRelationshipCheck">false</property> 
    <! -- Specify a custom global sequence number loading class --> 
    <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property> 
    <! - defaults to type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool - > 
    <property name="processorBufferPoolType">0</property> 
    <! -- Distributed transaction switch, 0: no distributed transactions are filtered, 1: distributed transactions are filtered (if only global tables are involved in distributed transactions, no distributed transactions are filtered), 2: distributed transactions are not filtered, but distributed transaction logs are logged --> 
    <property name="handleDistributedTransactions">0</property>
    <! -- off heap for merge/order/group/limit 1 0 close - > 
    <property name="useOffHeapForMerge">0</property> 
    <! Select * from zooKeeper; 
    <property name="useZKSwitch">false</property> 
    <! If true, the isolation level is strictly observed, and the connection is not switched in a transaction when only the SELECT statement is present. 
    <property name="strictTxIsolation">false</property> 
    <! -- Isolation level used when Mycat connects to database 1 -- read uncommitted 2 -- Read Committed 3 -- repeatable read 4 -- serialized --> 
    <property name="txIsolation">2</property>
    <! Select * from zooKeeper; 
    <property name="useZKSwitch">true</property> 
    <! If 0, catlet tasks involving multiple Datanodes will not be executed across threads. 
    <property name="parallExecute">0</property> 
</system>
Copy the code

The user label

Used to configure access users and permissions for Mycat in the following format:

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">TESTDB</property>
    <property name="defaultSchema">TESTDB</property>
    <! --No MyCAT Database selected schema will be used as schema.

    <! -- Table level DML permission Settings -->
    <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                    <table name="tb01" dml="0000"></table>
                    <table name="tb02" dml="1111"></table>
            </schema>
    </privileges>
</user>
Copy the code
  • nameSpecify user name
  • defaultAccountSet whether the current account is the default account

The property tag

  • passwordSpecify account password
  • schemasSet the accessible schema for the current account. Multiple schemas can be specified, separated by commas
  • readOnlyTrue indicates read-only, false indicates read/write
  • banchmarkLimit the total number of front-end connections. If the value is 0 or not set, the number of front-end connections is not limited
  • usingDecrypt0 indicates that the password is not encrypted, and 1 indicates that the password is encrypted

The privileges TAB

Fine-grained DML permission control for user schemas and tables.

The check attribute indicates whether the DML permission check function is enabled. The default value is disabled.

DML order description: insert, update, select, delete

schemaAttributes in the tag:

  • nameSpecify the logical library name
  • dmlThe current user’s permission control for this logical library

tableAttributes in the tag:

  • nameSpecifies the logical table name
  • dmlThe permission control of the current user on this logical table, where,tb01Without permission,tb02There are four kinds of permissions

Firewall label

Global firewall Settings to ensure that Mycat is not accessed by anonymous IP from a security perspective.

<firewall>
    <whitehost>
        <host host="127.0.0.1" user="root"/>
        <host host="127.0.0.2" user="root"/>
    </whitehost>
    <blacklist check="false"></blacklist>
</firewall>
Copy the code
  • writehostSet the whitelist. Wildcard characters can be used%Whitelist indicates the IP addresses that can be accessed
  • blacklistOf the labelcheckIf the property is set to false, blacklist check is disabled. If the property is set to true, DML permission can be specified.

Schema. The XML configuration

  • Configure logical libraries and logical tables
  • Configure the data nodes to be stored in the logical table
  • Configure information about the physical database server corresponding to the data node

Schema tag

The schema tag is used to define logical libraries. Example:

<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">.</schema>
Copy the code
  • name: Specifies the schema name
  • checkSQLschema: Whether to check the schema name in the SQL, for example, executeselect * from TESTDB.db1If this property is set to true, Mycat will change the statement toselect * from db1
  • sqlMaxLimit: When this value is set, each SQL statement executed, if not appendedlimit, Mycat will automatically add the corresponding value; If this parameter is not specified, all queried information is displayed by default.
  • dataNode: This attribute is used to bind the logical library to a specific database. If you define this attribute, the logical library will not work in the database and table mode (no logical table information can be configured inside the schema tag), that is, all operations on the logical library will be directly applied to the bound dataNode. This schema can be used for read/write separation and master/slave switchover.

Table label

After you use the SCHEMA tag to define the logical library, you also need to use the Table tag to define the logical tables. Example:

<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100"> 
    <! -- Multiple table definitions --> 
    <table name="travelrecord,address" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" splitTableNames ="true"/> 
    <! -- single table definition --> 
    <table name="oc_call" primaryKey="id" dataNode="dn1$0-743" rule="latest-month-calldate"/> 
</schema>
Copy the code
  • nameProperty defines the name of the logical table, which must be unique and must be the same as the physical table name in the database. Configure multiple tables with comma split, that is, multiple tables use this configuration
  • primaryKeyProperty specifies the primary key of the logical table, which also needs to be the same as the primary key of the physical table
  • dataNodeProperty specifies the name of the data node where the physical table resides. When configuring multiple data nodes, use commas (,) to separate them, or specify an index range:dn1$0-743. Notice That the order of data nodes cannot be changed after they are defined. Otherwise, data will be confused
  • ruleAttribute to specify the sharding rule name, corresponding torule.xmlIn the<tableRule>Of the labelnameProperty, if sharding is not required
  • splitTableNamesProperty defines whether multiple table definitions are allowed

The dataNode label

The dataNode tag is used to define the data nodes that point to the physical database where the logical tables are stored. Example:

<dataNode name="dn1" dataHost="host1" database="test" />
Copy the code

Datanodes are commonly referred to as data fragments. A dataNode is an independent data fragment. Use database instance + concrete library to define a data shard.

  • name: Defines the data node name, which is unique
  • dataHost: defines which database instance the shard belongs to. The attribute value is referenced indataHostOn the labelnameattribute
  • database: defines which specific library on which specific database instance the shard belongs to,

DataHost label

The dataHost tag is used to define back-end physical database host information. The tag has two sub-tags that define a set of database host information. For example, to define a set of database hosts in a master-slave cluster structure:

[Photo from the Internet]

<dataHost name="host1" 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.96.187:3306" user="root" password="123456">
        <readHost host="hostS1" url="192.168.96.187:3306" user="root" password="123456" />
    </writeHost>
</dataHost>
Copy the code

The dataHost tag defines the specific database instance, read/write separation configuration, and heartbeat statements.

  • name: defines the dataHost unique identifier, anddataNodethedataHostAttribute consistent
  • maxCon: specifies the maximum number of connections per read/write instance connection poolwriteHost.readHostThe tag uses this value to instantiate the maximum number of connections in the pool
  • minCon: specifies the minimum connection for each read/write instance connection pool, initializing the pool size
  • balance: Indicates the load balancing type. 0: all read operations are sent to the currently availablewriteHostOn; 1: All read operations are randomly sent toreadHostOn; 2: All read operations are performed randomlywriteHostandreadHostThe distribution of
  • writeType: Indicates the load balancing type. 0: all write operations are sent to the currently availablewriteHostOn; 1: All write operations are randomly sent toreadHostOn; 2: All write operations are performed randomlywriteHostandreadHostThe distribution of
  • dbType: Specifies the type of database to connect to at the back end. The binary Mysql protocol is currently supported, among other usesJDBCConnected database, for example:mongodb.oracle.sparkEtc.
  • dbDriver: Specifies the Driver used to connect to the back-end databasenativeandJDBC; usenativeThe binary mysql protocol is executed, so mysql and Maridb can be used; Other types of databases are requiredjdbcDrive to support
  • switchType: When the MySQL server is down, change the primary/secondary relationship type, for example, configure one MySQL servermaster, aslaveIf the primary node is down, -1: the secondary node does not automatically switch to the primary node. 1: (Default value) Automatic switchover: the secondary node is switched to the primary node. 2: The switchover is determined based on the primary/secondary synchronization status of MySQL. The heartbeat detection statement is as follows:show slave status3: Switch mechanism based on MySQL Galary Cluster (suitable for cluster), heartbeat detection statement isshow status like 'wsrep%'
  • slaveThreshold: Defines the primary/secondary replication delay threshold, whenSeconds_Behind_Master > slaveThresholdThe read/write split filter filters out the Slave machine to prevent old data from being read long ago

The heartbeat tags

<heartbeat>select user()</heartbeat>
Copy the code

The heartbeat TAB specifies the statements that are used to perform heartbeat checks with the back-end database. For example, MySQL can use select user(), Oracle can use select 1 from dual, etc.

WriteHost & readHost tags

Both tags are used to configure a set of information about the master and slave databases, and Mycat instantiates the back-end connection pool with the connection information configured by these two tags. The only difference is that writeHost is configured as the write instance (master) and readHost as the read instance (Salve), and readHost is the subtag of writeHost. These two tags allow you to combine read/write instances to meet system requirements.

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, when a writeHost goes down, the system automatically detects it and switches to a standby writeHost.

Writehost tag and readHost tag attributes

These two tags have the same attributes, so here are some of them:

  • hostProperty is used to identify different instance names, generallywriteHostThe name to useM1As a suffix,readHostUse theS1As a suffix
  • urlProperty is used to configure the database connection address, if usednativethedbDriver, is generallyaddress:portIn this form. withJDBCOr otherdbDriver, you need to specify this parameter. For example, when usingJDBCYou can write:jdbc:mysql://localhost:3306/
  • userProperty configures the database user name
  • passwordProperty Configures the database password
  • weightProperty to configure a database inreadhostIs the weight of the read node
  • usingDecryptProperty specifies whether the password is encrypted. The default is0To enable the function, set it to1

summary

This article provides a detailed description of Mycat’s two important configuration files, server.xml and schema.xml. If Mycat is interested, check out the rest of this column.