MySQLDump without locking the tables

Question

It seems that if you have many tables, you can only perform a MySQLDump without locking them all, otherwise you can an error.

What are the side effects of performing a MySQLDump without locking all the tables; Is the DB snapshot I get this way, consistent? Do I have any other alternative for getting a backup of a MySQL DB with many tables?

Answer 1

The best way (if using InnoDB) is actually to run the backup on a replicated slave. That way locking will be of no consequence. Else just use the --single-transaction flag as mentioned.

Answer 2

What storage engine(s) do you use?

If you are using InnoDB, then you can run mysqldump with the --single-transaction flag and get a consistent snapshot without locking the tables.

If you are using MyISAM, then you need to lock the tables to get a consistent snapshot. Otherwise any insert/update/delete statements that run on your MyISAM tables while mysqldump is running may or may not be reflected in the output depending on the timing of those statements.