Consistent logical backup of databases that use MyISAM and InnoDB engines

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.

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.