Bug #40352 Backup of live static InnoDB without crash recovery

• Submitted: 27 Oct 2008 16:31
• Modified: 13 May 2010 16:03
• Reporter: Ben Clewett
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 5.0.67
• OS: Any
• CPU Architecture: Any
• Tags: backup live innodb crash recovery

[27 Oct 2008 16:31] Ben Clewett

Description:
A search and posting to news groups suggests this is not yet possible.

I am requesting with great hope that this ability may be added:

To be able to complete a 'tar' backup of a live InnoDB database in which DML is not occurring, such that restoring the backup will not result in a crash recovery.  For instance a slave server where the slave has been stopped: STOP SLAVE;

With the understanding I have, I would imagine this would require committing all outstanding transactions from the log and syncing disk writes, until such time as DML resumed after the backup.

This would enable slaves to be kept running, which enables users to still complete select queries to gain reports.  Further, the InnoDB cache would not be flushed, drastically aiding performance.  

This also allows great simplification of monitoring systems (such as Nagios) which would then not have to account for backup downtime, which can be hard to set-up reliably.

How to repeat:
echo "STOP SLAVE" | mysql
tar cvzf mysql.tgz mysql_parant_dir
echo "START SLAVE" | mysql

cd /other_location
tar xvzf mysql.tar
mysqld 

(No crash recovery)