Q:

Backing up MySQL DB wtih mixture of innodb and myisam tables

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.

A:

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

