Writing transactions to .MYD file to take Backup

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:

• Safest way to perform mysqldump on a live system with active reads and writes? (Jun 20, 2012)

• Difference between backup methods: Oracle and MySQL (Apr 18, 2012)

• Quick MySQL Backup (1 file per table) (Jan 31, 2012)

• mysql db backup excluding specific tables (Nov 30, 2011)

• Quick MySQL Backup (Oct 01, 2011)

• How can I optimize a mysqldump of a large database? (Apr 17, 2011)

StackExchange Database Administrators Q#21004, answer score: 3