Disks can be rolled back based on application-consistent snapshots without corruption or loss of data. This ensures that applications such as MySQL start in a consistent state and avoids log rollbacks on database startup. This topic describes how to create an application-consistent snapshot for an Elastic Compute Service (ECS) Linux instance and how to verify the data backup effect of the snapshot. MySQL is used in the examples.

Prerequisites

  • A Linux instance runs one of the following operating systems: CentOS 7.6 or later, Ubuntu 18.04 or later, and Alibaba Cloud Linux 2.1903 LTS 64-bit.
  • The disks of the ECS instance are enhanced SSDs (ESSDs) and the file system is EXT3, EXT4, XFS, or NTFS. Network or shared file systems are not supported.
  • The Cloud Assistant client is installed on the instance. For more information, see Install the Cloud Assistant client.
    Note By default, ECS instances created from public images after December 1, 2017 are pre-installed with the Cloud Assistant client.

Background information

For information about application-consistent snapshots, see Enable the application-consistent snapshot feature.

The following table describes the configurations of the verification environment used in this topic.
Configuration Description
ECS instance type ecs.g7.large
Operating system CentOS 7.9 64-bit
Disk Enhanced SSDs (ESSDs)
Application MySQL 5.7
Note For information about how to deploy MySQL, see Manually deploy a MySQL database on an instance running CentOS 7.

Procedure

  1. Step 1: Prepare a database verification environment
  2. Step 2: Create application pre-freeze and post-thaw scripts
  3. Step 3: Attach a RAM role to the instance
  4. Step 4: Call the TestPIT stored procedure in the database
  5. Step 5: Create an application-consistent snapshot in the ECS console
  6. Step 6: Verify whether an application-consistent snapshot is created
  7. Step 7: Use the application-consistent snapshot to roll back the instance to verify the data backup effect of the snapshot

Step 1: Prepare a database verification environment

The MySQL 5.7 environment has been installed on the Linux instance. This section describes how to prepare a database verification environment on the instance.

  1. Connect to the instance.
    For more information, see Connection methods.
  2. Create the /root/test.sql test script.
    1. Create and open the /root/test.sql test script.
      vi /root/test.sql
    2. Press the I key to enter the edit mode.
    3. Compile and verify the test script.
      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. Example 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.
  3. 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 your MySQL username.

  4. Create a database named AdventureWorks.
    CREATE DATABASE AdventureWorks;
  5. Run the test script.
    source /root/test.sql

Step 2: Create application pre-freeze and post-thaw scripts

This section describes how to create a pre-freeze script and a post-thaw script for MySQL. These scripts are required for you to create an application-consistent snapshot for the instance.
Note The pre-freeze and post-thaw scripts used in this topic are for reference only. When you create an application-consistent snapshot for your instance, you can compile pre-freeze and post-thaw scripts based on your application and business scenarios.
  1. Create an application pre-freeze script named /tmp/prescript.sh.
    1. Use the root user to create the /tmp/prescript.sh script.
      vi /tmp/prescript.sh
    2. Press the I key to enter the edit mode.
    3. Specify the content of the script based on your application.
      In this example, the script contains the following 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  -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 -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 this script, you must modify the following parameters:
      • $MYSQL_USER: Replace this parameter with your MySQL username.
      • $MYSQL_PASSWORD: Replace this parameter with your MySQL password.
    4. Press the Esc key, enter :wq, and then press the Enter key to save and close the script.
    5. Configure the script to allow only the root user to have read, write, and execute permissions on the script.
      Note For security purposes, set the script permissions to 700 to allow only the root user to have read, write, and execute permissions on the script. Otherwise, script permissions are determined as invalid and the script cannot be run.
      chmod 700 /tmp/prescript.sh
  2. Create an application post-thaw script named /tmp/postscript.sh.
    1. Use the root user to create the /tmp/postscript.sh script.
      vi /tmp/postscript.sh
    2. Press the I key to enter the edit mode.
    3. Specify the content of the script based on your application.
      In this example, the script contains the following 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
      In this script, you must modify the following parameters:
      • $MYSQL_USER: Replace this parameter with your MySQL username.
      • $MYSQL_PASSWORD: Replace this parameter with your MySQL password.
    4. Press the Esc key, enter :wq, and then press the Enter key to save and close the script.
    5. Configure the script to allow only the root user to have read, write, and execute permissions on the script.
      Note For security purposes, set the script permissions to 700 to allow only the root user to have read, write, and execute permissions on the script. Otherwise, script permissions are determined as invalid and the script cannot be run.
      chmod 700 /tmp/postscript.sh
  3. Go to the /tmp directory to check the script permissions.
    Go to the /tmp directory.
    cd /tmp
    Check script permissions.
    ls -l
    Example command output: ls

Step 3: Attach a RAM role to the instance

Before you enable the application-consistent snapshot feature, you must configure a Resource Access Management (RAM) role for the ECS instance.

  1. Log on to the RAM console by using your Alibaba Cloud account.
  2. Create a RAM role for the application-consistent snapshot feature. For more information about how to create a RAM role, see Create a RAM role for a trusted Alibaba Cloud service.
    The following figure shows an example on how to create the AppSnapshotRoleName RAM role. RAM role for the application-consistent snapshot feature
  3. Create a permission policy for the application-consistent snapshot feature. For more information about how to create a policy, see Create a custom policy.
    Snapshot policy
    Create the AppSnapshotPolicy policy to grant the permissions to query snapshot details, create snapshots, configure tags, and query disk details. You can use the following policy:
    {
        "Version": "1",
        "Statement": [
            {
                "Effect": "Allow",
                "Action": [
                    "ecs:DescribeSnapshot*",
                    "ecs:CreateSnapshot*",
                    "ecs:TagResources",
                    "ecs:DescribeDisks"
                ],
                "Resource": [
                    "*"
                ],
                "Condition": {}
            }
        ]
    }
  4. Attach the AppSnapshotPolicy policy to the AppSnapshotRoleName RAM role. For more information about how to attach a policy to a RAM role, see Grant permissions to a RAM role.
    Attach policy
  5. Bind the AppSnapshotRoleName RAM role to the ECS instance. For more information about how to bind a RAM role to an ECS instance, see Bind an instance RAM role.

Step 4: Call the TestPIT stored procedure in the database

This section describes how to call the stored procedure before you create an application-consistent snapshot for the instance, so that you can verify the application freezing and data rollback effects later.

  1. 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 your MySQL username.

  2. Switch to the AdventureWorks database.
    USE AdventureWorks;
  3. Call the TestPIT stored procedure.
    CALL TestPIT;
    Note You must create an application-consistent snapshot during the 3 minute window it takes for TestPIT to finish running.

Step 5: Create an application-consistent snapshot in the ECS console

This section describes how to create an application-consistent snapshot for the instance that hosts MySQL in the ECS console.

  1. Go to the Instances page.
    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 on which you want to perform operations is located.
  2. Find the instance for which you want to enable the application-consistent snapshot feature and choose More > Disk and Image > Create Instance Snapshot in the Actions column.
  3. In the Create Instance Snapshot dialog box, configure the parameters for the instance snapshot and enable the instant access feature.
  4. In the Create Instance Snapshot dialog box, configure the application-consistent snapshot feature.
    Select Enable Application-consistent Snapshot and specify the Application Pre-freeze Script Path and Application Pre-freeze Script Path parameters. Make sure that your specified paths are consistent with those of the scripts created in Step 2. For more information about the scripts, see Step 2: Create application pre-freeze and post-thaw scripts. ECS console
  5. Click OK.
    After the instance snapshot is created, you can choose Storage & Snapshots > Snapshots in the left-side navigation pane and click the Instance Snapshots tab to view the snapshot.

    You can also click the Snapshots tab to check whether the created snapshot is an application-consistent snapshot or a file-system consistent snapshot based on the tag. Application-consistent snapshots have the APPConsistent:True tag. File-system consistent snapshots have the FsConsistent: True tag.

Step 6: Verify whether an application-consistent snapshot is created

This section describes how to verify whether an application-consistent snapshot is created and how to verify whether data submissions are suspended for the database.

  1. On the 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 > ECS Cloud Assistant.
    3. Click the Command Execution Result tab.
    4. Find the execution ID obtained in Step 5 and click it to view the execution results.
      View the execution results

      If the return value of ExitCode is 0 as shown in the preceding figure, Cloud Assistant has created an application-consistent snapshot. In this case, the return information indicates that an application-consistent snapshot is created for the instance, and shows the ID of the instance snapshot.

      Note If the return value of ExitCode is not 0, an error occurred. Troubleshoot the error based on the ExitCode value. For more information, see Enable the application-consistent snapshot feature.
  2. View the time at which the application froze in the return information.
    In the return information, find the time at which the application pre-freeze script begins running and the time at which the application post-thaw script finishes running.
    • In this example, the application pre-freeze script began running at 2021-08-03 18:58:41. Pre-freeze script
    • In this example, the application post-thaw script finished running at 2021-08-03 18:58:44. Post-thaw script
  3. On the Snapshots page, view the created instance snapshot and disk snapshots.
    1. In the left-side navigation pane, choose Storage & Snapshots > Snapshots.
    2. Click the Instance Snapshots tab to view the instance snapshot.
    3. Click the Snapshots tab to verify based on tags whether application-consistent snapshots are created for the disks.
      In the following figure, the APPConsistent:True tag indicates that the disk snapshot is an application-consistent snapshot. Snapshot tags
  4. Connect to the MySQL database to view the time at which data submissions are suspended.
    1. Connect to the instance.
    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 your MySQL username.

    3. Query the content of the PointInTime table.
      SELECT * FROM PointInTime;
      If the following execution results are returned, data writes are suspended for the database while the application pre-freeze and post-thaw scripts are running. select

Step 7: Use the application-consistent snapshot to roll back the instance to verify the data backup effect of the snapshot

Use the application-consistent snapshot that you created to roll back the instance. Then, check whether the last write time of the MySQL database precedes the time at which the application pre-freeze script began running to determine the data backup effect of the snapshot.

  1. In the ECS console, use the application-consistent snapshot created for the instance to roll back the instance.
  2. Log on to MySQL and query the content of the PointInTime table.
    1. Connect to the instance.
    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 your MySQL username.

    3. Query the content of the PointInTime table.
      SELECT * FROM PointInTime;
      Example return result: Restore data

      Data insertions are stopped before the database froze. In this example, after you use the application-consistent snapshot of the instance to restore data, the last write operation to the MySQL database was performed at 2021-08-03 18:58:40. This time precedes 2021-08-03 18:58:41, the application freezing time obtained in Step 5. The application-consistent snapshot has properly backed up data for MySQL.