Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a question regarding the logical backup of MySQL databases that use both MyISAM and InnoDB.

The mysqldump utility supports these two options:

  • --single-transaction - Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does) [...] Option automatically turns off --lock-tables.

  • -x, --lock-all-tables - Locks all tables across all databases. This is achieved by taking a global read lock for the duration of the whole dump. Automatically turns --single-transaction and --lock-tables off.

    • For InnoDB, we need --single-transaction

    • for MyISAM, we need - lock-tables or lock-all-tables (in case we need cross-database consistency).

So, how is a hybrid database (a database that uses both MyISAM and InnoDB engines) supposed to be backed up?

Edit:

Just to clarify, the question could be reformulated like this:

Do the lock-[all-]tables options guarantee a consistent backup of InnoDB tables?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

With mysqldump you can only safely use --single-transaction if all your tables are InnoDB, otherwise your backup is inconsistent.

If you have the requirement for a hybrid backup, then you need the lock-tables on all tables in the backup (default), which will be safe for all engines. It's also worth mentioning that the default options will make sure your backup is safe, you don't need to turn any special flag on.

Note: If you do have a hybrid mix, perhaps look at xtrabackup. It will only be locking during the MyISAM phase of the backup.

share|improve this answer
    
Was just about to post this, but you beat me to it. Relevant link: percona.com/doc/percona-xtrabackup/innobackupex/… –  Derek Downey Sep 30 '11 at 12:24
    
Thanks for confirming that lock-tables guarantees consistent backup for both engines. I'm currently looking at Zmanda Recovery Manager for MySQL (I'll take a look at xtrabackup too). –  Dimitre Radoulov Sep 30 '11 at 12:33
    
Zmanda is just a GUI manager for all other backup methods - I'm pretty sure using xtrabackup is supported. –  Morgan Tocker Sep 30 '11 at 12:38
    
Hi @Morgan Tocker, Zmanda also provides a set of scripts (zrm-*) which actually execute various backup types: logical, raw and snapshots. All database that we administer are hybrid, so there is little value of using a separate product - we cannot avoid the lock during the backup. –  Dimitre Radoulov Sep 30 '11 at 12:44
    
This answer says that if one uses mysqldump and specifies --lock-all-tables then the backup will be consistent, right? And one needs to run mysqldump only once: it'll be safe for both InnoDB and MyISAM at the same time, right? –  KajMagnus Oct 18 '13 at 13:58

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.