Shulou(Shulou.com)06/01 Report--

Preface

When we build a MySQL cluster, we naturally need to complete the master-slave synchronization of the database to ensure data consistency. And master-slave synchronization is also divided into many ways, one master multi-slave, chain master-slave, multi-master multi-slave, according to your needs to set up. But as long as you need master-slave synchronization, you must pay attention to the configuration of server-id, otherwise there will be master-slave replication exception.

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

Server-id configuration

When you use a master-slave topology, be sure to specify a unique and different server-id for all MySQL instances. The default value is 0. When server-id=0, the binary log will still be recorded for the host, but all slave connections will be rejected; for the slave, the connection to other instances will be refused.

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

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

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

# my.cnf [mysqld] # replicationlog-bin=mysql-binserver-id=171562767sync_binlog=1binlog-ignore-db=mysqlbinlog-ignore-db=information_schema

Use of server-id

Server-id is used to identify database instances to prevent infinite loops of SQL statements in chained master-slave, multi-master and multi-slave topologies:

The source instance marked binlog event filters the main library binlog, and when it is found that the server-id is the same, skip the event execution to avoid infinite loop execution. If replicate-same-server-id=1 is set, all event is executed, but it is possible to cause an infinite loop to execute SQL statements.

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

When the server-id of the main library and the standby library are repeated

Due to the default replicate-same-server-id=0, the slave database skips all data synchronized by the master database, resulting in inconsistency between master and slave data.

When two slave server-id are duplicated

It will cause the connection between the slave library 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 library and slave library connect and synchronize through the event mechanism. When a new connection arrives, if the server-id is found to be the same, the master library will disconnect the previous connection and re-register the new connection. When the A library is connected to the main library, when the B library connection arrives, the A library will be disconnected, and the A library will be reconnected, resulting in a lot of abnormal information over and over again.

Rules for generating server-id

Since the server-id can't be the same, how can we make sure that each one is different when we have 10 instances? There are several common methods:

Random number timestamp IP address + port is allocated centrally in the management center to generate self-increasing ID

All of the above methods are fine, but be careful not to exceed the maximum value of 2 ^ 32-1, and the value had better be > 2. The method I use is two digits after the IP address + the serial number of the native MySQL instance, but if you manage multiple instances through docker, you can wonder if there is any beautiful solution.

Server-uuid configuration

The MySQL service automatically creates and generates 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 shell > cat ~ / mysql/data/ auto.cnf [auto] server-uuid=fd5d03bc-cfde-11e9-ae59-48d539355108

This auto.cnf configuration style is similar to my.cnf, but this file contains only an auto configuration block and a line of server-uuid configuration. 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 host, and use show slave status on the slave to view the Master_UUID field.

Server-uuid parameters do not replace server-id, they have different functions. If the master-slave instance has the same server-uuid, it will quit if the master-slave synchronization occurs. However, we can set replicate-same-server-id=1 to avoid error reporting (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

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.