Backing up MySQL DB wtih mixture of innodb and myisam tables

Question

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.

Answer 1

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

Answer 2

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