Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to achieve a global serial number in MyCat. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. Local file mode

Principle:

In this way, MyCAT configures sequence to a file, and when the configuration in sequence is used, MyCAT will change the current value of sequence in the sequence_conf.properties file in classpath.

Configuration method:

Make the following configuration in the sequence_conf.properties file:

GLOBAL_SEQGLOBAL_SEQ.HISIDS= GLOBAL_SEQ.MINID=1001 GLOBAL_SEQ.MAXID=1000000000 GLOBAL_SEQ.CURID=1000

Where HISIDS represents the used history segment (generally, no special needs can not be configured), MINID represents the minimum ID value, MAXID represents the maximum ID value, and CURID represents the current ID value.

Configuration in server.xml:

0

Note: sequnceHandlerType needs to be configured to 0, which means local file mode is used.

Examples of use:

Insert into table1 (id,name) values (next value for MYCATSEQ_GLOBAL,'test')

Cons: when MyCAT is rereleased, the sequence in the configuration file will revert to its original value.

Advantages: local loading, fast reading speed.

two。 Database mode

Principle:

Set up a table in the database to store information such as sequence name (name), current value of sequence (current_value), step size (how many sequence are read by incrementint type at a time, assuming K), etc.

Steps to obtain Sequence:

When using the sequence for the first time, read current_value from the table in the database, and increment to MyCat based on the passed-in sequence name, and set the current_value in the database to the original current_value value + increment value.

MyCat will read current_value+increment 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).

MyCat is responsible for maintaining the table, and all you need to do is insert a record into the table for which sequence is used. If the sequence read in one time is not used up, the system stops, and the remaining sequence value read this time will no longer be used.

Configuration method (server.xml configuration):

one

Note: sequnceHandlerType needs to be configured as 1, which means that sequence is generated in a database manner.

Configuration method (database configuration):

1) create MYCAT_SEQUENCE table

-create a table to store sequence DROP TABLE IF EXISTS MYCAT_SEQUENCE;-name sequence name-current_value the current value-increment growth step! It can be understood as how many sequence mycat reads in the database at a time. When these are used up, read them from the database next time. CREATE TABLE MYCAT_SEQUENCE (name VARCHAR (50) NOT NULL,current_value INT NOT NULL, increment INT NOT NULL DEFAULT 100, PRIMARY KEY (name)) ENGINE=InnoDB;-insert a sequence INSERT INTO MYCAT_SEQUENCE (name,current_value,increment) VALUES ('GLOBAL', 100000100)

2) create related function

-get the value of the current sequence (return current value, increment) DROP FUNCTION IF EXISTS mycat_seq_currval; DELIMITER CREATE FUNCTION mycat_seq_currval (seq_name VARCHAR (50)) RETURNS varchar (64) CHARSET utf-8 DETERMINISTIC BEGIN DECLARE retval VARCHAR (64); SET retval= "- 999999999 INTO retval FROM MYCAT_SEQUENCE WHERE name null"; SELECT concat (CAST (current_value AS CHAR), ",", CAST (increment AS CHAR)) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name; RETURN retval; END DELIMITER -set sequence value DROP FUNCTION IF EXISTS mycat_seq_setval; DELIMITER CREATE FUNCTION mycat_seq_setval (seq_name VARCHAR (50), value INTEGER) RETURNS varchar (64) CHARSET utf-8 DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_value = value WHERE name = seq_name; RETURN mycat_seq_currval (seq_name); END DELIMITER;-get the next sequence value DROP FUNCTION IF EXISTS mycat_seq_nextval DELIMITER CREATE FUNCTION mycat_seq_nextval (seq_name VARCHAR (50)) RETURNS varchar (64) CHARSET utf-8 DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_valuecurrent_value = current_value + increment WHERE name = seq_name; RETURN mycat_seq_currval (seq_name); END DELIMITER

3) sequence_db_conf.properties-related configuration, which specifies the node on which sequence-related configuration is located:

For example:

USER_SEQ=test_dn1

Note: the MYCAT_SEQUENCE table and the above three function need to be on the same node. Function should be executed directly on the database of a specific node. If you do so, report:

You might want to use the less safe log_bin_trust_function_creators variable

The following settings need to be made to the database:

My.ini [mysqld] under windows plus my.ini [mysqld] plus log_bin_trust_function_creators=1 under log_bin_trust_function_creators=1 linux / etc/my.cnf

After the modification, you can execute the above function in the mysql database.

Examples of use:

Insert into table1 (id,name) values (next value for MYCATSEQ_GLOBAL,'test')

3. Local timestamp mode

ID= 64-bit binary (42 (milliseconds) + 5 (machine ID) + 5 (business code) + 12 (repeat accumulation)

Converted to decimal to 18-bit long type, can be concurrently 12-bit binary accumulation per millisecond.

Mode of use:

a. Configure server.xml

two

b. Configure under mycat: sequence_time_conf.properties

WORKID=0-31 any integer DATAACENTERID=0-31 any integer

The WORKID,DATAACENTERID configured for each mycat under multiple mycat nodes is different, forming a unique identity. A total of 32 / 32 / 1024 combinations are supported.

4. Distributed ZK ID generator

three

The connection information for Zk is configured uniformly in the zkURL property of myid.properties.

Distributed ID generator based on ZK and local configuration (you can obtain the unique InstanceID of the cluster (computer room) through ZK, or you can configure InstanceID through configuration files)

ID structure: long 64-bit, ID can occupy up to 63 bits * | current time millis (38-bit microsecond timestamp, can be used for 17 years) | clusterId (server room or ZKid, 5-bit configuration file) | instanceId (instance ID, which can be obtained through ZK or configuration file, 5-bit) | threadId (thread ID,9 bit) | increment (self-increment, 6-bit) * a total of 63 bits, which can withstand the concurrency of 1000 * (2 ^ 6) = 640000 for single machine in single machine room. * No sad lock, no strong competition, higher throughput

Configuration file: sequence_distributed_conf.properties, as long as the configuration: INSTANCEID=ZK is to get InstanceID from the ZK.

5. Zk increment mode

four

The connection information of Zk is configured uniformly in the zkURL attribute of myid.properties. 4 is the increment sequence number of zookeeper implementation.

1) configuration file: sequence_conf.properties, as long as the following attributes of ZK address and table name are configured

The minimum value in the current interval of a thread in TABLE.MINID

The maximum value in the current interval of a thread in TABLE.MAXID

The current value in the current interval of a thread in TABLE.CURID

2) the MAXID and MINID configured in the file decide to get the interval each time, which is valid for each thread or process.

3) the configuration of these three attributes in the file is only valid for the first thread of the first process, and other threads and processes will read ZK dynamically.

6. Self-growing primary key

(1) implementation of MyCAT self-growing primary key and returning to generate primary key ID

Description:

Mysql itself for non-self-growing primary key, using last_insert_id () will not return the result, only 0

Mysql only defines a self-growing primary key, and you can return the primary key value with last_insert_id ()

MyCAT currently provides self-growing primary key functionality, but if there is no auto_increment defined in the data table on the corresponding mysql node, then calling last_insert_id () on the MyCAT layer will not return a result.

The correct configuration is as follows:

Mysql definition self-incrementing primary key

CREATE TABLE table1 ('id_' INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,' name_' INT (10) UNSIGNED NOT NULL, PRIMARY KEY ('id_')) ENGINE=MYISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Mycat defines primary key increment

Mycat adds corresponding settings to sequence_db_conf.properties.

TABLE1=dn1

Add the sequence record of TABLE1 table to mycat_sequence table in database

The test uses:

127.0.0.1/root: [TESTDB > insert into tt2 (name_) values ('T1'); Query OK, 1 row affected (0.14 sec) 127.0.0.1/root: [TESTDB > select last_insert_id () +-+ | LAST_INSERT_ID () | +-+ | 100 | +-+ 1 row in set (0.01 sec) 127.0.0.1/root: [TESTDB > insert into tt2 (name_) values ('t2'); Query OK, 1 row affected (0.00 sec) 127.0.0.1/root: [TESTDB > select last_insert_id () +-+ | LAST_INSERT_ID () | +-+ | 101 | +-+ 1 row in set (0.00 sec) 127.0.0.1/root: [TESTDB > insert into tt2 (name_) values ('t3'); Query OK, 1 row affected (0.00 sec) 127.0.0.1/root: [TESTDB > select last_insert_id () +-+ | LAST_INSERT_ID () | +-+ | 102 | +-+ 1 row in set (0.00 sec)

Example of getting last_insert_id after a new record is added in Myibatis:

This is how to achieve a global serial number in the MyCat shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.