Automating Backup of MySQL schemas

I tackled this topic on my newer moodle server in a couple previous posts.  My goal was to have the db automatically backed up each night.  This is the post detailing that experience.

Yesterday, I decided to do the same thing with the other schemas sitting on our dedicated DB server.  Why not? I am not sure why it took me over a month to see need for this.

If I am the admin, one of my top priorities is to have a daily backup of the DB.

We have a networking group that backs up all the servers, but that is like taking a sledge hammer to a job where a pair of needle nose pliers is needed. They are like a general MD, while I am the specialist.

As my brain descended back into this task, I read the post linked above, which helped.  The first thing I did was browse to the DB server and log in.  Using MySQL administrator, connected to MySQL and created a new user and password (the same one I used on our newer server) and assigned schema rights to each of the schemas I was planning to backup.

Then I browsed to the newer server and copied the folder with the backupDB.bat file, I figured I would just model what I did here. Then I had to look where the mysqldump.exe utility was located in the directory structure. The directory structure was different, it actually has spaces in some of the directory names.

Newer server path to mysqldump: C:\wamp\bin\mysql\mysql5.5.20\bin\

Sample of file on new server creating the backup file

C:\wamp\bin\mysql\mysql5.5.20\bin\mysqldump -u username -ppassword --result-file="c:\automatedDBBackups\backup.%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%.sql" student

Older server path to myslqdump: C:\Program Files\MySQL\MySQL Server 5.1\bin\

Sample of file on older server creating the backup file

"C:\Program Files\MySQL\MySQL Server 5.1\bin\"mysqldump -u username -ppassword --result-file="F:\dbbackups\student\backup.%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%.sql" moodle-studentmoodle

Notice the slight difference, I had to use "around the path" to the mysqldump command.  Those are necessary for DOS to ignore the spaces.  This post helped solve.

I am reminded too that when I initially solved this, I tried putting a space between the -ppassword - NO SPACE THERE. No space after the path in "" either.  DOS = do not like spaces.

In the end, I used the F drive on the old server to store the backup files, in a directory structure like

F:\dbbackups\entityname\ backup.2012-05-04.sql

I have 8 different entityname folders, all being called from the same backupDB.bat file.  Nice!

The last step is to automate the calling of the backupDB.bat file.  I will use the AT command, as before, rather than a scheduled task.

I will have to remember to periodically go to the F drive and clean up some of the backups.  There is about 10 Gig of available space - that will be eaten up pretty fast since I am backing up all 8 of the active schemas each day.