The following is an alternate procedure to backup MySQL databases, as opposed to the now legacy MySQL Administrator tool.

This method allows you to specify one or all the MySQL databases on a given server, creating a full time stamped backup. 7Zip is also optimized to zip the created backup, utilizing the timestamp and allowing for the process to clean up the backup folder on a configurable timeframe, i.e., delete all backups that are older than 14 days.

--------------------------------------------------------------------------------------------------------------

In order to setup the scheduled task, the attached files are required and will need to be placed on the relevant server/s.

Setting Up The Batch File

1) Due to the script optimizing the Server date/time, it is key to use the correct script. Currently there are two versions: UK and USA. Using the wrong script will cause the backup process to fail/not create the backup correctly.

2) Once you've decided which script iteration to use, edit the batch file in Notepad++ (or your preferred text editor) and navigate to line 28. The variables defined between lines 28-53 all need to be set to your server specific settings.

NOTE: Should there be any spaces found in the folder path, it is required that the value is configured with quotation marks. 

3) Before setting up the scheduled task, it is advised that you test the script through CMD. To carry this out:

i) Open up CMD as an administrator

ii) Change the directory to the root of where your scripts are stored on the server and enter the name of the batch file:

Pressing Enter here will kick off the backup process and outline any issues encountered.

If all has gone well, there should be a zipped folder in your backup directory. Should the size of the zip be 0KB, then there has been an issue. Unzip the folder and check out the file (If there is one) that has been zipped.

NOTE: To find out more information on the issue, scroll up within the CMD window and look for error messages. The most common error is related to the path not being found. Therefore, you should check all of the paths entered.

Should you still have issues, bypass the script by performing a MySQLDUMP straight from CMD. However, use the same parameters outlined in the logs of the batch file. MySQLDUMP may outline a different error pointing to where the issue is stemming from.

5) Repeat step 3 for all necessary databases

Setting up the Scheduled Task

Once you're happy that the script is performing correctly, open up Task Scheduler.

1) To make matters easier, I have exported an example Task Scheduler task in the following Zip: MySQL Task Scheduler Backup.zip. See MySQL Task Scheduler Backup\MySQLBackups\Example Task Scheduler Task.

2) Create an aptly named folder on the left-hand window, helping to organise the tasks. Then click Import Task to import the example task.

3) Areas to Change:

i) Ensure the name is changed to describe the task

ii) Select Change User or Group and enter an admin user who will be used to run the task in the background.

iii) Within the Triggers tab, change the trigger to start from tomorrows date and select the relevant running time.

iiii) Within the Actions tab, double-click the pre-defined action and select Browse. Change this value to the location of the newly created batch file.

iiiii) Once happy, select OK and enter the password when prompted.

iiiiii) In order to ensure the task is set up correctly, right-click the task in the list and select Run. Should a new zipped backup be present in the backup folder, then the task is working as expected. If it is not, give the task settings another check and try again.

NOTE: This script has been tested on two customer environments, with beneficial results on both. However, the script was created primarily from a 3rd party so please use with care. Any improvements to the script/process are much appreciated.