Backing up MySQL databases on Windows from the command line

I’ve been working on a bunch of internal projects at my company and had the need for a previous version of the database. While I could certainly have restored from a backup of the server, it got me thinking about how slick it would be to have mysql backups performed at certain intervals (daily, weekly, etc.) that I could quickly reference in the in event it was needed quickly.

Windows Batch File

The cheap nba jerseys first step to perform is to create a simple batch file (I called mine mysql_backup.bat).

Once that file is created, the proper instructions are needed to invoke the mysqldump feature native to MySQL.

The entire command will end up looking like this:

mysqldump  we  -u "username" -p "password" > output_file.sql

mysqldump

Depending on where you intend to run your batch file from, you may wish to just specify the actual path to the mysqldump executable. For my particular installation, the mysqldump command was loaded wholesale nfl jerseys at: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump. In the event ?????? the path has spaces, it will need to be quoted in the batch file.

Username/password configuration

It is important to note that the username and password must also be included in the command line string.

-u username -p password

Database selection / output file name and location

After the username and password strings, the database name along with the output destination should be specified.

databasename > mysql_backup.sql

In my case, I wanted to actually write my backup files to a network share on another server. This is easily accomplished. Just pass in the complete path:

databasename >  2010  \\fileserver\share\backups\mysql_backup.sql

Complete Command String

So what does the complete command string look like to run a MySQL backup from a batch file in any location?

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" -u username -p password databasename > \\fileserver\share\backups\mysql_backup.sql

What about backup file encryption?

Windows comes with a pretty slick tool called Cipher. Using this tool, you can actually encrypt the files cheeses when they are backed up. Keep in mind that the instructions below only show the basic methodology for enabling encryption on the file. Most likely you would want to generate a key and store in a secure location, etc. The cheap nba jerseys command string to run a backup and initiate file encryption is as follows:

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" -u username -p password databasename > \\fileserver\share\backups\mysql_backup.sql && cipher /e /a \\fileserver\share\backups\mysql_backup.sql

The command above will call the cipher command (using /e – encrypt and /a – for a particular folder or file) on a particular file that should be encrypted.

Running the batch file

Depending on the options listed above, save the contents of the command string into the batch file you wish to run. Save the batch file in a location on the machine where MySQL is installed. If you have included the entire path to the mysqldump executable, you should be able to run it from the location where you save it. Once you have is saved the file (with a .bat extension), double-clicking on it will actually run the script. Note: If you want to edit the batch file, simply right-click and select edit or open it with your favorite text editor.

Scheduling

Once you have a batch file that is working correctly, you will most likely want to schedule it to run on a routine basis. Windows Task Scheduler works well. Simple open Task Scheduler on Windows, click Create Task. On the Actions tab, click New… and then select Start a program from the dropdown menu. Simple type in the path to your batch file you have created and you are just about done! Simply select the schedule you want it to run on and then save the scheduled task. You Benefits can right-click on it and run it immediately to ensure it has been configured correctly.