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

This article is about how MySQL generates a unique server-id. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Preface

We all know that MySQL uses server-id to identify a database instance and uses it in chained or dual-master replication structures 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.

The purpose of server_id

To put it simply, server_id has two uses:

1.

two。 Used for IO_thread to filter the main library binlog. If replicate-same-server-id=1 is not set, when it is found from the io_thread of the library that the source of the event is the same as its own server-id, the event is skipped and the event is not written to the relay log. The event will not be executed naturally from the sql_thread of the library. This avoids infinite loops of sql statements in chained or dual-host structures.

Note: event with the same server-id is filtered at the io_thread layer, while rules such as replicate- (do | ignore)-are filtered at the sql_thread layer. Both io_thread and sql_thread have filtering capabilities.

Why can't server_id be repeated?

In the same cluster, once the server-id is repeated, it may cause some weird problems.

Look at the following two situations:

Figure 1: the server-id of the master library is different from that of the slave library, but two or more slave libraries have the same server-id

In this case, the copy will sway from side to side. When the server-id of the two slave libraries are 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 is found that there is a connection with the same server-id before, the connection will be unregistered first and then re-registered.

Refer to the following code snippet:

If (! (si- > master_id= uint4korr (p)) si- > master_id= server_id; si- > thd= thd; pthread_mutex_lock (& LOCK_slave_list); / * cancel the connection of 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 machines keep registering and logging out from the library, which will produce a lot of relay log files. If you look at the status of the library, you will see that the name of the relay log file is constantly changing, and the copy status of the library will be yes and it will be connecting.

From Library 1, which is also a relay database, it can synchronize correctly and then rewrite the relay-log content to its own binlog. When you get the binlog from the library 2 io thread with a server-id of100, you will discard the event when you find that all the content is self-originating.

As you can see in the above two cases, it is important to maintain the uniqueness of the server-id in the same replication set.

Dynamic Modification of server_id

Don't be too happy to find that server-id can be modified dynamically. The advantage is that in the case of figure 1 above, the server-id conflict can be resolved by directly modifying the server-id of one of the slave libraries. The disadvantages are hidden, as shown in the following figure:

Now suppose that some ddl changes have been made on passive-master after active-master 's synchronization with passive-master was disconnected for some reason. Then a dba had a whim to change the server-id of passive-master to 400. When double master replication starts, those ddl changes that were previously performed on passive-master with a server-id of 200will fall into an endless loop. If it's alter table t engine=innodb, it won't stop, maybe you'll find it. But it's hard to find a sql like update aura. Of course, this kind of scenario is just made up by me. Here is a more real example of the weird slave lag problem that occurs when the two standby computers of the active and standby devices are converted to dual master: the weird slave-lag problem that occurs when the two standby computers of the http://hatemysql.com/2010/10/15/ are converted to dual master.

These two examples just want to show that modifying server-id is a bit dangerous, it is best not to modify it, so can it be generated in one step?

Generate a unique server_id

The commonly used methods are as follows:

1. Use random number

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

two。 Use timestamp

Calculated in 86400 seconds a day, 50 years later, the largest server-id is only 86400-365-50, which is completely within the range of server-id.

3. Use ip address + port

This is the method we often use. For example, if ip is 192.168.122.23 and port is 3309, then server-id can be written as 122233309. The possibility of conflict is relatively small: encounter *. *. 122.23 or *. 12.223, and 3309 of the same replication set is built.

4. Adopt a centralized number generator

Self-increasing id is used on the management server to distribute server-id uniformly. This ensures that there is no conflict, but the central node needs to be maintained.

5. Manage each replication set separately

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

All of the above methods are good, but:

Method 4 adds the maintenance burden, and if the development environment, test environment and online environment all maintain a set of generator, it is a bit troublesome, and the risk of network segment isolation may be encountered when mixed together, and the problem of database permissions of the generator is difficult to control. So I don't recommend it.

Method 5 achieves autonomy, but the administrative cost is a bit high. The slave library must be able to write the server-id table of the master library, which is complex.

The problem with all five methods is that the cold backup data is used to expand the capacity, and the server-id needs to be modified manually, otherwise it will conflict with the server-id of the cold backup source. Moreover, when mysql starts, you can't tell whether the mysql has just been expanded through backup or has been running normally before. So you don't know whether this server-id should be changed or not. And I hope that server-id can completely block this annoying thing by being completely transparent to dba and without conflict.

Suggested method

It's actually very simple. Ipv4 is a 4-byte integer, exactly the same range as server-id. We believe that only ip address + port can uniquely identify a mysql instance, so we always want to integrate both ip information and port information into server-id. But don't forget that you can't start two identical ports on one ip at the same time. So, server-id only needs to take the integer form of ip address: select INET_ATON ('192.168.12.45'), 3232238637! For all newly launched instances, the mysql startup script forces server-id to be checked, corrects server-id errors when they are found, and then starts. This approach has a prerequisite: multiple instance on the same machine do not have a master-slave relationship, otherwise server-id will also cause problems. This situation generally occurs only in the test environment, but not online. With this premise met, all problems can be easily solved.

Thank you for reading! This is the end of this article on "how to generate a unique server-id for MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.