Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I run the following before tar-ing up the data directory:


However, tar will sometimes complain that the ibdata* and ib_logfiles* files are updated during the process. What am I missing?

The slave machine is in a cold standby machine so there are no client processes running while tar is running.

CentOS release 5.6 64bits, MySQL 5.1.49-log source distribution.

share|improve this question
You can't run a cold backup. By definition it is not a cold backup if the database is running. – Andrew Brennan Apr 2 '14 at 9:44

FLUSH TABLES WITH READ LOCK will not halt writes to InnoDB.

It may block access to writing to tables, but InnoDB will allow writes to ibdata1 to provide MVCC info for redo and undo logs.

Check out Map of InnoDB's Infrastructure

InnoDB Architecture

Please note the physical independence of a table from the ibdata1, and how log files are related.

Since the box is a Slave, you have two options :

OPTION #1 (Warm Backup)

  • Run your Linux/tar

OPTION #3 (Cold Backup of Fully Committed Data)

  • Run SET GLOBAL innodb_fast_shutdown = 0;
  • Run service mysql stop
  • Run your Linux/tar
  • Run service mysql start

OPTION #3 (Cold Backup)

  • Run service mysql stop
  • Run your Linux/tar
  • Run service mysql start


Options #2 and #3 would be the better choices by far. What is the difference between them ?

  • OPTION #2 flushes all transactional changes out of ibdata1 and the transaction logs (ib_logfile0, ib_logfile1). This makes for a longer shutdown. However, you will have a faster mysql startup because InnoDB Crash Recovery would not need to be performed.
  • OPTION #3 lets you shutdown faster, leaving InnoDB in suspended animation (all transactional changes in ibdata1 and the transaction logs (ib_logfile0, ib_logfile1)). The transactional changes are applied on mysql startup during the InnoDB Crash Recovery phase.
share|improve this answer
I do run STOP SLAVE as step 1. – Peter Gon Dec 12 '12 at 1:44
In your OPTION #1 try - Run STOP SLAVE; - Wait for sometime ensure there are no recent timestamps modified on ibdata or ib_logfiles. (Say 30mins). - Run your Linux/tar - Run START SLAVE; But for me best is OPTION #2. – Mannoj Feb 4 '13 at 9:54

I believe you could accomplish it by freezing your filesystem.

LVM has support for snapshots, as described in CentOS documentation and in TLDP.

Some Storage models, like DELL Equallogic, provides snapshot support and access from Linux Command Line. You could benefit from those, too.

share|improve this answer
Freezing the filesystem is not enough for InnoDB. The buffer pool still has changes to commit to disk for data and indexes. – RolandoMySQLDBA Aug 6 '14 at 20:35
Snapshots provide what's called "crash consistent" copies. If your database is able to recover from someone tripping over the power cord, it will be able to recover from a restored snapshot. This doesn't mean that it will be as quick or easy as other types of backups and you will lose in-flight transactions, so we have other tools. OTOH, there are systems that let you take snapshots as fast as every minute. If InnoDB doesn't provide crash recovery, then yeah, snapshots are useless but why are you using it, then? NOTE: I'm not a DBA, I just play one during sales calls. :) – samwyse Sep 8 '15 at 15:24

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.