Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL backup script automation on Windows server

Posted on 2013-02-03
4
Medium Priority
?
502 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 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 143

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
Suggested Courses

636 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