MyCAT global serial number

MyCAT global serial number

When database/table sharding is implemented, the auto-increment primary key of the database cannot be globally unique. Therefore, MyCat provides global sequence and multiple implementation methods including local configuration and database configuration.

Local file Mode

Principle: in this way, MyCAT configures the sequence to the file. When the sequence configuration is used, MyCAT changes the current sequence Value in the sequence_conf.properties file in the conf file.

For example:

# Default global sequence
GLOBAL. HISIDS =
GLOBAL. MINID = 10001
GLOBAL. MAXID = 20000
GLOBAL. CURID = 10000

HISIDS indicates the historical segments used (unless otherwise required), MINID indicates the minimum ID, MAXID indicates the maximum ID, and CURID indicates the current ID.

Usage:

Configure server. xml as follows:

<System> <property name = "sequnceHandlerType"> 0 </property> </system>

0 indicates that local files are used.

The test is as follows:

 

Mysql> create table test (id int, name varchar (20 ));
Query OK, 0 rows affected (0.13 sec)

Mysql> insert into test (id, name) values (next value for MYCATSEQ_GLOBAL, @ hostname );
Query OK, 1 row affected, 1 warning (0.03 sec)

Mysql> select * from test;
+ ------- + -------------- +
| Id | name |
+ ------- + -------------- +
| 10001 | mysql-slave2 |
+ ------- + -------------- +
1 row in set (0.02 sec)

 

In this case, the GLOBAL. CURID value in sequence_conf.properties is 10001. Of course, you can use any rules defined in sequence_conf.properties, such:

# Self define sequence
COMPANY. HISIDS =
COMPANY. MINID = 1001
COMPANY. MAXID = 2000
COMPANY. CURID = 1000

 

Mysql> insert into test (id, name) values (next value for MYCATSEQ_COMPANY, @ hostname );
Query OK, 1 row affected, 1 warning (0.02 sec)

Mysql> select * from test;
+ ------- + -------------- +
| Id | name |
+ ------- + -------------- +
| 10001 | mysql-slave1 |
| 1001 | mysql-slave1 |
+ ------- + -------------- +
2 rows in set (0.10 sec)

 

This can be customized.

 

Database Mode

Principle

Create a table in the database and store information such as the sequence name (name), current sequence Value (current_value), and step size (K;

Steps for obtaining Sequence:

1 ). when this sequence is used for the first time, current_value and increment are read from the database table according to the input sequence name to MyCat, set current_value in the database to the original current_value value + increment value (based on subsequent storage functions)

2). MyCat reads current_value + increment as the sequence value to be used this time. In the next use, 1 is automatically added. After the increment is used, 1 is executed.) the same operation is performed.
MyCat is responsible for maintaining this table. To use sequence, you only need to insert a record in this table. If a read sequence is not used up, the system stops, and the remaining value of the read sequence is no longer used.

Usage:

1. Configure the server. xml file

<System> <property name = "sequnceHandlerType"> 1 </property> </system>

1 indicates using the database to generate sequence

2. Configure the database

Create tables and stored procedures in the database corresponding to one of the shard points

Because the datanode configuration information in schem. xml is as follows:

<DataNode name = "dn $1-3" dataHost = "localhost1" database = "db $1-3"/>

For example, when I create a database in dn2, the corresponding database name is db2 (why datanode is involved here, because the sequence_db_conf.properties file will be used later). Note that it is created by logging on to the database, instead of creating

1> Create a MYCAT_SEQUENCE table

Mysql> drop table if exists MYCAT_SEQUENCE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql> 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;
Query OK, 0 rows affected (0.07 sec)

Where:-name sequence name
-Current_value: Current value
-Increment growth step! It can be understood as the number of sequence mycat reads in the database at a time. When these are used up, it will be read from the database again next time.

Note: MYCAT_SEQUENCE must be capitalized.

2> insert a sequence record

Mysql> insert into MYCAT_SEQUENCE (name, current_value, increment) VALUES ('mycat', 1,100 );
Query OK, 1 row affected (0.01 sec)

Insert a sequence named mycat. The current value is 1 and the step size is 100.

3> Create a storage function.

Note: It must be created in the same database. In this example, It is db2. Create three.

-Get the value of the current sequence (return the current value, incremental)

 

Drop function if exists mycat_seq_currval;
DELIMITER $
Create function mycat_seq_currval (seq_name VARCHAR (50) RETURNS varchar (64) CHARSET utf8
DETERMINISTIC
BEGIN
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;

 

-Set the 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 utf8
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 utf8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval (seq_name );
END $
DELIMITER;

 

So far, the database preparation has been completed.

3. Add the following content to the sequence_db_conf.properties file under the mycat conf directory:

MYCAT = dn2

Among them, dn2 corresponds to the datanode of the database db2

Note: The value of MYCAT must be in upper case, which is irrelevant to whether the table data is in upper case. In fact, the value of name in MYCAT_SEQUENCE does not affect the result.

Start test:

Log on to the mycat Client

# Mysql-h127.0.0.1-utest-ptest-P8066-DTESTDB

Create test table

Mysql> create table test (id int, name varchar (10 ));
Query OK, 0 rows affected (0.22 sec)

Insert data

The insert format is as follows:

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

 

Mysql> insert into test (id, name) values (next value for MYCATSEQ_MYCAT, 'test1 ');
ERROR 1003 (HY000): mycat sequnce err.org. opencloudb. config. util. ConfigException: can't find definition for sequence: MYCAT
Mysql> insert into test (id, name) values (next value for MYCATSEQ_MYCAT, 'test1 ');
Query OK, 1 row affected (0.11 sec)

Mysql> select * from test;
+ ------ + ------- +
| Id | name |
+ ------ + ------- +
| 101 | test1 |
+ ------ + ------- +
1 row in set (0.09 sec)

 

The following error is reported for the first time:

ERROR 1003 (HY000): mycat sequnce err.org. opencloudb. config. util. ConfigException: can't find definition for sequence: MYCAT

Because the current mycat modification to sequence_db_conf.properties is not known, you can restart mycat or log on to the 9066 Management port for reload @ config;

Now, the test is complete. The key points are: MYCAT_SEQUENCE must be capitalized, and MYCAT = dn2 must be capitalized in the sequence_db_conf.properties file.

MySQL read/write splitting using MyCAT

MySQL read/write splitting using MyCAT

Verify MyCAT ER sharding

This article permanently updates the link address: