The backup process should include the following steps:
- Backup creation
- Compression
- Encryption
- Transfer to storage on another server or the cloud (AWS S3, Azure, OneDrive, etc.)
- Removal of old backups
- 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
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:
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.
.gif)
0 Comments