Easy MySQL Backups on a Windows Server
Posted on February 27, 2008, under Code Snippets, How To, IIS, Software, Windows.
During the re-launch of BlueCrestStudios we moved to a Windows server. It was part of our initiative to take things back to our roots, and make things simpler. Because we run a fair amount of PHP applications we went with MySQL as our database. MySQL comes with a great little application called "mysqldump" which simply enough dumps your data to a SQL file. This little tool, combined with the free and great 7Zip makes for simple and great database backups. It’s best to dump your database onto a separate drive then where the data is stored, offers a little redundancy.
Now before we get started, I know some of you are thinking there is no need for this if you hosting company does backups. True, yes any good hosting company should do regular backups of all your data. However, I find it good to have a dump of our databases for that oops situation, and not involve the hosting company if we need to recover a database or table or row. In addition, you can schedule dump every other hour, or more regularly then your hosting company does backups.
So here is our script, let’s see what we are doing here:
1: set ARCHIVE=E:\Backups\MySQL\%DATE%.zip
2: set FILE=E:\Backups\MySQL\Backup.sql
3: DEL %ARCHIVE% /f /q
4: "C:\Program Files\MySQL\bin\mysqldump.exe" --all-databases --user=YOURMYSQLUSER --password=YOURMYSQLPASS > %FILE%
5: "C:\Program Files\7-Zip\7z.exe" a -tzip %ARCHIVE% %FILE%
6: DEL %FILE% /f /q
Basically we are dumping the database daily here, to an archive that holds the day of the week. The variable "ARCHIVE" holds to archive for today that will be created. The "FILE" variable holds the temporary dump file before we compress it, it is deleted after the compression just to clean up after ourselves.
- Line 3, we are deleting the current backup archive just incase.
- Line 4, we are doing the actual dump. You need to set your mysql user and password , a note do not use root!
- Line 5, we are doing the actual creating of the archive. SQL dump files compress very well, so this is a space saver step.
- Line 6, we are deleting the temporary backup file as mentioned above.
The tactics above can be enhanced greatly, as you can do dumps based on database, time, and get very elaborate on this. As an extra step I am using my Windows Home Server to download the dump nightly via FTP. Simple, elegant, fairly robust, and useful.
Popularity: 8% [?]
