Solved

MySQL backup script automation on Windows server

Posted on 2013-02-03
4
461 Views
Last Modified: 2014-02-02
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?
0
Comment
Question by:Reece Dodds
  • 2
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38858952
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
.\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.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38858966
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.
0
 
LVL 7

Author Comment

by:Reece Dodds
ID: 38932342
I will try an adoption of this script and let you know how I get on.  Thanks
0
 
LVL 7

Author Closing Comment

by:Reece Dodds
ID: 39828377
Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
A procedure for exporting installed hotfix details of remote computers using powershell
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now