MySQL backup script automation on Windows server

Hi all

I've done a lot of looking, but can't find something that covers all bases of what I want.

I'd like a backup script that I can schedule to run nightly to backup my server's MySQL databases.

It's Windows Server 2008 R2, IIS7.5, MySQL v5.1, PHP v5.4.

There is about 8 databases, but I'd like them backed up to individual files with the date in the file name.  eg.  database1-20130204.sql

I've read a little about mysqldump but have no idea how to make it dump the databases to individual files at the same time (i.e one script rather than a different one for each database for each day of the week).

I'd also like the dump to be on a different HDD...  eg. D:\MySQL-Backups\dumps.

New databases are added or some deleted somewhat frequently, so I'd prefer not to have to adjust the backup script each time I make a change...

Can the script create a logfile about what databases were dumped that can be emailed to me using blat.exe or something?
Reece DoddsAsked:
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I would do like this... create a backup.bat file like this:
"C:\Program Files (x86)\MySQL\MySQL Server 4.1\bin\mysql" -s  --user=username --password=password --skip-column-names --execute="select concat('mysqldump --where \'1=0\' ', schema_name ) from information_schema.schemata;" > backup_script.bat

Open in new window

you adjust the path information, the "where" condition (to be removed) add other options to the mysqldump as you need, and that's it.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as explanation. the first line connects to the mysql database, and "builds" the second part by querying the database for all the databases in the mysql instance.
second line is running that script.
Reece DoddsAuthor Commented:
I will try an adoption of this script and let you know how I get on.  Thanks
Reece DoddsAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.