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 prevents log rollbacks on database startup. This topic describes how to create application-consistent snapshots for a Linux Elastic Compute Service (ECS) instance and how to check whether the snapshots can be used as expected to restore data. MySQL is used in the examples.
Prerequisites
An ECS instance that runs one of the following operating systems is created: 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) at the performance levels 0, 1, 2, and 3 (PL0, PL1, PL2, and PL3 ESSDs) or ESSD AutoPL disks, and the file systems are ext3, ext4, XFS, or New Technology File System (NTFS). Network file systems or shared file systems are not supported.
Cloud Assistant Agent is installed on the ECS instance. For more information, see Install Cloud Assistant Agent.
NoteBy default, Cloud Assistant Agent is pre-installed on ECS instances that are created from public images after December 1, 2017.
MySQL is installed on the instance. For more information, see Deploy MySQL on a Linux instance.
Background information
For information about application-consistent snapshots, see Create application-consistent snapshots in the ECS console.
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 |
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.
Connect to the ECS instance.
For more information, see Connection method overview.
Create the /root/test.sql test script.
Create and open the /root/test.sql test script.
vi /root/test.sql
Press the
I
key to enter Insert mode.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 ;
Press the
Esc
key, enter:wq
, and then press the Enter key to save and close the script.
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.Create a database named AdventureWorks.
CREATE DATABASE AdventureWorks;
Run the test script.
source /root/test.sql
Exit MySQL.
exit
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 to create application-consistent snapshots.
The pre-freeze and post-thaw scripts used in this topic are for reference only. When you create application-consistent snapshots, you can compile pre-freeze and post-thaw scripts based on your applications and business scenarios.
Connect to the ECS instance.
For more information, see Connection method overview.
Create an application pre-freeze script named /tmp/prescript.sh.
Use the root account to create the /tmp/prescript.sh script.
vi /tmp/prescript.sh
Press the
I
key to enter Insert mode.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 this 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.Grant read, write, and execute permissions on the script only to the root account.
NoteFor 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
Create an application post-thaw script named /tmp/postscript.sh.
Use the root account to create the /tmp/postscript.sh script.
vi /tmp/postscript.sh
Press the
I
key to enter Insert mode.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 this 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.Grant read, write, and execute permissions on the script only to the root account.
NoteFor security purposes, make sure that only the root account are 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
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.
Log on to the Resource Access Management (RAM) console with an 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 the AppSnapshotRoleName RAM role.
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 Attach an instance RAM role to an ECS 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.
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.Switch to the AdventureWorks database.
USE AdventureWorks;
Call the TestPIT stored procedure.
CALL TestPIT;
ImportantYou 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.
Go to the Instances page in the ECS console.
Log on to the ECS console.
In the left-side navigation pane, choose .
In the top navigation bar, select the region where the ECS instance is located.
Find the instance based on which you want to create a snapshot-consistent group and choose
in the Actions column.In the Create Snapshot-consistent Group dialog box, configure the parameters to create a snapshot-consistent group.
Select the ESSDs for which you want to create snapshots.
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.
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.
On the ECS Cloud Assistant page, view the command execution results.
Log on to the ECS console.
In the left-side navigation pane, choose .
Click the Command Execution Result tab.
Find the execution ID obtained in the previous step and click it 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.NoteIf 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 Error codes.
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
.
On the Snapshots page, view the created snapshot-consistent group and disk snapshots.
In the left-side navigation pane, choose .
Click the Snapshot-consistent Groups tab, find the snapshot-consistent group that you created, and then click the group ID to view the details of the snapshot-consistent group.
In the Snapshot Information section, click the ID of a snapshot.
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.
Connect to the MySQL database to view the time when insert operations were suspended.
Connect to the ECS instance.
For more information, see Connection method overview.
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.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 yes, the MySQL data was properly backed up in the application-consistent snapshots and is restored as expected on the ECS instance.
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.
Log on to MySQL and query the content of the PointInTime table.
Connect to the ECS instance.
For more information, see Connection method overview.
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.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 the2021-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.