How to generate a unique server-id in MySQL

Preface

We all know that MySQL uses server-id to uniquely identify a database instance and uses it in a chained or dual-master replication structure to avoid infinite loops of SQL statements. This article shares my understanding of server-id, and then compares and weighs several ways to generate a unique server-id.

Purpose of server_id

In short, server_id has two purposes:

1. Used to mark the origin of the binlog event, that is, where the SQL statement originated from.

2. Used for IO_thread to filter the main library binlog. If replicate-same-server-id=1 is not set, then when the slave's io_thread finds that the source of an event is the same as its own server-id, it will skip the event and not write it to the relay log. The sql_thread of the slave library will naturally not execute the event. This can avoid infinite loops of SQL statements in a chained or dual-main structure.

Note: Events with the same server-id are filtered at the io_thread level; while rules such as replicate-(do|ignore)- are filtered at the sql_thread level. Both io_thread and sql_thread have filtering functions.

Why can't server_id be repeated?

In the same cluster, if the server-id is repeated, some strange problems may occur.

Consider the following two cases:

Figure 1: The server-ids of the master and slave are different, but two or more slaves have the same server-id

In this case the replication will swing left and right. When the server-id of two slave libraries is the same, if slave library 1 has been connected to the master library, slave library 2 also needs to connect to the master library. If it finds that there is a previous connection with the same server-id, it will first cancel the connection and then re-register it.

Refer to the following code snippet:

int register_slave(THD* thd, uchar* packet, uint packet_length)
{
 int res;
 SLAVE_INFO *si;
...
 if (!(si->master_id= uint4korr(p)))
 si->master_id= server_id;
 si->thd= thd;
 pthread_mutex_lock(&LOCK_slave_list);
/* Unregister the connection with the same server-id first*/
 unregister_slave(thd,0,0); 
/* Re-register */
 res = my_hash_insert(&slave_list, (uchar*) si);
 pthread_mutex_unlock(&LOCK_slave_list);
 return res;
...
}

The two slaves are constantly registered and unregistered, which will generate a lot of relay log files. If you check the slave status, you will see that the relay log file name keeps changing, and the replication status of the slave is sometimes yes and sometimes connecting.

Figure 2: In a chained or dual-master structure, the server-id of the master and slave are the same

Slave 1 is also a relay database. It can synchronize correctly and then rewrite the relay-log content into its own binlog. When the slave 2 io thread with server-id 100 obtains the binlog, it finds that all the contents are from itself and discards these events. Therefore, slave 2 cannot correctly synchronize the data of the master. Only events written directly to the relay server can be correctly synchronized to slave 2.

From the above two cases, we can see that it is very important to maintain the uniqueness of server-id in the same replication set.

Dynamic modification of server_id

I accidentally discovered that the server-id can be modified dynamically, but don't be too happy too soon. The advantage is that, in the case of Figure 1 above, the server-id conflict can be resolved by simply modifying the server-id of one of the slaves. The disadvantages are very hidden, as shown in the following structure:

Now suppose that the active-master loses synchronization with the passive-master for some reason, and some DDL changes are made on the passive-master. Then a dba suddenly had an idea to change the server-id of passive-master to 400. When dual-master replication is started, the DDL changes with server-id 200 previously executed on the passive-master will fall into an infinite loop. If it is alter table t engine=innodb, it will never stop, you may find it. But it is difficult for you to find SQL like update a=a+1; Of course, this scenario is just my fabrication. Here is a more real example of the strange slave lag problem that occurs when the two backup machines of the primary and backup are converted to dual masters: http://hatemysql.com/2010/10/15/神奇的奴-lag问题时官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的官方的

I give these two examples just to illustrate that modifying the server-id is a bit dangerous and it is best not to modify it. So is it possible to generate it in one step?

Generate a unique server_id

The commonly used methods are as follows:

1. Use random numbers

The server-id of MySQL is a 4-byte integer ranging from 0 to 4294967295, so the possibility of conflict when using a random number in this range as the server-id is very small.

2. Use timestamp

Directly use date +%s to generate server-id. If we calculate that there are 86400 seconds in a day and we calculate for the next 50 years, the largest server-id used will be 86400*365*50, which is completely within the server-id range.

3. Use IP address + port

This is the approach we often take. For example, if the IP address is 192.168.122.23 and the port number is 3309, then the server-id can be written as 122233309. The possibility of conflict is relatively small: it will only occur when *.*.122.23 or *.*.12.223 is encountered and 3309 is built with the same replication set.

4. Use a centralized number generator

On the management server, a self-incrementing id is used to uniformly allocate server-ids. This ensures that there is no conflict, but requires the maintenance of a central node.

5. Manage each replication set separately

Add a management table to the MySQL database in each replication set to ensure that the server-id of each slave database does not conflict.

The above methods are all good, but:

• Method 4 adds a maintenance burden, and it is a bit troublesome to maintain a set of number generators for the development environment, test environment, and online environment. Mixing them together may lead to the risk of network segment isolation, and the issue of number generator database permissions is difficult to control. So it is not recommended.
• Method 5 achieves autonomy, but the management cost is a bit high. The slave database needs to be able to write to the server-id table of the master database, which is complicated.
• The problem with all five methods is that when using cold standby data to expand capacity, the server-id needs to be modified manually, otherwise it will conflict with the server-id of the cold standby source. Moreover, when MySQL is started, you cannot tell whether it has just been expanded through backup or has been running normally before. So you don’t know whether to change the server-id. I hope that the server-id is completely transparent to the dba and there will be no conflicts, so as to completely block this annoying thing.

Suggested approach

It’s actually very simple. ipv4 is a 4-byte integer, which has the same range as server-id. We believe that only the IP address + port can uniquely identify a MySQL instance, so we always hope to integrate both IP information and port information into the server-id. But don't forget that two identical ports cannot be started on the same IP at the same time. Therefore, server-id only needs to be in the integer form of the IP address: select INET_ATON('192.168.12.45'), 3232238637! For all newly launched instances, the MySQL startup script forces a check on the server-id. If an incorrect server-id is found, it is corrected and then started. This method has a prerequisite: multiple instances on the same machine should not have a master-slave relationship, otherwise the same server-id will cause problems. This situation usually only occurs in a test environment and is basically not seen online. Once this premise is met, all problems can be easily solved.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.