Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
  • 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 ?

share|improve this question

2 Answers 2

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

share|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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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