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

The last time we introduced the concept of Mycat, we talked about the sharding table. Let's start with configuring a sharding table and briefly describe the configuration of Mycat.

Schema.xml is the first configuration file encountered, which manages the logic library, tables, sharding rules, sharding nodes and node hosts of Mycat. Such content is presented in the corresponding tags, and the configuration process is the setting of these tags.

The logical library setting, that is, the schema tag, specifies the library name testdb.

...

Table settings, that is, table tag, specify table name tb1, shard node, and sharding rules. The table tag should be included in the schema tag.

The setting of the sharding node, that is, the dataNode tag, specifies the name of the shard node, the host of the node, such as the shard node dnTest1 is located on the node host Rep1_3306, and the name of the corresponding physical library, such as the physical library test1 corresponding to the sharding node dnTest1 (which really exists on the backend database instance).

The setting of the node host, that is, the dataHost tag, specifies the node host name, the corresponding backend database instance information, read-write separation configuration, heartbeat statement, etc., which will be further explained in the high availability section of Mycat.

Select user ()

Select user ()

When setting the dataHost tag, the backend MySQL instance should create a good zzzz user.

Mysql > create user 'zzzz'@'192.168.4.%' identified by' zzzz'

Mysql > grant select, insert, update, delete, execute, alter, create on test1.* to 'zzzz'@'192.168.4.%'

Since tb1 is a sharding table, it is also necessary to explain the sharding field and slicing algorithm, and configure the rule.xml file.

The sharding field, which is the primary key id field of the tb1 table.

Id mod-long

Slicing algorithm, simple touch.

two

Now that the tb1 table is configured, to access the table through Mycat, you need a simple permission to configure the server.xml file, which also includes the system configuration and tuning parameters of Mycat.

Test_usertestdb

Now you can log in to Mycat and create the table structure of tb1. This step can also be done directly to the backend instance. If there are multiple nodes, it will be more troublesome, so zzzz users are given create permissions and created directly through Mycat.

Log in to mycat

$mysql-h292.168.4.184-P8066-utest_user-ptest_user

Mysql > create table tb1 (id int auto_increment primary key, user_name varchar (30) not null default'')

At this point, you can use the tb1 table, insert a piece of data, and look at the routing process through the log. In order to see a more detailed log, first adjust the log level to debug and configure the log4j.xml file.

Insert data

Mysql > insert into tb1 select null, 'abc'

ERROR 1064 (HY000): partition table, insert must provide ColumnList

Mysql > insert into tb1 (id, user_name) values (null, 'abc')

ERROR 1064 (HY000): For input string: "NULL"

Mysql > insert into tb1 (id, user_name) values (7, 'abc')

Query OK, 1 row affected (0.00 sec)

Observe the log and you can see the insert on the route to dnTest2 shard node.

03According 18 08 ServerQueryHandler.java:56 33 ServerQueryHandler.java:56 DEBUG [$_ NIOREACTOR-2-RW] (ServerQueryHandler.java:56)-ServerConnection [id=2, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=true, schema=testdb] insert into tb1 (id, user_name) values (7, 'abc')

03racer 18 08 NonBlockingSession.java:113 33 DEBUG [$_ NIOREACTOR-2-RW] (NonBlockingSession.java:113)-ServerConnection [id=2, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=true, schema=testdb] insert into tb1 (id, user_name) values (7, 'abc'), route= {

1-> dnTest2 {insert into tb1 (id, user_name) values (7, 'abc')}

} rrs

03According 18 08 con need syn 33 MySQLConnection.java:459 04.222 DEBUG [$_ NIOREACTOR-2-RW] (MySQLConnection.java:459)-con need syn, total syn cmd 2 commands SET names utf8;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ Schema change:false con:MySQLConnection [id=5, lastTime=1521333184222, user=zzzz, schema=test2, old shema=test2, borrowed=true, fromSlaveDB=false, threadId=68, charset=utf8, txIsolation=0, autocommit=true, attachment=dnTest2 {insert into tb1 (id, user_name) values (7, 'abc')}, respHandler=SingleNodeHandler [node=dnTest2 {insert into tb1 (id, user_name) values (7,' abc')}, packetId=0], host=192.168.4.151, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

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.