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
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:
You need to run sync at the OS level to sync the fs cache to disk (assuming a *nix OS).
