I'm trying to backup MySQL DB by archiving the data directory of required databases.

I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not written into .MYD files. cpio reports that there are few changes which got written recently to the files were not archived. Is there any command or options to make MySQL write all the transactions before lock to .MYD files

2 Answers 2


Your problem may have to do with the session you ran FLUSH TABLES WITH READ LOCK;.

If you launched FLUSH TABLES WITH READ LOCK; like this:

# mysql -uroot -p... -e"FLUSH TABLES WITH READ LOCK;"

all that would do is close all open tables, reopen those tables, and the session dies. The lock is not maintained.

You would have to launch FLUSH TABLES WITH READ LOCK; like this:

# mysql -uroot -p... -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &

This forces the session to stay open and maintain the lock. You would have to open another session, get the process ID of the SELECT SLEEP(86400), start your backup, and then kill that DB Connection when your backup is complete.

Here is a sample script on how to mysqldump all databases locked with FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400):

MYSQL_CONN="-hhostip -uusername -ppassword"
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
    sleep 3
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

Here are my other posts where I discuss this and other backup variations involving FLUSH TABLES WITH READ LOCK:

  • Thanks @RolandoMySQLDBA exactly this is my scenario. I'll fix it as you guided.
    – Prakash
    Jul 17, 2012 at 13:33
  • +1 Good point. I assumed he had a stateful session. Usually, I write all but the simplest backup scripts in a language that allows for persistent connections (ruby, perl, etc) for this very reason. It's also a good idea to add a sync in there, too to make sure the filesystem has flushed all pages to disk. Jul 17, 2012 at 13:59

You need to run sync at the OS level to sync the fs cache to disk (assuming a *nix OS).

  • This is a very valid answer in lieu of the fact that MyISAM does not cache .MYD. +1 !!! Jul 17, 2012 at 14:01

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.