Preservation of data is an essential part of any business. Losing a database, such as a customer list, could effectively be the end of a company. The primary way to protect your data is to regularly back it up.

In this article, we will explain how to automate regular backups for MySQL on Windows. If you use Linux, check out this article: How to Automate MySQL Database Backups in Linux.

What a Backup Process Should Include

Data protection is not only about creating a backup file. After the backup is created, it must be compressed to save storage space. It is also recommended for additional protection to encrypt the backup file with symmetric encryption (but be sure not to misplace the key). You will then need to transfer the compressed and encrypted backup to storage on another server or, preferably, to the cloud. It is also necessary to ensure that old backups are removed from the storage. Otherwise, the storage will overflow over time.

This process needs to be configured, but if something goes wrong you need to have ways of knowing about it. Therefore, you must have a notification process in place.

Thus, the backup process should include the following steps:

1. Backup creation
2. Compression
3. Encryption
4. Transfer to storage on another server or the cloud (AWS S3, Azure, OneDrive, etc.)
5. Removal of old backups
6. Notifications about backup results

How to Create a .bat Script

A script is the basic automation tool in any operating system. And although Windows is primarily interface-oriented, it is possible to create a .bat file that can follow almost all the steps described above.

Preparing to Create a Script

We will need two utilities to cook up a script:

1. Backup creation utility

When installing SQL Server on Windows, the go-to utility for creating backups is installed too – mysqldump. The utility is located in the same directory as MySQL Server.

To make your experience with MySQL Server more pleasurable, add the directory with mysql utilities to Windows environment variables. Click here to find out how to do it.

mysqldump is a straightforward utility. To back up an entire server, run this command:

mysqldump --user root --password --all-databases > all-databases.sql

2. Compression and encryption utility

By default, Windows does not have a command-line utility for compressing and encrypting files, so you need to download and install it yourself. Most suitable for this task is 7z.

This utility is very easy to use. To compress and encrypt a file, you need to run the command (replace %password% with your password):

“C:\Program Files\7-Zip\7z” a -p%password% archive.zip backup.sql

Simple Script for Creating, Encrypting and Transferring Backups to a Network Folder

Below is a simple script for creating a backup of all databases on MySQL Server, compressing it and copying it to a network folder. The result of executing this script is written to the Windows Event Log.

The data necessary for authorization to MySQL Server and the network folder are indicated at the script’s beginning. You also need to specify the path to mysqldump and to 7z.

rem credentials to connect to mysql server
set mysql_user=root
set mysql_password=***********
rem archive password
set encryption_password=***********
rem Credentials to connect to network folder
set network_folder=\\192.168.1.1\Elements
set network_user=192.168.1.1\kevin
set network_password=***********
rem backup storage period (in days)
set max_keep_days=1
rem path to backup compression utility
set seven_zip_path=C:\Program Files\7-Zip\
rem backup file name generation
set backup_name=all-databases-%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%-%time::=.%
rem backup creation
mysqldump --user=%mysql_user% --password=%mysql_password% --all-databases >> %backup_name%.sql
if %ERRORLEVEL% neq 0 eventcreate /ID 1 /L APPLICATION /T ERROR /SO mysql-backup-script /D "Backup failed: error during dump creation" && exit

rem backup compression
"%seven_zip_path%7z" a -p%encryption_password% %backup_name%.zip %backup_name%.sql
if %ERRORLEVEL% neq 0 eventcreate /ID 1 /L APPLICATION /T ERROR /SO mysql-backup-script /D " Backup failed: error during archive creation" && exit

rem delete temporary .sql file
del %backup_name%.sql

rem create temporary network drive for file transfer
net use N: %network_folder% /u:%network_user% %network_password%
rem copy backup to sorage
robocopy %backup_name%.zip "N:\" /e /maxage:3
if %ERRORLEVEL% neq 0 eventcreate /ID 1 /L APPLICATION /T ERROR /SO mysql-backup-script /D "Backup failed: error during file transfer to storage" && exit
rem obsolete files from storage
ForFiles /p "N:\" /s /d -04 /c "cmd /c del @path & echo Deleted old: @path"
rem disconnect network drive

net use N: /delete
rem local backup copy

del %backup_name%.zip
eventcreate /ID 1 /L APPLICATION /T INFORMATION /SO mysql-backup-script /D "Backup successful"

Running the Script Regularly

After the script for the backup is created, it should be scheduled for regular execution.

The main way to schedule any process to run regularly in Windows is the Windows scheduler. Below are the instructions on how to schedule a regular run of the backup script:

1. Press win+r and enter taskschd.msc.
   
2. Select Create Basic Task on the right panel.
   
3. Follow the steps in the wizard. On the Action tab, select “Start a Program” and specify the path to the backup script.
   
4. After clicking the “Finish” button, a task will be created that will be regularly launched at the specified interval.
   

Backup Restoration

You can restore data using the mysql utility. It is located in the same directory as mysqldump

mysql --user root --password mysql < all-databases.sql

Summary

Creating a .bat script to run regular backups is a simple solution that covers the bare minimum in the process of good backup creation. However, Windows is an operating system that focuses on customizing processes through a user interface. As a result, shell scripts for Windows are underdeveloped, and it is usually difficult to implement a good and reliable solution based only on scripts.

Using GUI Solutions

Unlike Unix systems – Windows is focused on user interaction through a graphical user interface.

Below we will consider utilities with a graphical interface that allows you to perform all the steps necessary to create a good backup of MySQL databases on Windows.

SQLBackupAndFTP

SQLBackupAndFTP is a popular backup solution for an SQL Server. However, this program allows you to automate backups for MySQL Server too.

This utility has a straightforward interface, but also boasts many useful features such as backup encryption, email notifications and automated scripts before and after creating a backup. It also allows automated data recovery.

Most importantly, this program allows you to send a backup to a folder, FTP server or cloud storage. The complete list of supported destinations is: local/network folder/NAS, FTP server, Amazon S3, S3 Compatible (custom, Google Cloud, Wasabi, Aruba), Dropbox, Google Drive, OneDrive, OneDrive for Business, Azure Storage, Box, Backblaze B2, and Yandex.Disk.

To create a backup job in SQLBackupAndFTP follow the steps below:

1. Create a backup job by clicking Jobs > Add Backup Job
   
2. Establish a connection with your MySQL Server.
   
3. Select the databases you want to back up.
   
4. Specify the location where the backups will be stored. In this case, it’s Google Drive. Select Google Drive from the list provided and establish a connection.
   
5. Create a backup schedule.
   
6. Enter your email to receive fail/success notifications.
   

To recover data, follow these steps:

1. Create a Restore Job by clicking Jobs > Add New Restore Job
   
2. Specify the location where the backups will be stored (for example, Google Drive) and establish a connection.
   
3. Choose a backup that should be restored.
   
4. Connect to your MySQL Server.
   
5. If necessary, set up email notifications.

Click “Run Now” to start.

Also, you can restore your backups in just several clicks. Choose the backup you need from the “History & Restore” section, click the three dots and press “Restore from Backup…”, and then follow the steps.

Summary

SQLBackupAndFTP was developed over 15 years ago and has since been installed over a million times. It is a reliable data protection solution. A simple interface and an extensive list of supported cloud storage save database administrators a lot of time.

SqlBak

If you need to have regular MySQL database backups for several servers, then a solution that needs to be configured on the server itself may not be very convenient.

Another tool that covers all the steps required to create a good backup is SqlBak. You only need to install a small application in which you will need to specify credentials to connect to the MySQL database. All other settings, logs, and reports are located on the site, which is very convenient if you need to administer several servers simultaneously.

SqlBak can save backup in the following locations: local/network folder/NAS, FTP server, Amazon S3, S3 Compatible (custom, Google Cloud, Wasabi, Aruba), Dropbox, Google Drive, OneDrive, OneDrive for Business, Azure Storage, Box, Backblaze B2, and Yandex.Disk.

To set up an automated MySQL Server backup on Windows using SqlBak, follow the steps below:

1. First of all, you need to install SqlBak on your computer and establish a connection to the MySQL Server. You can find more information on the Download page.
   
2. Go to the Dashboard page, click the “Add new job” button, check the settings and click “Create Job.”
   
3. Select the databases you want to back up.
   
4. Set up a location where your backups will be stored, for example, Google Drive.
   
5. Set up a backup schedule.
   
6. Enter your email for backup fail/success notifications.
   

You can restore your data in just three clicks. Select the backup you need from the backup history section, click the “Restore” button, and follow the steps.

Summary

SqlBak can run on both Windows and Linux. The advantages of SqlBak are revealed when connecting multiple MySQL Servers. The service simplifies the administration of multiple MySQL computers and can restore a backup to another linked server. That makes it very easy to transfer databases between servers and set up automated recovery on the development server.

Bottom Line

Even though the MySQL Server does not have built-in backup automation mechanisms, this task can be solved in many other ways: from writing a shell script to using utilities with a graphical interface such as SqlBak or SQLBackupAndFTP.

The main purpose of a backup is that you can use it to restore your data. Never store your backup on the server where you keep your database, keep your encryption key safe and be sure to test restoration on another server from time to time.