Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 0 down vote accepted

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.