This article describes how to schedule automatic backups of the ESS database in MySQL on Windows using the mysqldump utility.
This article is intended for ESS system administrators or database administrators responsible for the maintenance of the ESS database.
Create a new batch file (*.bat) using your favorite text editor, and paste in the following text:
@echo off echo Executing mysqldump... "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" -e -q -uusername -pc3p0 -hlocalhost --single-transaction --result-file="%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.sql" -B database echo Culling old dumps... forfiles /s /m *.sql /d -7 /c "cmd /c del /q @path" echo Complete.
- Prevents command output from being printed to the console (stdout).
- Prints friendly informational text about what we're doing.
- Creates a mysqldump output file. Has components:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe"— The absolute path to mysqldump.
-e— Use extended insert to reduce output file complexity and size.
-q— Return rows one at a time instead of buffering large tables in memory to complete the operation faster.
-uusername— The username for authenticating to the database server.
-pc3p0— The password for authenticating to the database server.
-hlocalhost— The address of the database server host.
--single-transaction— Complete the operation in a single transaction so that the output file is consistent and the operation can be completed without locking tables against concurrent use.
--result-file="%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.sql"— The name of the output file. If no path is specified, it will be created in the working directory of the batch file. This string uses Windows command-line variables to create a file with a date stamp for its name.
-B database— Database names follow. More than one may be listed here, however, they will all be contained in the same output file.
- Prints more friendly informational text about what we're doing.
- Deletes output files older than 7 days. Has components:
forfiles— The Windows command used to execute a batch operation on multiple files.
/s— Search into subdirectories recursively.
/m *.sql— Match only files of the pattern *.sql.
/d -7— Match only files older than 7 days.
/c "cmd /c del /q @path"— Execute the quoted command. Has components:
cmd /c del— Invoke the Windows command shell to run the delete command.
/q— Do not prompt for delete confirmation.
@path— Select the file matched by the forfiles command.
- Prints even more friendly information to indicate we're done.
Go to Start -> Administrative Tools -> Task Scheduler to create a scheduled task for executing the batch file on a nightly basis:
- In the Action pane, select Create Basic Task...
- Give your task a name like "Database Backup" and click Next.
- Trigger the task on a daily basis and click Next.
- Start the task beginning "today" to occur at 2:00:00 AM (or some other convenient low-load time), every 1 days. Click Next.
- Set the action to start a program. Click Next.
- Use the Browse... button to locate your batch file or enter its absolute path.
- Populate the Start in field with the directory in which you want the database backups to be created (e.g., the directory containing the batch file). Click Next, then click Finish.