How to schedule remote mysql backups on windows

Question

Hi guys and gals!

I am looking for a smart way to take care of scheduling backups on a remote Mysql Unix/Linux server from my own personal Windows XP computer.

Is there any (free if possible, preferable of course) software that i can schedule that logs in to a mysql database and downloads a complete backup?

Would also need an alternative that might handle backups for databases that I don't have remote access to.

Thanks!

Answer 1

Have you looked at pmbp?

It's web-based (PHP) and among the other features, it lists:

• backup of one or several databases with or without data, table structure, ...
• three types of compression (no compression, gzip or zip)
• scheduled backups (by a small PHP script which must be included in an existing PHP script)
• interface for managment of the backups (viewing, restoring, downloading, deleting)
• backup directly onto FTP server and sending of backups by email
• platform independent - only webserver and PHP needed to run e.g. on MS Windows, Linux or Mac
• shell mode (to use manually or by cron script)
• backup of whole file directories (on a FTP server).
• backup databases from different accounts on several MySQL servers
• one installation can be used for all MySQL users of one MySQL server (used by webhosters)
• highest security through two alternative login methods (HTTP or HTML authentication)

Answer 2

I'm usually doing this from a Linux box, but you can do the same if you have Cygwin (or just the SSH executable) on your Windows machine:

ssh [email protected] "mysqldump dbname | gzip -9" > localfile.sql.gz

Answer 3

I'm supposing you have permission in your external host for these operations. That said, you have two approaches:

Using the mysqldump of your Windows MySql installation and a bat script for scheduling

If you have wamp/lamp/etc installed on your windows machine, you can use mysqldump in a bat file and set windows scheduler to call it everyday at 0:00 AM.

Create a .bat file containing: (Make sure that your output directory is writable for this task in Win Vista and 7):

set DATET=%date:~-4%_%date:~7,2%
"c:\xampplite\mysql\bin\mysqldump" --host="YOUR_HOST" --user="YOUR_USER" --password="YOUR_PASSWORD" YOUR_DB > "c:\"backup_%DATET%_YOUR_DB.sql
pause

...and set a task in your windows scheduler to call this bat everyday.

If you need additional parameters to your backup, don't forget to check mysqldump reference.

Using a "full-blown" software based backup solution

Check Mysql's solution directory. The only free solution I know of this list is the app from topsoft but I haven't tested it. You could check Sourceforge as well.