Create application-consistent snapshots for MySQL - Elastic Compute Service

Use a CentOS 7.9 instance with MySQL 8.0 to create and verify application-consistent snapshots that prevent data corruption during backup.

Prerequisites

The ECS instance uses ESSDs, and the multi-attach feature is disabled for the disks.
The ECS instance is in the Running state. Cloud Assistant is in Normal state on the instance. To view Cloud Assistant status, see Check Cloud Assistant status and troubleshoot exceptions.
MySQL is installed, and you have the database username and password. See Manually deploy a MySQL database (Linux).
A RAM role with a custom policy for application-consistent snapshots is attached to the ECS instance. See Create a RAM role and attach it to an ECS instance.
Note
Cloud Assistant requires specific permissions to access the ECS instance and run commands. Grant these permissions through the RAM role.
- Configure a custom RAM role, such as AppSnapshotRoleName.
- Attach a custom policy to the RAM role. The following sample policy grants permissions to query snapshots, create snapshots, add tags, and query disk information.
```
{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ecs:DescribeSnapshot*",
                "ecs:CreateSnapshot*",
                "ecs:TagResources",
                "ecs:DescribeDisks"
            ],
            "Resource": [
                "*"
            ],
            "Condition": {}
        }
    ]
}
```

Workflow

The following workflow creates an application-consistent snapshot and verifies that the database restores to the exact state captured at snapshot time:

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

Create a prescript (prescript.sh) to pause database writes and a postscript (postscript.sh) to resume them. These scripts are required for application-consistent snapshots.
Step 2: Prepare the database verification environment

Create a database table and a stored procedure (TestPIT) that simulates continuous writes, so you can verify the snapshot's effectiveness after rollback.
Step 3: Create an application-consistent snapshot in the console

Create an application-consistent snapshot for the Linux instance that hosts MySQL. The prescript and postscript temporarily pause and resume database writes to ensure data consistency.
Step 4: Verify that the application-consistent snapshot is created

Check the Cloud Assistant command execution results. Note the database pause and resume times.
Step 5: Verify the data restoration from the application-consistent snapshot

Roll back the disk using the snapshot. Compare the last data write time in MySQL with the prescript.sh execution time to verify correct data restoration.
- If the last write time is earlier than the prescript execution time, no writes occurred during the pause. The restored data matches the snapshot state.
- If the last write time is the same as or later than the prescript execution time, writes occurred during the pause, indicating the snapshot did not work as expected.

Procedure

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

Important

The prescript.sh and postscript.sh scripts in this topic are for demonstration only. Write scripts based on your actual business needs. See Create an application-consistent snapshot.

Connect to the ECS instance as the root user.

See Log on to a Linux instance using Workbench.

Create /tmp/prescript.sh and add the script content.

Create /tmp/prescript.sh as root.
```
vim /tmp/prescript.sh
```
Press i to enter edit mode.

Customize the prescript.sh content for your application.

prescript.sh content

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

Modify the following parameters:

$MYSQL_USER: Your MySQL username.
$MYSQL_PASSWORD: Your MySQL password.

Press Esc, enter :wq, and press Enter to save and exit.
Set permissions to 700 (root only).
Important
Only the root user must have read, write, and execute permissions (700). Otherwise, the script execution fails.
```
chmod 700 /tmp/prescript.sh
```

Create /tmp/postscript.sh and add the script content.

Create /tmp/postscript.sh as root.
```
vim /tmp/postscript.sh
```
Press i to enter edit mode.

Customize the postscript.sh content for your application.

postscript.sh content

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

Modify the following parameters:

$MYSQL_USER: Your MySQL username.
$MYSQL_PASSWORD: Your MySQL password.

Press Esc, enter :wq, and press Enter to save and exit.
Set permissions to 700 (root only).
Important
Only the root user must have read, write, and execute permissions (700). Otherwise, the script execution fails.
```
chmod 700 /tmp/postscript.sh
```

Go to /tmp and verify the script permissions.
```
cd /tmp
ls -l
```
The following output shows correct permissions.

Step 2: Prepare the database verification environment

Create the test script /root/test.sql.

Create and open /root/test.sql.
```
vim /root/test.sql
```
Press i to enter edit mode.

Add the verification SQL script.

The script creates a table (PointInTime) and a stored procedure (TestPIT):

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 ;

Press Esc, enter :wq, and press Enter to save and exit.

Log on to MySQL.
Run the following command and enter the MySQL password when prompted.
```
mysql -u <mysqlUserName> -p
```
Replace <mysqlUserName> with your MySQL username.
Create a 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 TestPIT finishes. The procedure runs for about 3 minutes.

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

Go to ECS console - snapshot consistency group.
In the upper-left corner of the page, select a region and resource group.
On the Snapshot-consistent Groups tab, click Create Snapshot-consistent Group.
In the Create Snapshot dialog box, configure the snapshot-consistent group.
1. Resource Types defaults to Instance.
2. Select the instance and its ESSD disks.
3. Expand Advanced Settings and enable the application-consistent snapshot.
  1. Select Enable Application-consistent Snapshot.
  2. Set the prescript.sh and postscript.sh paths to 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 execution ID. Use this ID to view the execution result.

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

Click the Cloud Assistant execution ID to view the result.

An Exit Code of 0 indicates the snapshot was created successfully. The output displays the snapshot and snapshot-consistent group IDs.

Note
If the Exit Code is not 0, troubleshoot based on the Exit Code error code. See Error codes.
View the database pause and resume times in the Cloud Assistant output.
Find the prescript.sh start time and the postscript.sh completion time.
- The prescript.sh ran at 2024-08-27 15:27:55, indicating the database paused writes at this time.
- The postscript.sh ran at 2024-08-27 15:27:57, indicating the database resumed writes at this time.
View the snapshot-consistent group and disk snapshot.
1. Go to ECS console - snapshot consistency group.
2. On the Snapshot-consistent Groups tab, click the snapshot-consistent group ID to view details.
3. In the Snapshot Information section, verify the snapshot tag confirms application consistency.
  
  The disk snapshot tag APPConsistent:True confirms an application-consistent snapshot was created.
Connect to MySQL and check the data commit pause time.
1. Connect to the ECS instance.
  
  See Log on to a Linux instance using Workbench.
2. Log on to MySQL.
  Run the following command and enter the MySQL password when prompted.
```
mysql -u <mysqlUserName> -p
```
  Replace <mysqlUserName> with your MySQL username.
3. Query the PointInTime table.
```
USE AdventureWorks;
SELECT * FROM PointInTime;
```
4. View the database pause time in the query results.
  
  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 snapshot-consistent group.

See Roll back disks using a snapshot-consistent group.
Log on to MySQL and query the PointInTime table.
1. Connect to the ECS instance.
  
  See Log on to a Linux instance using Workbench.
2. Log on to MySQL.
  Run the following command and enter the MySQL password when prompted.
```
mysql -u <mysqlUserName> -p
```
  Replace <mysqlUserName> with your MySQL username.
3. Query the PointInTime table.
```
USE AdventureWorks;
SELECT * FROM PointInTime;
```
4. View the last record timestamp after data restoration.
  
  The last record was inserted at 2024-08-27 15:27:54, earlier than the pause time 2024-08-27 15:27:55 noted in Step 4. This confirms the data was restored correctly from the application-consistent snapshot.