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
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.
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.
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.
2 thoughts on “Backing up MySQL databases on Windows from the command line”
Good site you have got here.. It’s hard to find good quality writing like yours these days.
I truly appreciate individuals like you! Take care!!
Hi, just wanted to say, I loved this blog post. It was helpful.
Keep on posting!