Monday, November 16, 2009

MySQL Backup

Ever had to backup a MySQL database running on Windows. Well this was my first time. I have a pretty active database that grows to about 70GB over the course of a month. I wanted to perform the backup using a MySQL tool. I suppose I could have just run a Windows backup, but that wouldn't be much fun. So here is what I ended up doing. I ran the below command from within the folder I wanted to land the dump files in. For instance I wanted my dump files to reside in C:\mysql\backup so I ran the below command from within this folder I created.

..\bin\mysqldump.exe --user=root --password=abc123! --routines --create-options --single-transaction --skip-add-locks --skip-lock-tables --default-character-set=utf8 database table bzip2.exe > database_table.sql.bz2

To get the compression running, I downloaded Cygwin to use the bzip2.exe utility. I actually needed the below files for the bzip2.exe utility to run and I placed these files in the newly created backup folder:

To decompress the file simply run: bzip2.exe -d database_table.sql.bz2.

I apologize, but I can't list all the sites I looked at to come up with this specific command but it is not original. Maybe the original part is running on Windows instead of *nix. Maybe next time I will just run Windows backup

No comments: