Using a Custom Script to Implement Consistent Backup for MySQL

This practice uses single-server MySQL 5.5 running on SUSE 11 SP3 to demonstrate how to use a custom script to freeze and unfreeze the MySQL database in order to implement application-consistent backup.

Scenario

An enterprise purchases Elastic Cloud Servers (ECSs) and installs MySQL 5.5 on ECSs for storing business data. As data increases, crash-consistent backup cannot meet the recovery time objective (RTO) and recovery point time (RPO) requirements and therefore application-consistent backup is needed.

Required Data

**Table 1** Required data
Item	Description	Example Value
MySQL username	Username for connecting to the MySQL database	root
MySQL password	Password for connecting to the MySQL database	Example@123

Procedure

Encrypt the MySQL password.
1. Log in to the MySQL server and run the cd /home/rdadmin/Agent/bin/ command to go to the Agent directory.
2. Run the /home/rdadmin/Agent/bin/agentcli encpwd command. The following information is displayed:
```
Enter password:
```
  Enter the MySQL password and press Enter. After the encrypted password is displayed, copy it to the clipboard.
  
  The plaintext password configured in the freezing and unfreezing scripts cannot exceed 16 characters. Or, the password will be truncated after the configuration, and application consistency backup will fail.
Run the cd /home/rdadmin/Agent/bin/thirdparty/ebk_user command to go to the directory saving the custom scripts and run the vi mysql_freeze.sh command to open the example MySQL freezing script.

The following figure shows an example. Set MYSQL_USER and MYSQL_PASSWORD based on your actual conditions, where MYSQL_PASSWORD should be the encrypted password obtained in step 1.

You can also run the sed commands to modify the configuration:

sed -i 's/^MYSQL_PASSWORD=.*/MYSQL_PASSWORD="XXX"/' mysql_freeze.sh mysql_unfreeze.sh, where XXX indicates the password obtained in step 1.

If you run this command, both the freezing and unfreezing scripts will be modified and therefore 3 is not needed.
Run the vi mysql_unfreeze.sh command to open the example MySQL unfreezing script and change the username and password in the script to be consistent with your actual settings.

The mysql_unfreeze.sh and mysql_freeze.sh scripts can only be used to freeze and unfreeze databases. If other operations are required, you can add them in the scripts via compilation. For details, see Using a Custom Script to Implement Consistent Backup for Other Linux Applications.

MySQL is frozen by running the FLUSH TABLES WITH READ LOCK command. This command will not trigger disk flushing on bin log. If bin log is enabled and the value of sync_binlog is not 1, some SQL operations saved in the backup image may not be recorded in bin log. To realize complete protection on bin log, set sync_binlog to 1.