For a MySQL installation on Windows Server 2008, 2003, or Windows Vista, backing up your databases can be performed in a couple of different ways.

The first method is more of a hack and involves backing up your entire MySQL “data” directory located somewhere in your “Programs Files” folder (ex:  C:\Program Files\MySQL\MySQL Server 5.0\data). But in order for this to work, you need to stop the MySQL server first and then restart it once the backup is complete. This means that your MySQL server is offline while the backup is performed. In addition to that, backing up your database this way is not a documented method. You are not guaranteed that it will work in future versions of MySQL. There are a few other “gotchas” as well. For example, restoring your backup won’t work if you don’t restore every single database folder that was originally in the MySQL data directory in the first place. In this case, the MySQL Server won’t even start.

THE CORRECT WAY TO BACKUP YOUR MYSQL DATABASES

If you want to play it safe, you should backup your MySQL databases the documented way. The tool of choice for this is “mysqldump.exe” which is located in your MySQL “bin” folder. You can use mysqldump from the Windows Command prompt while running as Administrator.

For those of you with experience in the *nix environment, you probably know how to redirect output to a file. You can do this in Windows with the Command Prompt as well.

If you placed the MySQL path in your Windows environment when you installed MySQL, you should be able to use a command like this in the Command Prompt to backup all of your databases:

mysqldump -uroot –pYOUR_ROOT-PASSWORD --all-databases > “C:\all-databases-backup.sql"

To backup only one database, you could use:

mysqldump -uroot –pYOUR_ROOT-PASSWORD DATABASE-NAME > “C:\database-backup.sql"

Of course, for the above commands to work, you need to substitute the MySQL root password with your own password. The root password was created when you ran the MySQL Server Instance Configuration Wizard during the install process of your MySQL server.

One thing I’ve found is that you should not leave a space between the -u and -r parameters in your mysqldump command. Other than this, it’s rather straight forward to create a MySQL database backup.

If you want more information about mysqldump, click here.

SECURE, COMPRESSED, AUTOMATED ONLINE BACKUP

If you need an automated backup solution for your MySQL databases, you can use a Windows batch file to perform the above mysqldump commands and then set the batch file to run automatically in Windows Task Scheduler. If you also need an automatic online backup solution, you can use SecureBackup™. Simply tell SecureBackup™ where your database backup files are located in one of your backup jobs. I also want to mention that the *.SQL backup files created above are really just text files and can be quite large. SecureBackup™ will automatically compress these files during the backup process and significantly reduce not only their size, but the time it takes to backup these files over the Internet. They will also be encrypted with 256-bit AES encryption.