Mycat global series and mycat global series

Mycat global series and mycat global series

Overview

This article introduces how mycat can ensure the global unique primary key method in the case of database/table sharding. Next we will analyze the advantages and disadvantages of the three methods.

 

Configuration

File Retrieval

1. Modify the server configuration file

Vim server. xml

<System> <property name = "sequnceHandlerType"> 0 </property> </system> Note: If sequnceHandlerType is set to 0, local files are read.

 

2. Configure the sequence_conf.properties configuration file

3. Run the statement test in mycat (test in the logical Library)

insert into company(id,name) values(next value for MYCATSEQ_GLOBAL,'test');

Disadvantages: After MyCAT is restarted, the sequence in the configuration file is restored to the initial value.
Advantages: Local loading, fast reading speed.

 

Database Retrieval

1. Modify the server configuration file

Vim server. xml

<System> <property name = "sequnceHandlerType"> 1 </property> </system> Note: If sequnceHandlerType is set to 1, it indicates reading from the database table.

2. Configure the read node, that is, to configure the node on which the global table is located. Here I configure it on the dn1 node.

Vim sequence_db_conf.properties

3. Create Global tables and functions

Select the dn1 node of the mysql physical database and execute the following statement. The configured dn1 is the db1 database.

DROP TABLE IF EXISTS MYCAT_SEQUENCE;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 INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('GLOBAL', 100000, 100);DROP FUNCTION IF EXISTS `mycat_seq_currval`;DELIMITER ;;CREATE  FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8    DETERMINISTICBEGIN         DECLARE retval VARCHAR(64);        SET retval="-999999999,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 ;DROP FUNCTION IF EXISTS `mycat_seq_nextval`;DELIMITER ;;CREATE FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8    DETERMINISTICBEGIN          UPDATE MYCAT_SEQUENCE                   SET current_value = current_value + increment                   WHERE name = seq_name;           RETURN mycat_seq_currval(seq_name);  END;;DELIMITER ;DROP FUNCTION IF EXISTS `mycat_seq_setval`;DELIMITER ;;CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER) RETURNS varchar(64) CHARSET utf8    DETERMINISTICBEGIN          UPDATE MYCAT_SEQUENCE                     SET current_value = value                     WHERE name = seq_name;           RETURN mycat_seq_currval(seq_name);  END;;DELIMITER ;

4. Test Data insertion, also executed on the mycat logical database

Insert into user (id, name) values (next value for MYCATSEQ_GLOBAL, 'Chinese ');

Advantages: After MyCAT is restarted, the sequence value will not be initialized. For example, the initial value of MYCAT_SEQUENCE is 100000. After mycat is restarted and inserted, the initial value of MYCAT_SEQUENCE will be increased by 100 to 100100, the value newly inserted to the table also increases progressively by 100,

.

Disadvantages: When master-slave replication is configured (A master-B slave), the initial value of current_value IN THE MYCAT_SEQUENCE table on the master-slave table is 100000; the current read/write splitting scheme is that A is responsible for writing B is responsible for reading. When A is down, B is responsible for writing A to read, and now the global ID has reached 100103, if A goes down at this time,

Mycat should cache current_value or record the value of A, and will not immediately switch to the value of current_value on B. If mycat restarts at this time, at this time, the global series starts to use the current_value value in B. Because the value of B also starts from 100100, then inserting the global record into the table in B will conflict with the previously generated 100100-100103 primary key.. I don't know if this is A BUG, so when A goes down, you need to restart A quickly. In this way, even if mycat restarts, it will start reading from, at this time, the global ID starts from 100200.

Another method is to increase the current_value of B by 100 because mycat has not restarted the ID of the global sequence or cached A after A goes down, in this case, if A is switched to A, because the value of B is from 100100, it will not conflict with the previous one, if A is fixed and started at this time, the current_value of a should be increased by 200 to avoid conflicts with the current value after switching to.

 

Obtain the local Timestamp

1. Modify the server configuration file

Vim server. xml

<System> <property name = "sequnceHandlerType"> 2 </property> </system> Note: sequnceHandlerType is configured as 2 to indicate the timestamp mode.

 

2. Test the inserted data, which is also executed on the mycat logical database. Note that the timestamp length is 18 bits to ensure that the table's field length is sufficient.

Insert into user (id, name) values (next value for MYCATSEQ_GLOBAL, 'Chinese ');

Advantages:The above two schemes do not exist because the restart of mycat causes duplicate IDs.

Disadvantages:The data type is too long.

Summary

Each of the three methods has its own advantages and disadvantages.

 

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion