MySQL application consistent backup using pre and post script with Nimesa Data Protection

In recent posts, we have discussed adding your AWS account to Nimesa to do the data protection.

In this post, we will discuss application-consistent backups of your MySQL on EC2 instance.

To know to install MySQL Server on Amazon Linux 2 on AWS Cloud click here

Nimesa supports pre and post script hooks to perform the specific operation before or after a backup, example if your Instance is running Oracle and you want to enable hot backup mode before performing backup you can write the script and upload to pre-script

There is no doubt about the importance of back up your databases so that you can recover your data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake.

Backups are also essential as a safeguard before upgrading a MySQL installation, and they can be used to transfer a MySQL installation to another system or to set up replication slave servers.

To get a consistent backup of the MySQL Server, stop the server or lock and flush the relevant tables, Stopping the server will have long downtime, instead, we can put the database in read-only mode and flush the table before backing up the data

First, will start with preparing the Amazon Linux environment for executing the pre and post script

Install the pip and setup tools required and install MySQL-python ( yum install MySQL-python )

Enable password authentication for Nimesa to communicate to the Amazon Linux to execute the scripts

Refer my post to enable the password authentication for the Amazon Linux

Then will proceed creating the pre & post script for MySQL Instance application-consistent backup

Refer the below code for the pre and post script or you can access the code from my public gist https://gist.github.com/asvignesh/48a2c077c4cb362703d6c244e85f1c15

Copy the above mysql-pre.py and mysql-post.py in the /scripts folder of your MySQL Instance and modify the username and password of the Database with your database credentials.

Then create pre and post script shell file in your system and create a backup policy with the pre.sh and post.sh files

Then create a backup group for the MySQL Instance and associate the created application policy

In Nimesa, Backup group is similar virtual machines or instances grouped together and associate the application policy and schedule policy to define the SLA of the backup period and retention of backups

Nimesa will prompt for the credentials if the group is associated with the pre/post script or application-aware guest processing

After creating the backup group, a backup job will be submitted successfully.

Check the status of the Job in the Nimesa Job monitoring.

In this way, we can create an application-consistent backup of the MySQL Instance running on Linux using Nimesa Data protection software