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.
The alternative is to call the mysql and mysqldump executables using the full path to them (e.g F: wamp bin mysql mysql5.1.36 bin mysql and F: wamp bin mysql mysql5.1.36 bin mysqldump). The best way to create and automate backups of MySQL databases is to: 1. Use the Windows Task Scheduler to automatically run a backup task every day or week.
What a Backup Process Should Include
This is standard on many POSIX-type installations (Linux, BSD, OS X, etc.) but not present by default on Windows. – tadman Dec 10 '14 at 19:39 I have Gzip.pm under xampp library. Any process i have to install gzip? – razerer Dec 10 '14 at 19:43. Dec 10, 2014 This is standard on many POSIX-type installations (Linux, BSD, OS X, etc.) but not present by default on Windows. – tadman Dec 10 '14 at 19:39 I have Gzip.pm under xampp library. Any process i have to install gzip? – razerer Dec 10 '14 at 19:43. I am trying to install mysqldump on a Azure hosted Ubuntu VM. During the build process I attempt the following command: sudo apt-get update sudo apt-get install -y -no-install-recommends mysql-client-8.0 This results in the following error: Unable to locate package mysql-client-8.0.
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:
- 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:
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:
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):
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.
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.
- Select Create Basic Task on the right panel.
- Follow the steps in the wizard. On the Action tab, select “Start a Program” and specify the path to the backup script.
- 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
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:
- Create a backup job by clicking Jobs > Add Backup Job
- Establish a connection with your MySQL Server.
- Select the databases you want to back up.
- 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.
- Create a backup schedule.
- Enter your email to receive fail/success notifications.
To recover data, follow these steps:
- Create a Restore Job by clicking Jobs > Add New Restore Job
- Specify the location where the backups will be stored (for example, Google Drive) and establish a connection.
- Choose a backup that should be restored.
- Connect to your MySQL Server.
- 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:
- First of all, you need to install SqlBak on your computer and establish a connection to the MySQL Server. You can find more information here: https://sqlbak.com/download
- Go to the Dashboard page, click the “Add new job” button, check the settings and click “Create Job.”
- Select the databases you want to back up.
- Set up a location where your backups will be stored, for example, Google Drive.
- Set up a backup schedule.
- 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
Install Mysqldump Windows Xp
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
Install Only Mysqldump Windows
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.