How to schedule an automatic backup of MySQL Databases

A. Create a scheduled backup (Recommended for Administrators)


A very helpful page to do this is at :https://www.redolive.com/utah-web-designers-blog/automated-mysql-backup-for-windows/

In summary a batch script was used to:

  • Backup all MySQl databases, including all newly created ones automatically

  • Create an individual .sql file for each database

  • ZIP all the .sql files into one zip file and date/timestamp the file name to save space
  • Automatically delete MySQL backups older than n days (set to however many days you like)
  • FTP your backup zip to a remote location
  • Highly suggest you also setup a scheduled task to backup your MySQL directory and your new backup folder to an off site location

Steps on how to use the batch script:

A. If BMS was installed in default folder (C:\BMS4)

  1. Download this file MySQLBackups.7z.
  2. Unzip file in C:\ .  This will create a new folder C:\MySQLBackups. This folder contains the script and the free zip application.
  3. Run mysql_config_editor set --login-path=local --host=localhost --user=root -p --port 43306 on command Prompt.  This allows executing MySQL command without having the password visible in the script file.
  4. Enter the password for the root user.
  5. Finally create a scheduled task in windows to run the batch file on a schedule. 

B. If BMS was installed on a different folder

  1. Download the batch script file here.
  2. Open the batch file with any text editor (Notepad, Editplus)
  3.  Modify the SETTINGS and PATHS section of the file.

:: SETTINGS AND PATHS
:: Note: Do not put spaces before the equal signs or variables will fail

:: Error log path - Important in debugging your issues
set errorLogPath="c:\work\MySQLBackups\backupfiles\dumperrors.txt"

:: MySQL EXE Path
set mysqldumpexe="C:\BMS4\infrastructure\mysql\bin\mysqldump.exe"

:: Error log path - Use the full Universal Naming Convention if using a mapped network drive

:: No quotation marks
set backupfldr=c:\work\MySQLBackups\backupfiles\

:: Path to data folder which may differ from install dir
set datafldr="C:\BMS4\data"

:: Path to zip executable
set zipper="c:\work\MySQLBackups\zip\7za.exe"

:: Number of days to retain .zip backup files
set retaindays=5

:: DONE WITH SETTINGS

  • Set the format for the backup filename.  The original script uses the format:yyyy-mm-dd-m-s
  • Set the BMS MySQL data directory.  Default data directory is C:\BMS4\data.
  • Set the path of the mysqldump.exe to match the BMS install path.  Default directory is C:\BMS4\infrastructure\mysql\bin.
  • Set the path of the backup directory.  This will contain the database backups. Important: This must not contain any quotation marks.
  • Set the path of your zip application.  A free zip application can be downloaded here:http://www.7-zip.org/download.html.  This is optional.
  • Update the path where the backups will be saved and then deleted once zipped
  • Set the number of days to keep backups, using the win program  “Forfiles” for this, sample file is set to 5.

If backupfldr= is using a mapped drive to access the network, the scheduled task will not run. For example, instead of using Z:\data\, you should use \\machinename\share_name\data\ (the Universal Naming Convention, or UNC)


To find out the UNC of a mapped drive, on Windows Server command prompt:

C:\net use

4.  Run mysql_config_editor set --login-path=local --host=localhost --user=root -p --port 43306 on command Prompt.  This allows executing MySQL command without having the password visible in the script file.

5. Finally create a scheduled task in windows to run the batch file on a schedule. 


C. Backup script with robocopy for retaining weekly and monthly backups

  1. Download the batch script file here.
  2. Open the batch file with any text editor (Notepad, Editplus)
  3.  Modify the SETTINGS and PATHS section of the file.

:: SETTINGS AND PATHS
:: Note: Do not put spaces before the equal signs or variables will fail

:: Error log path - Important in debugging your issues
set errorLogPath="c:\work\MySQLBackups\backupfiles\dumperrors.txt"

:: MySQL EXE Path
set mysqldumpexe="C:\BMS4\infrastructure\mysql\bin\mysqldump.exe"

:: Error log path - Use the full Universal Naming Convention if using a mapped network drive

:: No quotation marks
set backupfldr=c:\work\MySQLBackups\backupfiles\

:: weekly folder path
set wkfldr="c:\MySQLBackups\wkfldr"

:: ARCHIVE folder
set archfldr="c:\MySQLBackups\archive"

:: Path to data folder which may differ from install dir
set datafldr="C:\BMS4\data"

:: Path to zip executable
set zipper="c:\work\MySQLBackups\zip\7za.exe"

:: Number of days to retain .zip backup files
set retaindays= 30

:: DONE WITH SETTINGS

  • Set the format for the backup filename.  The original script uses the format dow-dd-mm-yyyy.
  • Set the BMS MySQL data directory.  Default data directory is C:\BMS4\data.
  • Set the path of the mysqldump.exe to match the BMS install path.  Default directory is C:\BMS4\infrastructure\mysql\bin.
  • Set the path of the backup directory.  This will contain the database backups. Important: This must not contain any quotation marks.
  • Set the path for the weekly backup will be stored. In the script, it is stored at c:\MySQLBackups\wkfldr
  • Set the path for the monthly backup will be stored. In the script, it is stored at c:\MySQLBackups\archive
  • Set the path of your zip application.  A free zip application can be downloaded here:http://www.7-zip.org/download.html.  This is optional.
  • Update the path where the backups will be saved and then deleted once zipped
  • Set the number of days to keep backups, using the win program  “Forfiles” for this, sample file is set to 30.

Steps to add a task (that will run mysqlbackup.bat) in the Task Scheduler (Windows Server):


  1. StartTask Scheduler. Control Panel > System and Security > Administrative Tools > Task Scheduler.

  2. On the left panel, right-click on Task Scheduler Library then New Folder...

  3. Name the folder as BMS. Click on the BMS folder to highlight it.

  4. In the Actions Pane, click Create Task .

  5. On the General tab of the Create Task dialog box, enter a name for the task. Name the task as bms_mysqlbackup.

  6. Fill in or change any of the other properties on the General tab. IMPORTANT: Under security options, remember to choose “Run whether user is logged on or not otherwise it will fail.

    For more information about these properties, see General Task Properties.

  7. On the Triggers tab of the Create Task dialog box, click the New… button to create a trigger for the task, and supply information about the trigger in the New Trigger dialog box. For more information about triggers, see Triggers.

  8. On the Actions tab of the Create Task dialog box, click the New… button to create an action for the task, and supply information about the action in the New Action dialog box. For more information about actions, see Actions.

  9. (Optional) On the Conditions tab of the Create Task dialog box, supply conditions for the task. For more information about the conditions, see Task Conditions.

  10. (Optional) On the Settings tab of the Create Task dialog box, change the settings for the task. For more information about the settings, see Task Settings.

  11. Click the OK button on the Create Task dialog box.


Below is the screenshot of database backups while the script is running:

Optional : Test your batch file on a dummy directory.  You’ll see the backup directory fill up with .sql files, then a timestamped zip file is made, and the directory is cleared.  Put some files older than 5 days in there and they will be wiped at the end.

B. Make a cold backup (Recommended for standalone users)

To backup the BMS database:

  1. Stop  MySQL Service.  To Stop MySQL service, go to Control Panel\System and Security\Administrative Tools.


  2. Look for BMSMySQL service and click stop.



  3. Create a Backup folder where you want to store your backup databases.  Copy the BMS data folder (C:\BMS4\data) to your Backup folder.
  4. Optional but highly recommended to compress the backup data folder to save on data space.
  5. Start the MySQL service.



To restore (or use an older version) BMS databases:

  1. Stop MySQL service. See above instructions on how to stop MySQL service.
  2. Backup the current BMS data folder.  See steps above on how to backup BMS databases.
  3. Copy the data folder from your Backup folder into the BMS folder (C:\BMS4).  
  4. Start the MySQL service.


Related file:

How to Set MyLogin(1).docx