Create application-consistent snapshots for MySQL-Elastic Compute Service(ECS)-阿里云帮助中心

Prerequisites

The Elastic Compute Service (ECS) instance uses enterprise SSDs (ESSDs), and the multi-attach feature is disabled for the disks.
The ECS instance is in the Running state and the Cloud Assistant client is running correctly. To learn how to check the client status, see View the status of the Cloud Assistant client and handle exceptions.
You have installed a MySQL database and have the username and password for the database. For more information, see Manually deploy a MySQL database (Linux).
You have configured a RAM role and a custom policy with the required permissions for application-consistent snapshots and attached the role to the ECS instance. For more information, see Step 1: Create a RAM role.

Note
Because creating an application-consistent snapshot requires Cloud Assistant to access the ECS instance and run commands, you must grant it the necessary permissions through a RAM role.
- RAM role: A custom role, for example, AppSnapshotRoleName.
- Custom policy: The following policy grants permissions to query snapshots and disk information, create snapshots, and add tags to resources during the snapshot creation process.
```
{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ecs:DescribeSnapshot*",
                "ecs:CreateSnapshot*",
                "ecs:TagResources",
                "ecs:DescribeDisks"
            ],
            "Resource": [
                "*"
            ],
            "Condition": {}
        }
    ]
}
```

Workflow

This best practice shows you how to create an application-consistent snapshot to ensure application consistency. This process guarantees that the database is in the same state as when the snapshot was created. The workflow is as follows:

Step 1: Create the prescript.sh and postscript.sh scripts

Create a prescript (prescript.sh) to pause database write operations and a postscript (postscript.sh) to resume them. These scripts are required to create an application-consistent snapshot. The prescript pauses database writes before the snapshot is created, and the postscript resumes them after.
Step 2: Prepare the database verification environment

Create a database table and a stored procedure to simulate continuous data write operations. This lets you verify the effectiveness of the application-consistent snapshot. You will call the stored procedure (TestPIT) to insert data, which helps you check the effects of pausing the database and rolling back data.
Step 3: Create an application-consistent snapshot in the console

Create an application-consistent snapshot for the Linux instance that hosts the MySQL database. When you create the snapshot, you will use the prescript and postscript to temporarily stop and then resume database write operations, which ensures data consistency.
Step 4: Verify that the application-consistent snapshot is created

Verify the snapshot creation by checking the Cloud Assistant command execution results. Note the times when database write operations were paused and resumed.
Step 5: Verify the data restoration from the application-consistent snapshot

Roll back the disk using the application-consistent snapshot. Then, compare the last data write time in the MySQL database with the database pause time, which is the execution time of the prescript.sh script. This comparison verifies that the data was restored correctly from the application-consistent snapshot.
- If the last data write time is earlier than the prescript execution time, it means no write operations occurred while the database was paused. The restored data is consistent with the state of the database when the snapshot was created.
- If the last data write time is the same as or later than the prescript execution time, it means write operations occurred while the database was paused. This indicates that the application-consistent snapshot did not work as expected.

Procedure

Step 1: Create the prescript.sh and postscript.sh scripts

Important

The content of the prescript.sh and postscript.sh scripts in this topic is for demonstration purposes only. To create application-consistent snapshots for your own business applications, you must write the prescript.sh and postscript.sh scripts based on your actual business needs. For more information about the scripts, see Create an application-consistent snapshot.

Remotely connect to the ECS instance as the root user.

For more information, see Log on to a Linux instance using Workbench.

Create the /tmp/prescript.sh script and write content to it.

Use the root user to create /tmp/prescript.sh.
```
vim /tmp/prescript.sh
```
Press i to enter edit mode.

In the script, customize the content of prescript.sh for your application.

Expand to view the content of the prescript.sh script:

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

# Set your MySQL username
export MYSQL_USER="$MYSQL_USER"
# Set 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 -p$MYSQL_PASSWORD >> "${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 -p$MYSQL_PASSWORD -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 script, modify the following parameters:

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

Press the Esc key, enter :wq, and then press the Enter key to save the script and exit.
Set read, write, and execute permissions on the script for the root user only.
Important
To ensure script security, make sure that only the root user has read, write, and execute permissions on the script (permission 700). Otherwise, the script execution fails.
```
chmod 700 /tmp/prescript.sh
```

Create the /tmp/postscript.sh script and write content to it.

Use the root user to create /tmp/postscript.sh.
```
vim /tmp/postscript.sh
```
Press i to enter edit mode.

In the script, customize the content of postscript.sh for your application.

Expand to view the content of the postscript.sh script:

TIMESTAMP=`date +%s`
MYSQL_TEMP_FILE_NAME="/tmp/mysqlthaw${TIMESTAMP}.tmp"
LOG_FILE_NAME="/tmp/mysqlthaw${TIMESTAMP}.log"
# Set your MySQL username
export MYSQL_USER="$MYSQL_USER"
# Set 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 script, modify the following parameters:

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

Press the Esc key, enter :wq, and then press the Enter key to save the script and exit.
Set read, write, and execute permissions on the script for the root user only.
Important
To ensure script security, make sure that only the root user has read, write, and execute permissions on the script (permission 700). Otherwise, the script execution fails.
```
chmod 700 /tmp/postscript.sh
```

Go to the /tmp directory to check if the script permissions are correct.
```
cd /tmp
ls -l
```
The following output shows that the script permissions are correct.

Step 2: Prepare the database verification environment

Create the test script (/root/test.sql).
1. Create and open the test script (/root/test.sql).
```
vim /root/test.sql
```
2. Press i to enter edit mode.
3. Write the SQL script for verification.
  The SQL script contains statements to create a database table (PointInTime) and a verification stored procedure (TestPIT). The content is as follows.
```
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 the script and exit.
Log on to the MySQL database.
Enter the following command, press the Enter key, and enter the MySQL password as prompted.
```
mysql -u <mysqlUserName> -p
```
Replace <mysqlUserName> with your MySQL username.
Create a new database named AdventureWorks.
```
CREATE DATABASE AdventureWorks;
```
Run the test script.
```
source /root/test.sql
```
Call the stored procedure (TestPIT).
```
CALL TestPIT;
```
Important
You must create the application-consistent snapshot before the TestPIT stored procedure finishes. The procedure takes about 3 minutes to complete. Otherwise, you cannot verify the result of the application-consistent snapshot.

Step 3: Create an application-consistent snapshot in the console

Go to ECS console - Snapshot-consistent Groups.
In the top navigation bar, select the region and resource group of the resource that you want to manage.
On the Snapshot-consistent Groups tab, click Create Snapshot-consistent Group.
In the Create Snapshot dialog box, set the parameters for the snapshot-consistent group.
1. Resource Types is set to Instance by default.
2. Select a single instance and the ESSD disks within that instance.
3. Expand Advanced Settings and configure the application-consistent snapshot.
  1. Select Enable Application-consistent Snapshot.
  2. Set the paths for the prescript.sh and postscript.sh scripts to match the paths of the scripts created in Step 1.
  3. Set the duration for File System I/O Pause and Resume.
Click OK.

The command returns a Cloud Assistant command execution ID. You can use this ID to view the execution result.

Step 4: Verify that the application-consistent snapshot is created

Click the Cloud Assistant command execution ID to view the execution result on the Cloud Assistant page.

As shown in the figure above, an Exit Code of 0 indicates that the command to create the application-consistent snapshot was successfully executed by Cloud Assistant. The output displays the IDs of the created application-consistent snapshot and the snapshot-consistent group.

Note
If the Exit Code is not 0, troubleshoot the issue based on the Exit Code error code information. For more information, see Error codes.
In the output from Cloud Assistant, view the pause and resume times for the database.
In the output, find the start time of the prescript.sh script and the completion time of the postscript.sh script.
- The prescript.sh script ran at 2024-08-27 15:27:55, which means the database paused write operations at this time.
- The postscript.sh script ran at 2024-08-27 15:27:57, which means the database resumed write operations at this time.
View the snapshot-consistent group and disk snapshot information.
1. Go to ECS console - Snapshot-consistent Groups.
2. On the Snapshot-consistent Groups tab, find the created snapshot-consistent group and click its ID to view the snapshot details.
3. In the Snapshot Information section, check the snapshot's tags to verify that an application-consistent snapshot was successfully created.
  
  In the example, the disk snapshot's tag is APPConsistent:True, which indicates that an application-consistent snapshot was created.
Connect to the MySQL database and check the data commit pause time.
1. Remotely connect to the ECS instance.
  
  For more information, see Log on to a Linux instance using Workbench.
2. Log on to the MySQL database.
  Enter the following command, press the Enter key, and enter the MySQL password as prompted.
```
mysql -u <mysqlUserName> -p
```
  Replace <mysqlUserName> with your MySQL username.
3. Query the content of the PointInTime table.
```
USE AdventureWorks;
SELECT * FROM PointInTime;
```
4. In the query results, view the database pause time.
  
  You can see that no data was inserted between 2024-08-27 15:27:55 and 2024-08-27 15:27:58.

Step 5: Verify the data restoration from the application-consistent snapshot

Roll back the disk using the created snapshot-consistent group.

For more information, see Roll back disks using a snapshot-consistent group.
Log on to the MySQL database again and query the content of the PointInTime table.
1. Remotely connect to the ECS instance.
  
  For more information, see Log on to a Linux instance using Workbench.
2. Log on to the MySQL database.
  Enter the following command, press the Enter key, and enter the MySQL password as prompted.
```
mysql -u <mysqlUserName> -p
```
  Replace <mysqlUserName> with your MySQL username.
3. Query the content of the PointInTime table.
```
USE AdventureWorks;
SELECT * FROM PointInTime;
```
4. In the query results, view the timestamp of the last record in the database after data restoration.
  
  You can see that the last data record was inserted into the database at 2024-08-27 15:27:54, which is earlier than the pause timestamp 2024-08-27 15:27:55 that you noted in Step 4. This proves that the data was restored correctly from the application-consistent snapshot.