10

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?

2 Answers 2

9

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.

7
  • Was just about to post this, but you beat me to it. Relevant link: percona.com/doc/percona-xtrabackup/innobackupex/… Sep 30, 2011 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). Sep 30, 2011 at 12:33
  • Zmanda is just a GUI manager for all other backup methods - I'm pretty sure using xtrabackup is supported. Sep 30, 2011 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. Sep 30, 2011 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, 2013 at 13:58
3

As an additional idea for your situation, maybe it's helpful to spend some thoughts on setting up a master slave setup, where your backup is made from the slave.

A MySQL db with a mix of InnoDB and MyISAM will always (as far as I know in every configuration and with every backup tool) lock the MyISAM tables for the MyISAM backup phase.

To avoid this locking, use this setup:

  • Server A: mysql Master
  • Server B: mysql Slave (connected to Server A)

All writes and reads go to the Server A (Master). Server B (Slave) is only used for backup. On the slave, you install a backup tool or script of your choice, e.g. MySQL Administrator.

Configure your backup, e.g. daily during night-time. The locks are on the slave, resulting in a lag while the slave does lock, but this lag is caught up as soon as the backup is over and the lock is opened.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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