Consistent logical backup of databases that use MyISAM and InnoDB engines

Question

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?

Answer 1

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.

Answer 2

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.