Bug #82034 No need for FTWRL for cold/offline backup

• Submitted: 28 Jun 2016 15:21
• Modified: 10 Aug 2018 12:12
• Reporter: Andrii Nikitin
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.1 - 5.7
• OS: Any
• CPU Architecture: Any

[28 Jun 2016 15:21] Andrii Nikitin

Description:
Current documentation for cold/offline backup suggests to:
- FLUSH TABLES WITH READ LOCK
- capture MASTER STATUS 
- shutdown MySQL Server (while holding the lock)
- perform backup

This is a viable way to proceed, but on practice it may cause various practical issues.

It is common understanding in community that it is much simpler to just shutdown MySQL Server instead and just include binlog index file into backup.

Valid binlog position for point-in-time recovery or Slave setup - will be next binlog file (which is not present in  binlog index file).

How to repeat:
doc

Suggested fix:
see description

[10 Aug 2018 12:12] Margaret Fisher

Posted by developer:
 
Thanks for the suggestion! I have added this to the topic "Obtaining the Replication Master Binary Log Coordinates"
https://dev.mysql.com/doc/refman/8.0/en/replication-howto-masterstatus.html
(and also 5.6 and 5.7 versions):

If you are planning to shut down the master to create a data snapshot, you can optionally skip this procedure and instead store a copy of the binary log index file along with the data snapshot. In that situation, the master creates a new binary log file on restart. The master binary log coordinates where the slave must start the replication process are therefore the start of that new file, which is the next binary log file on the master following after the files that are listed in the copied binary log index file.

- I have also made relevant updates to a couple of the later topics in the process in case the user has taken this option.