CODEWITHSUNDEEP
mysql
Raul Singahn
Raul Singahn

Reputation: 315

MySQLDump without locking the tables

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?

Upvotes: 4

Views: 8408

Answers (4)

simplifiedDB
simplifiedDB

Reputation: 164

For Innodb , you need mention --single-transaction in mysqldump utility to avoid locking and get a consistent snapshot.

For MyISAM , you need to lock the tables to get consistent snapshot else you will miss DML to be logged while running dump

Upvotes: 1

Sean Kinsey
Sean Kinsey

Reputation: 38046

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.

Upvotes: 7

ryanprayogo
ryanprayogo

Reputation: 11817

The --single-transaction flag should work if your DB is of type InnoDB.

Upvotes: 2

Ike Walker
Ike Walker

Reputation: 65547

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.

Upvotes: 4

Related Questions