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'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

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
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:

share|improve this answer
    
Thanks @RolandoMySQLDBA exactly this is my scenario. I'll fix it as you guided. –  pprakash Jul 17 '12 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. –  Aaron Brown Jul 17 '12 at 13:59

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

share|improve this answer
    
This is a very valid answer in lieu of the fact that MyISAM does not cache .MYD. +1 !!! –  RolandoMySQLDBA Jul 17 '12 at 14:01

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.