Create application-consistent snapshots for MySQL - Elastic Compute Service

To create an application-consistent snapshot, the system suspends write requests to ensure the integrity and consistency of data captured in the snapshot. You can roll back disks by using application-consistent snapshots without data corruption or loss, which ensures that applications, such as MySQL, start in a consistent state. This topic describes how to create application-consistent snapshots for a Linux Elastic Compute Service (ECS) instance on which MySQL is deployed and check whether the snapshots can be used to restore data as expected.

Prerequisites

An ECS instance that runs one of the following operating systems is created: CentOS 7.6 or later, Ubuntu 18.04 or later, or Alibaba Cloud Linux 2.
The disks that are attached to the ECS instance are Enterprise SSDs (ESSDs) for which multi-attach is disabled.
Cloud Assistant Agent is installed on the ECS instance. For information about how to install Cloud Assistant Agent, see Install Cloud Assistant Agent.
Note
By default, Cloud Assistant Agent is pre-installed on instances that are created from public images after December 1, 2017.
MySQL is installed on the ECS instance. The username and password of MySQL are obtained. For more information, see Deploy MySQL on a Linux instance.

Procedure

In the following examples, an ECS instance that runs CentOS 7.9 and on which MySQL 8.0 is installed is used. The operations may vary based on the actual scenario.

Step 1: Prepare a database verification environment

Prepare a database verification environment, including creating a database table and a stored procedure, which is used to check whether the application interacts with the database as expected.

Connect to the ECS instance as the root user.
For more information, see Connect to a Linux instance by using a password or key.
Create the /root/test.sql script.
1. Create and open the /root/test.sql script file.
```
vim /root/test.sql
```
2. Press the I key to enter Insert mode.
3. Compile the script file.
  This script includes the SQL statement used to create a database table named PointInTime and the SQL statement used to create a stored procedure named TestPIT. Sample script:
```
USE AdventureWorks;
CREATE TABLE PointInTime(id int, t datetime);
DELIMITER $$
CREATE PROCEDURE `TestPIT`()
BEGIN
DECLARE i int;
SET i=1;
WHILE i < 180
DO
INSERT INTO PointInTime VALUES(i, now());
SELECT SLEEP(1);
SET i=i+1;
END WHILE;
END $$
DELIMITER ;
```
4. Press the Esc key, enter :wq, and then press the Enter key to save and close the script file.
Log on to MySQL.
Enter the following command, press the Enter key, and then enter your MySQL password as prompted:
```
mysql -u <mysqlUserName> -p
```
Replace <mysqlUserName> with an actual MySQL username.
Create a database named AdventureWorks.
```
CREATE DATABASE AdventureWorks;
```
Run the script.
```
source /root/test.sql
```
Exit MySQL.
```
exit
```

Step 2: Create the prescript.sh and postscript.sh script files

This section describes how to create the prescript.sh and postscript.sh script files for MySQL. The script files are used to create application-consistent snapshots.

Important

The prescript.sh and postscript.sh script files used in this topic are only for reference. When you create application-consistent snapshots for applications, you can compile the prescript.sh and postscript.sh script files based on your applications and business scenarios.

Connect to the ECS instance as the root user.
For more information, see Connect to a Linux instance by using a password or key.

Create the /tmp/prescript.sh script file and write data to the script file.

Use the root account to create the /tmp/prescript.sh script file.
```
vim /tmp/prescript.sh
```
Press the I key to enter Insert mode.

Write the script file based on your applications.

Sample script:

TIMESTAMP=`date +%s`
MYSQL_TEMP_FILE_NAME="/tmp/mysqlfreeze${TIMESTAMP}.tmp"
LOG_FILE_NAME="/tmp/mysqlfreeze${TIMESTAMP}.log"

# Enter your MySQL username.
export MYSQL_USER="$MYSQL_USER"
# Enter your MySQL password.
export MYSQL_PWD="$MYSQL_PASSWORD"

function Log()
{
    echo "$1" 
    echo "$1" >> ${LOG_FILE_NAME}
}


function ExitWithResult()
{
    Log "[INFO]:mysql freeze result is $1."
    exit $1
}

function Main()
{
    Log "*********************************************************************"
    Log "[INFO]:Begin to freeze mysql."

    which mysql
    if [ $?  -ne 0 ]
    then
        Log "[INFO]:mysql is not installed."
        ExitWithResult 0
    fi  

    systemctl status mysqld.service | grep "inactive (dead)"
    if [ $?  -ne 1 ]
    then
        Log "[ERROR]:mysql is not running."
        ExitWithResult 0
    fi  

    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "show processlist;" > "${MYSQL_TEMP_FILE_NAME}" 2>&1
    if [ $?  -ne 0 ]
    then
        cat ${MYSQL_TEMP_FILE_NAME} >>"${LOG_FILE_NAME}"
        [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
        Log "[ERROR]:Show process list failed."
        ExitWithResult 1
    fi


    process_id=`cat ${MYSQL_TEMP_FILE_NAME} | grep "select 1 and sleep(25)" | awk -F " " '{print $1}'`
    if [ "$process_id" != "" ]
    then
        cat ${MYSQL_TEMP_FILE_NAME} >>"${LOG_FILE_NAME}"
        [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
        Log "[ERROR]:MySQL already been freezed "
        ExitWithResult 1
    fi

    cat ${MYSQL_TEMP_FILE_NAME}

    Log "[INFO]:Try to execute flush tables command"

       echo "flush tables with read lock;select 1 and sleep(25);" | nohup mysql -u$MYSQL_USER   >> "${LOG_FILE_NAME}" 2>&1 &
    if [ $?  -ne 0 ]
    then
        Log "[ERROR]:Freeze mysql failed."
        ExitWithResult 1
    fi  

    Log "[INFO]:Flush tables command execute success"

    checkTime=0
    while [ 1 ]
    do
        mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "show processlist;" > "${MYSQL_TEMP_FILE_NAME}" 2>&1
        if [ $?  -ne 0 ]
        then
            cat ${MYSQL_TEMP_FILE_NAME} >>"${LOG_FILE_NAME}"
            [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
            Log "[ERROR]:Show process list failed."
            ExitWithResult 1
        fi
        
        cat ${MYSQL_TEMP_FILE_NAME}

        process_id=`cat ${MYSQL_TEMP_FILE_NAME} | grep "select 1 and sleep(25)" | awk -F " " '{print $1}'`
        if [ "$process_id" = "" ]
        then
            checkTime=`expr $checkTime + 1`
            Log "[INFO]:Mysql is not freeze. checkTime is ${checkTime}"
            sleep 1
        else
            Log "[INFO]:Found sleep command in processlist,freeze success"
            break
        fi

           if [ $checkTime -eq 10 ]
        then
            cat "${MYSQL_TEMP_FILE_NAME}" >>"${LOG_FILE_NAME}" 2>&1
            
            freeze_id=`cat ${MYSQL_TEMP_FILE_NAME} | grep "flush tables with read lock" | awk -F " " '{print $1}'`            
            mysql -u$MYSQL_USER -e "kill $freeze_id;" >> "${LOG_FILE_NAME}" 2>&1
            if [ $?  -ne 0 ]
            then
                Log "[ERROR]:Thaw mysql failed."
            fi    

            [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
            Log "[ERROR]:Mysql is not freeze. Will return error"
            ExitWithResult 1
        fi
    done

       [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
    Log "[INFO]:Finish freeze mysql."
    ExitWithResult 0
}
Main

In the preceding script, you must modify the following parameters:

$MYSQL_USER: Set this parameter to your MySQL username.
$MYSQL_PASSWORD: Set this parameter to your MySQL password.

Press the Esc key, enter :wq, and then press the Enter key to save and close the script file.
Grant read, write, and execute permissions on the script file only to the root account.
Important
For security purposes, make sure that only the root account is granted read, write, and execute permissions on the script file (chmod 700). Otherwise, the permission settings are invalid and the script cannot run.
```
chmod 700 /tmp/prescript.sh
```

Create the /tmp/postscript.sh script file and write data to the script file.

Use the root account to create the /tmp/postscript.sh script file.
```
vim /tmp/postscript.sh
```
Press the I key to enter Insert mode.

Write the script file based on your applications.

Sample script:

TIMESTAMP=`date +%s`
MYSQL_TEMP_FILE_NAME="/tmp/mysqlthaw${TIMESTAMP}.tmp"
LOG_FILE_NAME="/tmp/mysqlthaw${TIMESTAMP}.log"
# Enter your MySQL username.
export MYSQL_USER="$MYSQL_USER"
# Enter your MySQL password.
export MYSQL_PWD="$MYSQL_PASSWORD"

function Log()
{
    echo "$1" 
    echo "$1" >> ${LOG_FILE_NAME}
}


function ExitWithResult()
{
    Log "[INFO]:mysql unfreeze result is $1."
    exit $1
}

function Main()
{
    Log "*********************************************************************"
    Log "[INFO]:Begin to thaw mysql."   

    which mysql
    if [ $?  -ne 0 ]
    then
        Log "[INFO]:mysql is not installed."
        ExitWithResult 0
    fi  

    systemctl status mysqld.service | grep "inactive (dead)"
    if [ $?  -ne 1 ]
    then
        Log "[ERROR]:mysql is not running."
        ExitWithResult 0
    fi  


    mysql -u$MYSQL_USER  -e "show processlist;" > "${MYSQL_TEMP_FILE_NAME}" 2>&1
    if [ $?  -ne 0 ]
    then
        cat ${MYSQL_TEMP_FILE_NAME} >>"${LOG_FILE_NAME}"
        [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
        Log "[ERROR]:show process list failed."
        ExitWithResult 1
    fi

    Log "[INFO]:show process list success."

    cat ${MYSQL_TEMP_FILE_NAME}
    
    process_ids=`cat ${MYSQL_TEMP_FILE_NAME} | grep "select 1 and sleep(25)" | awk -F " " '{print $1}'`
    if [ "$process_ids" = "" ]
    then
        [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
        Log "[ERROR]:Get freeze process_id failed."
        ExitWithResult 1
    fi

    cat ${MYSQL_TEMP_FILE_NAME} | grep "select 1 and sleep(25)" | awk -F " " '{print $1}'| while read pid
    do
        Log "[INFO]:Try to stop sql process ${pid}."

        mysql -u$MYSQL_USER  -e "kill $pid;" >> "${LOG_FILE_NAME}" 2>&1
        if [ $?  -ne 0 ]
        then
            [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
            Log "[ERROR]:Thaw mysql failed.PIDs is ${process_ids}"
            ExitWithResult 1
        fi   
        Log "[INFO]:Stop sql process ${pid} success."

    done 
    
    [ -f ${MYSQL_TEMP_FILE_NAME} ] && rm -rf ${MYSQL_TEMP_FILE_NAME}
    Log "[INFO]:Finish thaw mysql."
    ExitWithResult 0
}
Main

In the preceding script, you must modify the following parameters:

$MYSQL_USER: Set this parameter to your MySQL username.
$MYSQL_PASSWORD: Set this parameter to your MySQL password.

Press the Esc key, enter :wq, and then press the Enter key to save and close the script file.
Grant read, write, and execute permissions on the script file to only the root account.
Important
For security purposes, make sure that only the root account is granted read, write, and execute permissions on the script file (chmod 700). Otherwise, the permission settings are invalid and the script cannot run.
```
chmod 700 /tmp/postscript.sh
```

Go to the /tmp directory and check the permissions on the script files.
```
cd /tmp
ls -l
```
The following command output is returned, which indicates that the permissions on the script files are correctly configured.

Step 3: Attach a RAM role to the Linux instance

Before you enable the application-consistent snapshot feature for the ECS instance, you must attach a Resource Access Management (RAM) role that has the required permissions, such as the permissions to query snapshot details and create snapshots, to the instance.

Log on to the RAM console by using your Alibaba Cloud account.
Create a RAM role for the application-consistent snapshot feature. For more information, see Create a RAM role for a trusted Alibaba Cloud service.
The following figure shows how to create a RAM role named AppSnapshotRoleName.

Create a permission policy for the application-consistent snapshot feature. For more information, see Create custom policies.

快照权限

Create the AppSnapshotPolicy policy to grant permissions to query snapshot details, create snapshots, configure tags, and query disk details. You can use the following policy content:

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ecs:DescribeSnapshot*",
                "ecs:CreateSnapshot*",
                "ecs:TagResources",
                "ecs:DescribeDisks"
            ],
            "Resource": [
                "*"
            ],
            "Condition": {}
        }
    ]
}

Attach the AppSnapshotPolicy policy to the AppSnapshotRoleName RAM role. For more information, see Grant permissions to a RAM role.
Attach the AppSnapshotRoleName RAM role to the ECS instance. For more information, see Use instance RAM roles to control access to resources.

Step 4: Call the TestPIT stored procedure in the database

To create application-consistent snapshots for MySQL databases, the system suspends the databases. This section describes how to call the TestPIT stored procedure to insert data into a MySQL database before you create an application-consistent snapshot for the database. This way, you can subsequently check whether the MySQL database was suspended and the created snapshots can be used as expected to restore data.

Log on to MySQL.
Enter the following command, press the Enter key, and then enter your MySQL password as prompted:
```
mysql -u <mysqlUserName> -p
```
Replace <mysqlUserName> with an actual MySQL username.
Switch to the AdventureWorks database.
```
USE AdventureWorks;
```
Call the TestPIT stored procedure.
```
CALL TestPIT;
```
Important
You must create application-consistent snapshots within the 3-minute window that is required to run the TestPIT stored procedure.

Step 5: Create application-consistent snapshots in the ECS console

This section describes how to create application-consistent snapshots for the Linux instance on which MySQL is deployed in the ECS console.

Go to the Instance page in the ECS console.
1. Log on to the ECS console.
2. In the left-side navigation pane, choose Instances & Images > Instances.
3. In the top navigation bar, select the region where the ECS instance resides.
Find the instance that you want to manage and choose > Disk and Image > Create Snapshot-consistent Group in the Actions column.
In the Create Snapshot-consistent Group dialog box, configure the parameters.
1. Select the ESSDs for which you want to create snapshots and configure the parameters in the Snapshot Parameters section.
2. Configure the application-consistent snapshot parameters.
  Select Enable Application-consistent Snapshot, enter the path in which the prescript.sh script file is located in the Application Pre-freeze Script Path field, and then enter the path in which the postscript.sh script file is located in the Application Post-thaw Script Path field. Make sure that the paths that you enter are the same as the paths of the script files created in Step 2. For more information about the script files, see the Step 2: Create the prescript.sh and postscript.sh script files section of this topic.
Click OK.

After the snapshot-consistent group is created, a message that contains the Cloud Assistant command ID and the task ID appears, as shown in the following figure. You can check whether application-consistent snapshots are created based on the task ID. 命令执行id

Step 6: Check whether application-consistent snapshots are created

This section describes how to check whether application-consistent snapshots are created and insert operations are suspended for the database.

On the ECS Cloud Assistant page, view the command execution results.
1. Log on to the ECS console.
2. In the left-side navigation pane, choose Maintenance & Monitoring > Cloud Assistant.
3. Click the Command Execution Result tab.
4. Find the task ID obtained in the previous step and click the ID to view the execution results.
  If application-consistent snapshots are created in the task, the return value of ExitCode is 0 and the IDs of the created application-consistent snapshots and the snapshot-consistent group are displayed in the command output, as shown in the preceding figure.
  Note
  If the return value of ExitCode is not 0, an error occurred. Troubleshoot the error based on the returned error code in the ExitCode column. For more information, see the Error codes section of the "Create application-consistent snapshots" topic.
View the time when the application was suspended in the return data.
In the return data, find the time when the prescript.sh script started to run and the time when the postscript.sh script stopped running.
- In this example, the prescript.sh script started to run at 2024-08-27 15:27:55.
- In this example, the postscript.sh script stopped running at 2024-08-27 15:27:57.
On the Snapshots page, view the created snapshot-consistent group and disk snapshots.
1. In the left-side navigation pane, choose Storage & Snapshots > Snapshots.
2. Click the Snapshot-consistent Groups tab, find the snapshot-consistent group that you created, and then click the ID of the snapshot-consistent group to view the details of the snapshots that are created the group.
3. In the Snapshot Information section, check whether the snapshots created in the snapshot-consistent group are application-consistent snapshots based on the tags added to the snapshots.
  If a created snapshot is an application-consistent snapshot, the APPConsistent:True tag is displayed in the Tag column corresponding to the snapshot, as shown in the following figure.
Connect to the MySQL database to view the time when insert operations were suspended.
1. Connect to the Linux instance.
  For more information, see Connect to a Linux instance by using a password or key.
2. Log on to MySQL.
  Enter the following command, press the Enter key, and then enter your MySQL password as prompted:
```
mysql -u <mysqlUserName> -p
```
  Replace <mysqlUserName> with an actual MySQL username.
3. Query the content of the PointInTime table.
```
USE AdventureWorks;
SELECT * FROM PointInTime;
```
  The following query result is returned, which indicates that insert operations were suspended for the database while the prescript.sh and postscript.sh scripts were running from 2024-08-27 15:27:55 to 2024-08-27 15:27:57. In this case, the application-consistent snapshots can ensure data consistency between the application and the database at the time when the snapshots were created.

Step 7: Check whether application-consistent snapshots can be used to restore data as expected

Use application-consistent snapshots to restore data on the Linux ECS instance. Then, compare the last write time of MySQL data with the time when the prescript.sh script started to run to determine whether the data was properly backed up in the application-consistent snapshots and is restored from the snapshots as expected.

In the ECS console, use the snapshot-consistent group that you created to roll back data on the Linux ECS instance.
For more information, see Roll back disks by using a snapshot-consistent group.
Log on to MySQL and query the content of the PointInTime table.
1. Connect to the Linux ECS instance.
  For more information, see Connect to a Linux instance by using a password or key.
2. Log on to MySQL.
  Enter the following command, press the Enter key, and then enter your MySQL password as prompted:
```
mysql -u <mysqlUserName> -p
```
  Replace <mysqlUserName> with an actual MySQL username.
3. Query the content of the PointInTime table.
```
USE AdventureWorks;
SELECT * FROM PointInTime;
```
  The following query result is returned.
  Insert operations were suspended before the database was suspended. In this example, after you use the application-consistent snapshots in the snapshot-consistent group that you created to restore data, the last write time to the MySQL database is 2024-08-27 15:27:54. The last write time precedes 2024-08-27 15:27:55 obtained in Step 6, which is the time when the prescript.sh script started to run. In this case, the MySQL data was properly backed up in the application-consistent snapshots.