up vote 0 down vote favorite
share [g+] share [fb]
  • If i will lock my tables while taking dump of production and at the same time if user will enter any data for specific table. What will happen to that specific insertion ? Will it be discarded or it will be inserted after successful creation of dump ?

  • If i will not lock my tables while taking dump of production. Will it create any problem ?

  • Which one is preferred way to taking dump of production with or without locking tables ?

link|improve this question
83% accept rate
feedback

2 Answers

up vote 2 down vote accepted
  • If you use mysqldump to backup your tables and someone attempts to modify the locked table, their update will block until the table is unlocked by mysqldump.
  • If you do not lock your tables while attempting to make a backup, it is entirely possible your backup would end up in an inconsistent state. Depending on your application, it could cause problems.
  • As @tsykoduk states, it is best to take your backups from a read-only replica of your production server. If that's not possible, the best solution depends on your environment. For example, if you use only InnoDB tables, you can run mysqldump with --single-transaction to ensure a consistent backup without blocking updates.

The MySQL docs describe most of these options in greater detail:

http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html

link|improve this answer
Thanks for great answer. I have only InnoDB. So, i don't need to worry about lock and all that ? Why ? What will --single-transaction do? – krunal shah Mar 22 '11 at 5:42
The full description can be found here: dev.mysql.com/doc/refman/5.1/en/… – Insyte Mar 22 '11 at 5:43
But basically it starts a transaction, creates the dump, than closes the transaction. That way mysqldump sees a consistent view of what the DB looked like when the transaction began, but the table continues to get updated outside of that transaction. – Insyte Mar 22 '11 at 5:44
feedback
up vote 1 down vote

It's best to take a backup off of a read-only replica, as when you lock the tables, any writes will error out. Also, a backup can be a pretty resource intensive operation, so it can effect performance.

If you do not have a read-only replica, then I would suggest looking into one of the MySQL hot backup products out there (Percona has a good one, as I recall).

Also - if you do not lock the tables, or use a hot backup product, you run the risk of not getting a complete backup. IE, if someone is writing to a table, and has it locked when you run the backup, you'll get corrupted data.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.