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

This article mainly introduces what are the important parameters for MySQL to ensure high availability of replication, which are introduced in great detail and have certain reference value. Friends who are interested must finish reading them!

Expire_logs_days, the time of binlog cleanup.

Relay-log-recovery = 1 and relay-log-info-repository = TABLE; on the slave library ensure the consistency of master-slave data, no matter what mistakes the slave machine makes.

Why?

First of all, the SQL thread, the SQL thread apply applies the binary log and records the location to which the binlog is applied to the relay-info.log.

And it is not that the relay log application brushes the disk once to write relay-log.info, but a parameter is specified, as follows, which means to play back events 10000 times to write a disk. This is why the crash from the library, there is a 1062 error. Because the slave library has already inserted data, but the file relay-log.info does not record the file, when the file tells the database that one more operation will be performed after restart, there will be an error of repeated insertion of this primary key. So if this parameter is set to table, it satisfies the consistency and avoids the problem of out-of-sync between database and file.

IO thread:

Unlike relay_log_info_repository, simply setting master_info_repository to table cannot solve the problem of consistency in receiving logs from the IO thread, because the IO thread receives the log write file as the relay log file, while the database receives the log of the main database where the master-info.log file is written. (synchronization is determined by sync_master_info) these are two different files, such as when relay receives the log. For event2, but at this time master-info.log records 1, at this time crash, when restarting the slave library, master-info.log told the database that I only received 1 and received 2 again, which is repeated, even if master_info_repository is set to table does not solve the problem. But when an error is reported, show slave status. In the end, the SQL thread reports an error. So set another parameter, relay-log-recovery=1.

The last very important parameter:

Clean up the currently received relay log. Then pull the relay log again from the location where the SQL Thread is applied.

But to make sure that the main library binlog should be retained for enough time, because I have seen some companies with a delay of as long as a month.

There is also the setting of read-only. 5.7. there is a new permission super_read_only parameter, which is set to on. No one has permission, nor does dba.

SQL threads are highly available

Write the playback location of SQL to relay-info.log, and write this file once without executing an event, will the performance be very poor? Because there is no fsync, so the file record will lag behind, will relay_log_info_repository=TABLE (5.6 only have), this artifact will be put in the database, atomic operation. (configure from Library)

Write 10000 event only once fsync write disk, then there is a question, if set to 1, is it useful? It's no use. It's possible to lose a record.

Sync_relay_log_info: this parameter is the same as the sync_relay_log parameter, when set to 1, every time the slave I / O thread receives the binlog log sent by master, it will be written to the system buffer and then brushed into the relay-log.info, which is the safest, because in the event of a crash, you will lose at most one transaction, but will result in a large number of binlog O on the disk. When set to 0, it is not immediately brushed into the relay-log.info, but is decided by the operating system when to write, although the security is reduced, but a large number of disk Igamo operations are reduced.

High availability of IO threads

Receive an event, write the master.log file, indicate the accepted location, and then write the relay log file. When you send crash, there will be problems, and you can also save the table master_info_repository.

The master log file will lag behind, and the IO thread will re-pull the binlog in the master log file, pull the log repeatedly, and the SQL thread will report an error, and the final error will be displayed by the SQL thread.

Relay-log-recovery=1, clear all currently accepted relay log. Then pull the thread again at the location where the SQL thread is running. SQL threads are reliable, so what's the problem? If the binary log on the main library is gone, it will not come, and there will be a problem, even if it is based on SQL threads. But there is a situation that lags behind a long time online.

Master_info_repository=TABLE from enabling parallel replication, must also be set to table, there is a double gap in performance.

The difference between Read_only and super_read_only is that users with super_priv permission can still write read_only.

From the library, super_read_only also called.

The above is all the contents of the article "what are the important parameters that MySQL guarantees to copy with high availability". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.