Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

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

2 Answers 2

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

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 the Map of InnoDB's Infrastructure and 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)

  • Run SET GLOBAL innodb_fast_shutdown = 1;
  • 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

OPTION #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 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
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 Feb 4 '13 at 9:54

Your Answer

 
discard

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.