Backup database database mySQL script

Question

We reserve Windows-based mySQL databases overnight by copying the entire mySQL database directory:

c$\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5 

This caused problems with MySQL, corruption of the table, and MySQL crash. It is not recommended to back up the active directory. We aim to disable MySQL in one night and then run the script. We also looked at starting mysqldump. Two questions:

What are the disadvantages of using a backup folder? This seems like the fastest recovery method.

Will mysqldump work on a real server so that it does not respond until the dump completes?

Answer 1

Backing up the real folder in which the database is stored comes, as you noticed, with some problems. Files can change when the folder is copied, which can lead to a damaged backup or (if you lock everything), which makes it impossible for MySQL to write (or possibly even read) the database, which is also not very good (TM).

One workaround is to back up a folder through a file system snapshot. I don’t know if NTFS supports this, but a quick google search found something called Shadow Copy. You can look at it.

Then, as pilsetnieks has already pointed out, you can run mysqldump in InnoDB tables using the --single-transaction option.

If you were on Linux, I would recommend you try Percona Xtrabackup , which has everything you could wish for. They have released a version of Alpha for Windows . It may be worth the look.

Answer 2

You can start mysqldump with the --single-transaction option (for InnoDB tables - I hope you use InnoDB.) This way, mysqldump does not need to lock tables for the whole dump and your system will not respond completely. (Performance may be somewhat affected, but at least it will not be completely dead.)