Automated Local Backup of a Remote MySQL Database
I always like to take backing up into my own hands, and a database is potentially the most important thing on your server next to only the code driving it all, which in nature typically has a local copy anyways.
Contents
The local machine is a Windows Vista workstation and the remote is a webserver running LAMP.
The security of the locally backup database file is only as secure as the machine it-self. If you're not sure what this means, do not backup sensitive data using this method.
Step 1: mysqldump and the remote script
mysqldump is a program which can dump the contents of a MySQL database. It should be used with a MySQL user which has read access to all of the databases on your server that you wish to backup.
mysqldump will be executed by PHP on the remote server and the output will be passed back over standard http.
Update the variables in the above script and save as index.php. Upload this file to it's own directory on your server, for example /www/sqlbackup/.
/www/sqlbackup/ should be configured as a password protected directory to prevent access by anyone to it.
Step 2: wget, rar and the local script
Download wget into your %windir. This is a command line utility that can be used to download remote files and pages from http and ftp servers.
Optionally, install WinRAR to add compression.
wget --http-user=... --http-password=... --output-document=C:\Users\Joe\Documents\Scripts\sqlbackup\backup.txt http://www.yourserver.com/sqlbackup/ "C:\program files\winrar\rar.exe" u -dw -y -p... backup.rar backup.txt
The first line uses wget to download the output from our remote script to backup.txt. The user, password, remote, and local locations all need to be changed to suite your configuration. This should be saved into script.bat in it's own directory on your local workstation, example C:\Users\Joe\Documents\Scripts\sqlbackup\.
The second line compresses the downloaded file into backup.rar and sets the password of the rar to whatever follows the -p argument. Note that the rar password ads very minimal security due to the script, which contains the password, being in the same directory. This line is optional and can be omitted.
Run backup.bat to make sure that everything is working fine.
Step 3: Scheduling
In a command window, enter at 13:00 /EVERY:M,T,W,Th,F,S,Su C:\Users\Joe\Documents\Scripts\sqlbackup\backup.bat
This will cause the above local script to execute everyday at 1:00PM everyday of the week.
There's nothing else to it! Keep in mind, once again, that this will make any of the dumped MySQL contents as insecure as your computer is!