1

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

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;

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.

Improve this question
1
  • 2
    You can't run a cold backup. By definition it is not a cold backup if the database is running.
    – Andrew Brennan
    Commented Apr 2, 2014 at 9:44

2 Answers 2

Reset to default
2

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 STOP SLAVE;
  • Run FLUSH TABLES;
  • Run your Linux/tar
  • Run START SLAVE;

OPTION #2 (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

EPILOGUE

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.
Improve this answer
2
  • I do run STOP SLAVE as step 1.
    – Peter Gon
    Commented Dec 12, 2012 at 1:44
  • 1
    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
    Commented Feb 4, 2013 at 9:54
0

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.

Improve this answer
2
  • Freezing the filesystem is not enough for InnoDB. The buffer pool still has changes to commit to disk for data and indexes.
    – RolandoMySQLDBA
    Commented Aug 6, 2014 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
    Commented Sep 8, 2015 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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