How to realize Global self-increment of Primary key in distributed Database MyCat

It is believed that many inexperienced people don't know what to do about how to realize the global self-increment of primary key in distributed database MyCat. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Today, let's talk about how to realize the global self-increment of primary keys.

problem

Primary key self-increment should be a very common requirement. In a stand-alone database, this requirement can be realized by an auto_increment, but in a database cluster, this requirement becomes more complex, because there are multiple database instances, each of which is a primary key self-increment, so it is not a primary key self-increment together.

The simplest way of thinking

The easiest way to deal with this problem is to set the step size and starting offset of the primary key. By default, the primary key self-increment step is 1, and if we have three database instances, we can set the primary key self-increment step to 3, so that for the first database instance, the primary key self-increment is 1, 4, 7, 10. For the second database instance, the primary key self-increment is 2, 5, 8, 11. For the third database instance, The self-increasing primary key is 3, 6, 9, 12.

MSSQL can specify the self-increment step and starting offset of the primary key directly in SQL, but MySQL needs to modify the database configuration to achieve it, so this method is not recommended here.

MyCat's method

MyCat, as a distributed database intermediary, shields the operation of database clusters. Let's operate database clusters just like operating stand-alone databases. It has its own scheme for self-increasing primary keys:

Implemented through local files

Realized through database

Implemented through local timestamps

Implemented through a distributed ZK ID generator

It is realized by increasing ZK.

Today, let's take a look at how to achieve global self-increment of primary keys through ZK increment.

The configuration steps are as follows:

First, modify the self-increment mode of the primary key to 4. 4 means to use zookeeper to achieve the self-increment of the primary key.

Server.xml

Configure the table to augment and set the primary key

Schema.xml

Set the primary key to augment itself, and set the primary key to id.

Information about configuring zookeeper

Configure zookeeper information in myid.properties:

Configure tables to be self-incremented

Sequence_conf.properties

Note that the name of the table should be capitalized here.

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

The MAXID and MINID of the file configuration decide to get the interval each time, which is valid for each thread or process.

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

Restart the MyCat test

Finally, restart MyCat, delete the previously created table, and then create a new table for testing.

After reading the above, have you mastered how to realize the global self-increment of primary keys in MyCat, a distributed database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!