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

02 configuration section said that the tb1 table is sliced according to the primary key id, and in practical work, it is often sliced according to the business field. This time, there is a tb3 table, which is sliced according to user_id. According to the above idea, adjust the configuration files involved first.

1. In schema.xml, add the configuration of the tb3 table.

two。 In rule.xml, add tb3 slicing configuration, in which the slicing algorithm is still simple to touch.

User_id

Mod-long

Connect Mycat to create the table structure of tb3.

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

Insert 2 pieces of data below.

Mysql > insert into tb3 (id, user_id, user_name) values (7,1, 'abcd')

Mysql > insert into tb3 (id, user_id, user_name) values (7,2, 'efgh')

It is found that although the id field is the primary key, it has lost its original uniqueness constraint in the case of fragmentation. The reason is simple: unique primary keys on multiple MySQL instances can naturally have the same value.

Mysql > select * from tb3

+-- +

| | id | user_id | user_name | |

+-- +

| | 7 | 2 | efgh |

| | 7 | 1 | abcd |

+-- +

In view of the above situation, the global serial number (sequence) is used in Mycat to reshape the global uniqueness of the primary key, including local configuration and database configuration.

Let's use the configuration database to get sequence, first understand how it works.

Create a table in the database that holds the sequence name (name column), current value (current_value), and step size (increment, how many sequence the table reads at a time).

Acquisition and maintenance of sequence:

1)。 When using the sequence for the first time, according to the passed sequence name, read current_value from the table in the database, and increment to Mycat, and set the current_value in the database to the original current_ value + increment value.

2)。 Mycat will read the current_ value + increment value as the sequence value to be used this time, and automatically add 1 the next time it is used. After using increment for several times, perform the same operation in step 1).

3)。 Mycat is responsible for maintaining the table, which sequence is used, just insert a record in the table. If the read sequence is not used up, the Mycat is stopped, and the remaining sequence values read this time will no longer be used.

Configuration method:

1. In server.xml, turn on the switch to generate sequence using the database mode.

one

two。 Adjust schema.xml and create a mycatseq database (arbitrary name), MYCAT_ search table (table name should be capitalized), and 3 functions on a MySQL instance on the Mycat backend.

2.1 add the following configuration to schema.xml.

2.2 the login node host Rep1_3306 creates the corresponding database and table.

Mysql > create database mycatseq

Mysql > use mycatseq

Mysql > create table MYCAT_SEQUENCE (name varchar (50) not null, current_value int not null, increment int not null default 100, primary key (name))

2.3 and create 3 functions.

# mycat_seq_currval

DELIMITER / /

CREATE DEFINER= `zzzz` @ `192.168.4% `FUNCTION `currval` (seq_name VARCHAR (50)) RETURNS varchar (64) CHARSET utf8 COLLATE utf8_bin

DETERMINISTIC

BEGIN

DECLARE retval VARCHAR (64)

SET retval= "- 99999999999"

SELECT concat (CAST (current_value AS CHAR), ",", CAST (increment AS CHAR)) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name

RETURN retval

END; / /

DELIMITER

# mycat_seq_nextval

DELIMITER / /

CREATE DEFINER= `zzzz` @ `192.168.4% `FUNCTION `mycat_seq_ nextval` (seq_name VARCHAR (50)) RETURNS varchar (64) CHARSET utf8 COLLATE utf8_bin

DETERMINISTIC

BEGIN

UPDATE MYCAT_SEQUENCE

SET current_value = current_value + increment WHERE name = seq_name

RETURN mycat_seq_currval (seq_name)

END; / /

DELIMITER

# mycat_seq_setval

DELIMITER / /

CREATE DEFINER= `zzzz` @ `192.168.4% `FUNCTION `mycat_seq_ setval` (seq_name VARCHAR (50), value INTEGER) RETURNS varchar (64) CHARSET utf8 COLLATE utf8_bin

DETERMINISTIC

BEGIN

UPDATE MYCAT_SEQUENCE

SET current_value = value

WHERE name = seq_name

RETURN mycat_seq_currval (seq_name)

END; / /

DELIMITER

At this point, the preparation for the use of Mycat sequence is ready, who will use, that is, tb3, how to use it, see the following steps.

1. To slightly modify the configuration of tb3 in schema.xml, add autoIncrement= "true", tell Mycat tb3 to use sequence; to add primaryKey= "id", and tell Mycat what the primary key field is.

two。 How does Mycat know which node host to get sequence? it needs to indicate the corresponding relationship between table name (uppercase) and shard node in a new configuration file sequence_db_conf.properties.

# testdb

TB3=gseq

3. Log in to the node host Rep1_3306 and initialize the sequence of the tb3 table.

Mysql > insert into MYCAT_SEQUENCE (name, current_value, increment) values ('TB3', 400,100)

Mysql > select * from MYCAT_SEQUENCE

+-+

| | name | current_value | increment | |

+-+

| | TB3 | 400 | 100 | |

+-+

Log in to Mycat to verify that sequence can be used properly.

Mysql > select next value for MYCATSEQ_TB3

+-+

| | 500 |

+-+

| | 500 |

+-+

At this point, the tb3 table is ready to use sequence, so let's insert the data.

Mysql > insert into tb3 (user_name) values ('igkl')

ERROR 1064 (HY000): bad insert sql (sharding column:USER_ID not provided,INSERT INTO tb3 (ID, user_name)

VALUES (501, 'igkl')

Mysql > insert into tb3 (id, user_id, user_name) values (9,4, 'igkl')

Since Mycat is responsible for the generation of the primary key value id, the id field can be omitted from the SQL statement (if you specify id as a value as above, there is no problem).

Mysql > insert into tb3 (user_id, user_name) values (59, 'mnop')

Mysql > select * from tb3 where user_id = 59

+-+

| | id | user_id | user_name | |

+-+

| | 502 | 59 | mnop |

+-+

Check the log and find that Mycat has rewritten the original SQL statement and added the id field.

03According 18 20 ServerConnection 46 ServerQueryHandler.java:56 36.798 DEBUG [$_ NIOREACTOR-1-RW] (ServerQueryHandler.java:56)-ServerConnection [id=1, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=true, schema=testdb] insert into tb3 (user_id, user_name) values (59, 'mnop')

03According to 18 20 ServerConnection 46 DEBUG 36.800 DEBUG [Thread-1] (NonBlockingSession.java:113)-ServerConnection [id=1, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=true, schema=testdb] insert into tb3 (ID,user_id, user_name) values (502, 59, 'mnop'), route= {

1-> dnTest2 {insert into tb3 (ID,user_id, user_name) values (502 user_name 59, 'mnop')}

03According 18 20 con need syn 46 MySQLConnection.java:459 36.800 DEBUG [Thread-1] (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=2, lastTime=1521377196800, user=zzzz, schema=test2, old shema=test2, borrowed=true, fromSlaveDB=false, threadId=81, charset=utf8, txIsolation=0, autocommit=true, attachment=dnTest2 {insert into tb3 (ID,user_id, user_name) values (502 mnop')}, respHandler=SingleNodeHandler [node=dnTest2 {insert into tb3 (ID,user_id, user_name) values (502 node=dnTest2 59, 'mnop')}, packetId=0], host=192.168.4.151, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

03According to 18 20 NonBlockingSession.java:229 46 DEBUG 36.802 DEBUG [$_ NIOREACTOR-2-RW] (NonBlockingSession.java:229)-release connection MySQLConnection [id=2, lastTime=1521377196792, user=zzzz, schema=test2, old shema=test2, borrowed=true, fromSlaveDB=false, threadId=81, charset=utf8, txIsolation=3, autocommit=true, attachment=dnTest2 {insert into tb3 (ID,user_id, user_name) values (502 values 59, 'mnop')}, respHandler=SingleNodeHandler [node=dnTest2 {insert into tb3 (ID,user_id, user_name) values (502 art 59,' mnop')}, packetId=1], host=192.168.4.151, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

If you are interested, please follow Subscription account's Database Best practices (DBBestPractice).

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.