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

In this issue, the editor will bring you the enhancement of how to synchronize SLAVE relay-log-related log files in mysql 5.5. the article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

In version 5.1, slave gets the log from MASTER, writes it to relay-log, and applies it to SQL

Note here that writing to RELAY-LOG refers to the relay-log written to "OS cache" first, rather than flushing it to disk immediately.

When to refresh depends on the CACHE refresh time of the operating system.

[@ more@]

If, during this period, a log has just been synchronized to "relay-log @ os cache" and not flushed to "relay-log @ disk", OS CRASH, then the log will be lost

(don't worry about this: MYSQL will retrieve the log from MASTER after reboot.)

Other related SLAVE files that also have consistent problems are: relay-log.info; master.info

If the two files are inconsistent, the problem will not be so easy; repeated SQL or re-reading of the log is likely.

In V5.5, MYSQL strengthens this block by adding three parameters:

Sync_relay_log_info # default 0

Sync_master_info # default 0

Sync_relay_log # default 0

Let them control how many SLAVE log "statements / transactions" trigger synchronization of related files.

# sync_relay_log_info

If the value of this variable is greater than 0

A replication slave synchronizes its relay-log.info file to disk (using fdatasync ()) after every sync_relay_log_info transactions.

A value of 1 is the generally the best choice.

The default value of sync_relay_log_info is 0

Which does not force any synchronization to disk by the MySQL server-in this case

The server relies on the operating system to flush the relay-log.info file's contents from time to time as for any other file.

# sync_master_info

If the value of this variable is greater than 0

A replication slave synchronizes its master.info file to disk (using fdatasync ()) after every sync_master_info events.

The default value of sync_relay_log_info is 0 (recommended in most situations)

Which does not force any synchronization to disk by the MySQL server

In this case, the server relies on the operating system to flush the master.info file's contents from time to time as for any other file.

# sync_relay_log

If the value of this variable is greater than 0

The MySQL server synchronizes its relay log to disk (using fdatasync ()) after every sync_relay_log writes to the relay log.

There is one write to the relay log per statement if autocommit is enabled, and one write per transaction otherwise.

The default value of sync_relay_log is 0, which does no synchronizing to disk-in this case

The server relies on the operating system to flush the relay log's contents from time to time as for any other file.

A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the relay log.

However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

Their working mode and effect are a little similar: sync_binlog parameter, we should pay attention to IO performance evaluation when setting it, especially sync for relay-log.

General SLAVE, that is, opening BINLOG to write, and now frequently refreshing relay-log writes, the pressure on IO has increased.

For example: refresh of relay-log.info:

If we set sync_relay_log_info=1 most safely, then every time we execute a SQL/ transaction, the SLAVE process needs to swipe to DISK after updating the relay-log.info

Assuming that our machine can execute QPS=1000, this requires relay-log.info to be refreshed 1000 times; (of course, if DISK has RAID CACHE, and write-back takes effect; the situation will be much better)

It's still very scary.

But considering that SLAVE generally does not carry the pressure caused by SELECT, the pressure generally does not exceed that of MASTER.

As long as the hardware configuration of MASTER is similar to that of SLAVE, you don't have to worry about it.

In addition, there is another parameter in v5.5: relay_log_recovery

When set to ENABLED, automatically discard all outstanding relay-log after CRASH and retrieve logs from MASTER again; this ensures the integrity of relay-log

Enable automatic relay log recovery immediately after the server starts

This means that the replication slave will discard all outstanding relay logs

And retrieve them from the replication host.

This should be used after the replication slave crashes to ensure that potentially corrupted relay logs are not processed.

The default value is 0 (disabled).

You can change this global variable dynamically or by starting a slave with the-- relay log recovery option.

The above is how to strengthen the synchronization of SLAVE relay-log-related log files in mysql 5.5 shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are 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.