I have a large database (almost 1GB) and it has a mixture of innodb and myisam tables. Does anyone have any general tips when backing it up or more specifically the commands i should send to mysqldump. I see that i should lock myisam tables, and that single transactions for innodb, but what if i have both. Also, what is actually happening when i lock an entire (very big) table on a production database.

2 Answers 2


What is interesting is that MySQL client tools cannot handle both storage engines simultaneously. However, do not despair.

There is really one successful way to snapshot data via a mysqldump:

Step 1) mysql -h... -u... -p... -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &

Within this MySQL session, this locks all tables, MyISAM, InnoDB, and others. The whole database is in a read-only state

Step 2) About 20-30 sec later, Capture the Process ID doing the SLEEP command like this:

SLEEPID=`mysql -h... -u... -p... -A -e"SHOW PROCESSLIST" | grep "SLEEP(86400)" |awk '{print $1}'`

Step 3) Perform mysqldump

mysqldump -h... -u... -p... --single-transaction --routines --triggers --all-databases > MySQLData.sql

Step 4) Kill the process doing the SLEEP command:

mysql -h... -u... -p... -A -e"KILL ${SLEEPID}"

Believe me, perfect snapshot every time.

Give it a Try !!!

P.S. To be fair, there are other tools you may want to look at

Tool 1) CDP R1Soft (using the MySQL Module)

Tool 2) XtraBackup from Percona


You can use Percona's XtraBackup with innobackupex, it will do the work for you.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .