Detailed explanation of server-id example in MySQL master-slave synchronization

Preface

When we build a MySQL cluster, we naturally need to complete the master-slave synchronization of the database to ensure data consistency. There are many ways of master-slave synchronization, including one master and multiple slaves, chained master-slave, and multiple masters and multiple slaves. You can set them up according to your needs. But as long as you need master-slave synchronization, you must pay attention to the configuration of server-id, otherwise master-slave replication exceptions will occur.

In controlling database data replication and log management, there are two important configurations: server-id and server-uuid, which affect binary log file records and global transaction identifiers.

server-id configuration

When you use a master-slave topology, be sure to specify a unique server-id for all MySQL instances. The default value is 0. When server-id=0, the master will still record binary logs, but will reject all slave connections; the slave will reject connections to other instances.

The server-id of the MySQL instance is a global variable and can be viewed directly:

mysql> show variables like '%server_id%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| server_id | 171562767 |
+---------------+-----------+
1 row in set (0.00 sec)

We can modify the global variable server-id directly online, but it will not take effect immediately, so remember to restart the service after the modification. After restarting, the system configuration file will be re-read, making the previous modification invalid. Therefore, it is recommended to modify the configuration file and restart the service instead of modifying it online:

#my.cnf
[mysqld]
#replication
log-bin=mysql-bin
server-id=171562767
sync_binlog=1
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

Server-id usage

The server-id is used to identify the database instance to prevent infinite loops of SQL statements in chained master-slave or multi-master-multi-slave topologies:

• Mark the source instance of the binlog event
• Filter the main database binlog. When the server-id is found to be the same, skip the event execution to avoid infinite loop execution.
• If replicate-same-server-id=1 is set, all events are executed, but this may lead to an infinite loop of SQL statements.

Let's use two examples to illustrate why server-id should not be repeated:

When the server-id of the primary and standby databases are duplicated

Because replicate-same-server-id=0 by default, the slave database will skip all data synchronized with the master database, resulting in inconsistency between master and slave data.

When the server-ids of two standby databases are duplicated

This will cause the connection between the slave database and the master database to be disconnected from time to time, resulting in a large number of exceptions. According to the design of MySQL, the master and slave databases are connected and synchronized through an event mechanism. When a new connection comes, if the server-id is found to be the same, the master database will disconnect the previous connection and re-register the new connection. When database A is connected to the main database, database B will connect to it, which will disconnect database A. Database A will then reconnect, and this cycle will repeat, resulting in a large amount of abnormal information.

Rules for generating server-id

Since the server-id cannot be the same, when we have 10 instances, how can we ensure that each one is different? There are several commonly used methods:

• Random Numbers
• Timestamp
• IP address + port
• Centrally distribute in the management center and generate self-increment ID

All of the above methods are OK, but be careful not to exceed the maximum value 2^32-1, and the value should preferably be >2. The method I use is the last two digits of the IP address + the local MySQL instance number, but if you are using Docker to manage multiple instances, how do you generate this? Can you think about whether there is any elegant solution?

server-uuid configuration

The MySQL service will automatically create and generate the server-uuid configuration:

• Read the UUID in the ${data_dir}/auto.cnf file
• If it does not exist, automatically create the file and generate a new UUID and read it

shell> cat ~/mysql/data/auto.cnf
[auto]
server-uuid=fd5d03bc-cfde-11e9-ae59-48d539355108

The auto.cnf configuration style is similar to my.cnf, but this file only contains an auto configuration block and a server-uuid configuration line. It is created automatically, so do not modify its contents.

In the master-slave topology, the master and slave can know each other's UUID. Use show slave hosts on the master and show slave status on the slave to view the Master_UUID field.

The server-uuid parameter does not replace the server-id parameter. They have different functions. When synchronizing the master and slave instances, if the server-uuid of the master and slave instances are the same, an error will be reported and the system will exit. However, we can avoid the error by setting replicate-same-server-id=1 (not recommended).

References

• 17.1.6 Replication and Binary Logging Options and Variables: https://dev.mysql.com/doc/ref...
• How to generate a unique server ID: https://www.jb51.net/article/169550.htm

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.