Elastic Compute Service:Best practices for creating application-consistent snapshots for a Linux instance on which MySQL is deployed

Procedure

Step 1: Prepare a database verification environment

Before you perform the following operations, make sure that MySQL 5.7 is installed on the ECS instance. This section describes how to prepare a database verification environment on the instance.

1. Connect to the ECS instance.

   For more information, see Connection method overview.

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 Insert mode.

   3. Compile 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. 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.

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

6. Exit MySQL.

   exit

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

1. Connect to the ECS instance.

   For more information, see Connection method overview.

2. Create an application pre-freeze script named /tmp/prescript.sh.

   1. Use the root account to create the /tmp/prescript.sh script.

      vi /tmp/prescript.sh

   2. Press the I key to enter the Insert mode.

   3. Write the script 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.

   4. Press the Esc key, enter :wq, and then press the Enter key to save and close the script file.

   5. Grant read, write, and execute permissions on the script only to the root account.

      Note

      For security purposes, make sure that only the root account is granted read, write, and execute permissions on the script (chmod 700). Otherwise, the permission settings are invalid and the script cannot be run.

      chmod 700 /tmp/prescript.sh

3. Create an application post-thaw script named /tmp/postscript.sh.

   1. Use the root account to create the /tmp/postscript.sh script.

      vi /tmp/postscript.sh

   2. Press the I key to enter the Insert mode.

   3. Write the script 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.

   4. Press the Esc key, enter :wq, and then press the Enter key to save and close the script file.

   5. Grant read, write, and execute permissions on the script to only the root account.

      Note

      For security purposes, make sure that only the root account is granted read, write, and execute permissions on the script (chmod 700). Otherwise, the permission settings are invalid and the script cannot be run.

      chmod 700 /tmp/postscript.sh

4. Go to the /tmp directory to check the script permissions.

   Go to the /tmp directory.

   cd /tmp

   Check script permissions.

   ls -l

   A command output similar to the following one is returned.

Step 3: Attach a RAM role to the instance

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

Step 4: Call the TestPIT stored procedure in the database

This section describes how to call the TestPIT stored procedure before you create application-consistent snapshots for the instance. This way, you can subsequently check whether your application was frozen and whether the snapshots can be used as expected to restore data.

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;

   Important

   You must create application-consistent snapshots within the 3-minute window that is required to run TestPIT.

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

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

1. 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 is located.

2. Find the instance that you want to manage and choose > Disk and Image > Create Snapshot-consistent Group in the Actions column.

3. In the Create Snapshot-consistent Group dialog box, configure the parameters.

   1. Select the ESSDs for which you want to create snapshots.

   2. Configure the application-consistent snapshot parameters.

      Select Enable Application-consistent Snapshot and configure the Application Pre-freeze Script Path and Application Post-thaw Script Path parameters. Make sure that the paths you specified are consistent with those of the scripts created in Step 2. For information about the scripts, see Step 2: Create application pre-freeze and post-thaw scripts.

4. Click OK.

After the snapshot-consistent group is created, a message indicating the Cloud Assistant command ID and the execution ID appears, as shown in the following figure. You can check whether application-consistent snapshots are created based on the execution ID.

Step 6: Check whether application-consistent snapshots are created

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

1. 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 > ECS Cloud Assistant.

   3. Click the Command Execution Result tab.

   4. Find the execution ID obtained in the previous step and click the ID to view the execution results.

      

      If application-consistent snapshots are created, the return value of ExitCode is 0 and the IDs of the created application-consistent snapshots and 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 in the "Create application-consistent snapshots in the ECS console" topic.

2. View the time when the application was frozen in the return data.

   In the return data, find the time when the application pre-freeze script began to run and the time when the application post-thaw script finished running.

   • In this example, the application pre-freeze script began to run at 2021-08-03 18:58:41.

   • In this example, the application post-thaw script finished running at 2021-08-03 18:58:44.

3. 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 snapshot-consistent group.

   3. In the Snapshot Information section, click the ID of a disk snapshot.

      

   4. On the Disk Snapshots tab, check based on the snapshot tags whether application-consistent snapshots are created for the disks on the ECS instance.

      If application-consistent snapshots are created, the APPConsistent:True tag is displayed in the Tag column corresponding to the snapshots, as shown in the following figure.

      

4. Connect to the MySQL database to view the time when insert operations were suspended.

   1. Connect to the ECS instance.

      For more information, see Connection method overview.

   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;

      The following query results indicate that insert operations were suspended for the database while the application pre-freeze and post-thaw scripts were running.

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

Use application-consistent snapshots to restore data on the ECS instance. Then, check whether the last write time of MySQL data precedes the time when the application pre-freeze script began to run. If the last write time precedes the start time, the MySQL data was properly backed up in the application-consistent snapshots and is restored as expected on the ECS instance.

1. In the ECS console, use the snapshot-consistent group that you created to roll back data on the ECS instance.

   For more information, see Roll back disks by using a snapshot-consistent group.

2. Log on to MySQL and query the content of the PointInTime table.

   1. Connect to the ECS instance.

      For more information, see Connection method overview.

   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;

      Results similar to the following ones are returned.

      Insert operations were stopped before the database was frozen. 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 2021-08-03 18:58:40. This time precedes the 2021-08-03 18:58:41 freezing time obtained in Step 5. In this case, the application-consistent snapshots backed up the MySQL data as expected.