Hi all,
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 - lock-tables or
lock-all-tables (in case we need cross-database cosistency).

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



Regards
Dimitre

On 30/09/2011 12:44, Radoulov, Dimitre wrote:
Just to clarify, the question could be reformulated like this:

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



Regards
Dimitre

"Radoulov, Dimitre" <cichomitiko (AT) gmail (DOT) com> wrote:

....

Yes. But of course the global lock will cause writes to queue up,
so anybody tries to avoid it. I.e. by using InnoDB tables and then
mysqldump --single-transaction


XL

On 30/09/2011 13:29, Axel Schwenke wrote:

Thank you, Axel!
I had this doubt because of the fact that the read lock doesn't
guarantee a consistent *copy of the datafiles* with OS utilities when
InnoDB tables are present in the database. That, of course, is different
than the export generated by mysqldump.

Thanks again!

Best regards
Dimitre