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

How to understand the scenario in which slave database writes redo and binlog does not lose data in real time? I believe that many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1.slave involves related documents

After reading the binlog log of master, slave needs to land three files: relay log, relay log info, and master info:

Relay log: the binlog of the read master. The content and format are the same as the binlog of master.

Relay log info: record the location, file number and other information of the relay log applied by SQL Thread

Master info: record the location, file number, delay and other information of the binlog read by IO Thread from master

If these three files are not landed in time, the host crash will lead to data inconsistency.

two。 Information storage mode

Before MySQL 5.6.2, the master information recorded by slave and the information of slave application binlog were stored in files, namely master.info and relay-log.info. After version 5.6.2, logging is allowed in table with the following parameters:

Master-info-repository = TABLE

Relay-log-info-repository = TABLE

The corresponding tables are mysql.slave_master_info and mysql.slave_relay_log_info, and both tables are innodb engine tables.

3. Control refresh parameters

Relay log, relay log info and master info also have three parameters to control refresh:

? sync_relay_log: the default is 10000, which means that every 10000 sync_relay_log events are flushed to disk. A value of 0 means no refresh and is controlled by cache of OS.

Sync_relay_log_info: if relay _ log_info_repository is FILE, if set to 0, ask OS to refresh the disk. The default is 10000 flushes to the disk. If relay_log_info_repository is TABLE and INNODB storage, no matter what the value is, the evnet will update the table every time.

Sync_master_info: if master-info-repository is FILE, if set to 0, each sync_master_info event will be flushed to disk (default is 10000 times); if master-info-repository is TABLE, if set to 0, the table will not be updated. If set to 1, the table will be updated by default to 10000

4. Recommended parameter settings

Sync_relay_log = 1

Sync_master_info = 1

Sync_relay_log_info = 1

Master-info-repository = TABLE

Relay-log-info-repository = TABLE

When this setting is set, the call to fsync () / fdatasync () increases as the number of master transactions increases, and if slave's binlog and redo are also refreshed in real time, it will cause serious IO performance bottlenecks.

After reading the above, have you mastered the method of how to understand the scenario in which slave library writes redo and binlog does not lose data in real time? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.