Sharing experience on MySQL slave maintenance

Preface:

MySQL master-slave architecture should be the most commonly used set of architectures. The slave database will synchronize the data transmitted from the master database in real time. Generally, the slave database can be used as a backup node or for query. In fact, not only the master database needs more attention, but the slave database also needs regular maintenance. This article will share some slave database maintenance experience. Let's learn together.

1. It is recommended to use GTID mode for master-slave replication

GTID is the global transaction ID. GTID is actually composed of server_uuid:transaction_id. Among them, server_uuid is the unique identifier of a MySQL instance, and transaction_id represents the number of transactions that have been submitted on the instance, and increases monotonically with the transaction submission. Therefore, GTID can ensure the execution of each MySQL instance transaction (the same transaction will not be executed repeatedly, and unexecuted transactions will be completed).

GTID-based master-slave replication can replace the traditional method of locating the replication position through the binlog file offset. Especially for the one-master-multiple-slave architecture, with the help of GTID, in the event of a master-slave switch, other MySQL slaves can automatically find the correct replication location on the new master. This greatly simplifies the maintenance of the cluster under a complex replication topology and reduces the risk of errors in manually setting the replication location. In addition, GTID-based replication can ignore transactions that have already been executed, reducing the risk of data inconsistency.

2. It is recommended that the slave database parameters be kept consistent with the master database

To ensure data consistency between the master and slave libraries, it is recommended that the slave library version be consistent with the master library, and related parameters be kept consistent with the master library as much as possible. For example, parameters such as character set, default storage engine, and sql_mode should be set the same. Especially for some parameters that cannot be modified dynamically, it is recommended to write them into the configuration file in advance and make them consistent with the main database.

3. Backup can be performed from the database

MySQL full backup will put some pressure on the server and sometimes hold a global lock for a short time. Especially for databases with large data volumes and busy business, full backup may have an impact on business. It is recommended to deploy the backup script on the slave server. The full backup can be performed on the slave server to reduce the impact of the backup process on the master server.

4. It is recommended to set the slave library to read-only

The database read and write status is mainly set by the read_only global parameter. By default, the database is used for read and write operations, so the read_only parameter is 0 or false. At this time, whether it is a local user or a user accessing the database remotely, as long as they have permission, they can perform read and write operations.

To avoid manual update operations on the slave library, it is recommended to set the slave library to read-only, that is, set the read_only parameter to 1. read_only=1 Read-only mode will not affect the synchronous replication function of the slave database. The slave database will still read the log on the master and apply the log on the slave side to ensure the synchronization and consistency of the master and slave databases. Setting the slave database to read-only will restrict users without super permissions from performing data modification operations. When ordinary application users perform DML operations such as insert, update, and delete that will cause data changes, they will be notified that the database is in read-only mode. This can effectively prevent update operations from occurring in the slave library.

In addition, if conditions permit, the slave database can take on part of the query work. For example, some report aggregation analysis queries or external service queries can be configured as slave database queries to reduce the pressure on the main database.

5. Pay attention to slave database monitoring and master-slave delay

Although the slave database is not as important as the master database, you should pay more attention to the monitoring status of the slave database at ordinary times. Don't wait until you need to use the slave database to find that the slave database is already inconsistent with the master database. In addition to some basic monitoring, the slave database should pay special attention to the replication status and delay status.

We can execute show slave status; on the slave side to query the slave status. There are three main values ​​of concern, namely Slave SQL Running, Slave IO Running and Seconds Behind Master. These three values ​​represent the SQL thread running status, IO thread running status, and slave database delay seconds. Only when Slave SQL Running, Slave IO Running is yes, and Seconds Behind Master is 0, do we consider the slave database to be running normally.

Summarize:

This article mainly shares some personal experiences on slave database maintenance. If there are any errors, please correct them. If other students have relevant experience or suggestions, you can also leave a message to share and discuss.

The above is the detailed content of the sharing of MySQL slave database maintenance experience. For more information about MySQL slave database maintenance experience, please pay attention to other related articles on 123WORDPRESS.COM!