Creating a database backup of a live shop?

Question

I like to create a backup of the whole database before doing any major changes on a live environment.

For smaller projects that is no problem as it usually only takes a few seconds.

On larger projects however I've noticed that using mysqldump creates a lock on the database while the backup is being created - which is obviously very bad as new orders won't be accepted during that time period.

I've tried using the --skip-lock-tables command with mysqldump, but I fear that this might create an unusable backup, as the database might be written to while the backup is being created.

What are some recommended strategies for creating a backup of a database before doing any major changes, on a live environment?

• Bryan 'BJ' Hoffpauir Jr.: Are you running MySQL on a physical system or a cloud-based service? Are you using a Volume Manager like LLVM on the server hosting the DB?
• Pavan Kumar: Thank u fr ur question. I didnt know this concept till nw..

Answer 1

You should always use the --single-transaction option when dumping an InnoDB/Magento database. I'm not sure if it prevents tables from being locked (couldn't find any reference), but it will give you a consistent snapshot of the database you're backing up at least.

Without this command, you will dump data as they are written to the database real-time, and you will end up with inconsistent data (e.g. orphaned records, etc.).

I've generally had good experience dumping 1-3GB databases with this option from live servers during off-peak times.

Reference: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction

• Louis B.: Thanks, it looks like the best option. I will be using mysqldump --single-transaction --quick to create a quick backup of the database just in case. Here's to hoping I won't ever have to do an emergency restore. :)
• Louis B.: I noticed that I can still write to the database while the dump is being created (for example delete a product). Do you know what happens if a database is changed while it's being dumped, will I still be able to restore from it later on?
• laketuna: --single-transaction takes a snapshot of your database as soon as you issue the command and dumps that. The changes to your database will operate as normal, but the dump won't have these changes made while the backup file is generated.