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?