Generating a set of files containing dumps of individual tables in a way that guarantees database consistency

Question

I'd like to dump a MySQL database in such a way that a file is created for the definition of each table, and another file is created for the data in each table. I'd like this to be done in a way that guarantees database integrity by locking the entire database for the duration of the dump. What is the best way to do this? Similarly, what's the best way to lock the database while restoring a set of these dump files?

edit

I can't assume that mysql will have permission to write to files.

Answer 1

If you are using InnoDB tables and therefore have transaction support, you don't need to lock the database at all you can just add the --single-transaction command line option. This gives you a consistent snapshot without locking anything by using the transaction mechanism.

If you don't have transaction support you can get what you describe with the --lock-tables command line option, this will lock the databases.

It's also worth noting that READ LOCKs aren't quite as good as they sound since any write operation will lock which will lock subsequent read operations, see this article for more detials

Edit: I don't think it's possible to dump the format and data separately without the file writing permission and the --tab option. I think your only option is to roll your own mysqldump script that uses one of the transaction or READ LOCK mechanisms that mysqldump uses. Failing that it may be easier to just postprocess a single dumpfile into the format you want.