Batch Files and Best Practices: Backup mySQL on Windows Server Automagically

Objective:

• Create a limited mySQL user account (e.g. Read-Only) to use in the batch file to follow the The Principle of Least Privilege methodology
• Place multiple mySQL database backups in a specified folder
• Add a timestamp to the mySQL dump file to easily distinguish between versions
• Have multiple mySQL databases get backed up at a specified time

One of the "best" best practices is to backup any important data that you do not want to loose. This is especially important when you are storing a bunch of other people's data in a database. Problem is, if it is something that we have to think about, it just isn't going to happen. Luckily, all modern operating systems have the ability to run user generated scripts to help automate this task.

In the Windows world, this can be accomplished by using Batch Files (commonly has the .bat file extension). By creating/modifying/editing a simple text file, you can "tell" the computer what you want it to do. However, if you need to do this on a Internet accessible server, some best practices come into play.

*In this writeup, I am using Windows Server 2003 and a mySQL database.

The principle of least privileges states that you should only give a user or a process just enough rights to get the task at hand done. This applies to user scripts or batch files as well. If your server gets compromised, then the script has an additional layer of protection built into minimizing it as an attack vector.

When backing up a mySQL database in a script, you can get the task accomplished with only giving a limited set of user permissions. You do not want to be running as root or as a higher level user inside your script as this is a plain text document and it typically has your server credentials in it. For our purposes, our limited user needs the following permissions to run our backups:

• Select
• Reload
• Show Databases
• Lock Tables

Notice, when the limited user is setup in this fashion, they cannot Create or Delete new databases or Drop existing tables. Once we have setup our limited user, it is time to move onto the batch file (see snippet below).

In order for the batch file to be able to connect to our various mySQL databases, we need to provide it with our credentials, paths to where we want to save the files and paths to where our database is installed at (see the Set some variables section in the example batch file). Since these items can be reused or may need to be changed in the future, setting them up as variables at the beginning of the file makes it easy to update.

In a batch file, a variable is created when you use the word set and follow it by a name. To get the example batch file to run, the follow items need to be updated:

• Directory where you files are to be stored needs to be set (e.g. "C:\Documents and Settings\fictionaluser\Documents\")
• The path to the actual mySQL database installation (e.g. "C:\mySQL")
• The path to the mySQL database data directory (subfolder of the mySQL directory. e.g. "C:\mySQL\data\")
• The database username
• The database password

Again, to help distinguish on database dump file from another, a timestamp is typically added to the file name. This is generated from the system clock where the batch file is running at. In the example, the timestamp is set to add it in Year/Month/Day_Hour in the Create the filename suffix section. If you would like it to output differently, change that section to the desired format. Also, you will need to limit your use of special characters because they are not always allowed in filenames.

Timestamp Key:

• %yy = year
• %mm = month
• %dd = day
• %hh = hour
• %ii = minute
• %ss = second

The remainder of the script runs through and creates a directory for each of the databases to store the dump file in. It uses the mySQL data directory (which was set earlier) to find all the databases that the have been created on the system. If you add a new database later on, you don't have to worry about coming back to the script and tweaking it; the batch file just handles it automatically. If you have any questions about this, feel free to leave them in the comments.

The last piece we need is to have the batch file run at specific intervals. If we were on a Linux/Unix based server, we could easily set up a cron job. One way to accomplish this in Windows Server is to set up Scheduled Task. The software starts a wizard where you tell it which program you want to run (e.g. our batch file, example.bat), the time you want this task to perform, and the username you want it to run under (some processes need sufficient privileges in order to run a script). After it is setup, then your database will automagically be backed up at the times you specified!

Example Batch File

[Editor's Note] - This script was originally found in the comments of the mySQL Reference manual for the mysqldump process