This article will outline how to perform backup and recovery of MySQL databases using Acronis Cyber Protect. This article also assumes that you have already added the machine where the MySQL database is running to the Acronis management console. See this list of articles on how to install Acronis Backup agents on various platforms and also how to add a machine to a protection plan in Acronis.
You should note that it is not advisable to perform a backup of a MySQL database if the database is still running as this might create a corrupted or incomplete backup file. It is therefore important to ensure that the database is not running during a backup operation. This can be done by closing the application that accesses the MySQL database and shutting down the database server where the MySQL database runs. This could cripple mission-critical applications, so the backup process should be scheduled for a convenient time when mission-critical applications such as point-of-sale systems are not in use, e.g. at night or during off-business hours.
Acronis provides downloadable scripts that run before and after creating a database backup. The scripts utilize the MySQL “FLUSH TABLES WITH READ LOCK” command to lock tables and records before starting the backup process, and then subsequently unlocks the database after Acronis Cyber Backup creates the backup. Using this method reduces downtime significantly as opposed to shutting down the MySQL database and server for the entire time the backup operation needs to run to completion. This article will outline how to download the scripts for Linux environments and configure Acronis to automatically run these scripts before and after each backup process.
Once you have set up your Acronis Cyber Protect console and successfully installed an Acronis agent on the machine with the MySQL database to be backed up, you need to first navigate to this page on the Acronis knowledge base. In step number two, you will see two links to download the above-mentioned scripts. One link downloads Windows scripts while the other downloads Linux scripts as shown below.
On a Linux machine, move the downloaded scripts to the directory where the MySQL database file is located. Unpack the files into this directory.
Now login to the Acronis Cyber Protect console and select your device and create a new backup plan as shown below. Under "What to Backup", select "Files/folders". Under "Items to Backup", click on "Specify" then navigate to the directory that contains the MySQL database file that you want to backup. Click the checkbox next to the folder name with the MySQL database file as shown below. Click the “Done” button.
Under "Where to Backup", select "Specify". Here, you will have the option of selecting an existing location or creating a new backup location. Select your desired backup location. Other options that you can specify for your backup include setting the backup schedule in accordance with your desired backup frequency and the optimal timing for the backup so as not to cripple business operations, setting how long you want Acronis to store the backups for, and setting up encryption for your database backups. Be sure to safely store your encryption password as you will not be able to access your backed-up files without it.
Once you have these configured to your desired specifications, click on the gear icon on the upper right-hand side of the backup interface then select "Backup Options". Scroll down to the “Pre-post data capture commands” option and click on it. You will see the interface below. This is where we will instruct Acronis to run the downloaded scripts as part of the backup process. Select the two toggles for "Execute a command before the data capture" and "Execute a command after the data capture" to "Yes".
First, we will set the parameters before the data capture. The Pre-freeze script should run before the database backup takes place. This is the script that locks the tables and records and preserves the database state throughout the backup process. under "Command or batch file path on the machine with an agent", navigate to the directory where you unpacked the scripts and select the "pre-freeze-script.sh" file. Under "Working directory", select the folder that contains the selected script. Then check the "Fail the backup if the command execution fails" check-box. This ensures that the database backup will not take place unless the database has been frozen.
The post-thaw script unfreezes the database after the backup process so that normal database operations can be resumed. Under “Execute a command after the data capture”, select the "post-thaw-script.sh" for the “Command or batch file path on the machine with an agent” field. Then select the folder containing this script file for the “Working” directory” field.
Click the “Create” button. After the window refreshes, click the “Run Now” button. Wait for the backup to run and refresh the console window.
Please note that these instructions only work with databases where all the database files are contained in a single volume. This backup method will not work properly on a distributed database. Congratulations! You have created a backup for your MySQL database.
