LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@1429takahiro

Aurora MySQL zero-downtime patching

Posted at
  • The purpose of this article is to confirm the following Aurora MySQL zero-downtime patching feature.
  • Actually I wanted to test this feature using Aurora PostgreSQL, but there is no new minor version to upgrade using zero-downtime patching currently.
  • So I tried it using Aurora (MySQL 5.7) 2.10.0.

What's the zero-downtime patching

ZDP works by preserving current client connections to your Aurora PostgreSQL DB cluster throughout the Aurora PostgreSQL upgrade process. When ZDP completes successfully, application sessions are preserved and the database engine restarts while the upgrade is still under way. Although the database engine restart can cause a drop in throughput, that typically lasts only for a few seconds or at most, approximately one minute.

Test step

  1. Prepare the Aurora MySQL 5.7 engine version 2.10.0
  2. Create a shell script to execute the SELECT command to MySQL DB every 1 seconds
  3. Execute the shell script and Aurora MySQL minor version upgrade

1. Prepare the Aurora MySQL 5.7 engine version 2.10.0.

  • I created Aurora MySQL 5.7 engine version 2.10.0 from the MC.
    Aurora MySQL Cluster Name : zdp-test-db
    Aurora MySQL Instance Name : zdp-test-db-instance-1
    MySQL version : 5.7
    Aurora engine version : 2.10.0

スクリーンショット 2022-07-10 16.59.41.png
スクリーンショット 2022-07-10 16.31.54.png

[root@ip-10-0-0-74 ~]# mysql -h zdp-test-db-instance-1.cgduinwy350o.ap-northeast-1.rds.amazonaws.com -P 3306 -u admin -pxxxxxxxxxxxxxxxx
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

  • Get the sample data from the following link
    https://dev.mysql.com/doc/index-other.html

  • I created the database and input the sample data to Aurora MySQL like below
    Database name : world
    Table name : city , country , countrylanguage

MySQL [(none)]> SOURCE /work/mysql/world-db/world.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

・・・以下省略・・・

MySQL [world]> USE world;
Database changed
MySQL [world]> 
MySQL [world]> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

MySQL [world]> 
MySQL [world]> SELECT * FROM city LIMIT 10;
+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District      | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |
|  3 | Herat          | AFG         | Herat         |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 |
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 |
|  8 | Utrecht        | NLD         | Utrecht       |     234323 |
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 |
| 10 | Tilburg        | NLD         | Noord-Brabant |     193238 |
+----+----------------+-------------+---------------+------------+
10 rows in set (0.00 sec)

2. Create a shell script to execute the SELECT command to MySQL DB every 1 seconds

  • I created the following simple shell script to execute the SQL command every 1 seconds.
[root@ip-10-0-0-74 mysql]# cat zdp_test.sh
#!/bin/bash

cat /dev/null > /work/mysql/zdp_test.log
num=1

while true
do
    echo "-------------------------------------------------------------------------" >> /work/mysql/zdp_test.log
    mysql -u user1 -pxxxxxxxxxxxxxxxx -h zdp-test-db-instance-1.cgduinwy350o.ap-northeast-1.rds.amazonaws.com -e "SELECT * FROM world.city WHERE ID = $num;" | sed -e 's/\t/\" \"/g' | sed -e 's/^/\"/g' | sed -e 's/$/\"/g' | sed -e "s/^/$(date '+%Y-%m-%d %H:%M:%S.%3N') /g" >> /work/mysql/zdp_test.log   
    num=`expr $num + 1`
    sleep 1

done
[root@ip-10-0-0-74 mysql]#
  • A result of this script is like below
    Execute time,ID(count up),Name,CountryCode,District,Population 
-------------------------------------------------------------------------
2022-07-10 16:35:49.768 "ID" "Name" "CountryCode" "District" "Population"
2022-07-10 16:35:49.768 "1" "Kabul" "AFG" "Kabol" "1780000"
-------------------------------------------------------------------------
2022-07-10 16:35:50.802 "ID" "Name" "CountryCode" "District" "Population"
2022-07-10 16:35:50.802 "2" "Qandahar" "AFG" "Qandahar" "237500"
-------------------------------------------------------------------------
2022-07-10 16:35:51.839 "ID" "Name" "CountryCode" "District" "Population"
2022-07-10 16:35:51.839 "3" "Herat" "AFG" "Herat" "186800"
-------------------------------------------------------------------------
2022-07-10 16:35:52.881 "ID" "Name" "CountryCode" "District" "Population"
2022-07-10 16:35:52.881 "4" "Mazar-e-Sharif" "AFG" "Balkh" "127800"
-------------------------------------------------------------------------
2022-07-10 16:35:53.904 "ID" "Name" "CountryCode" "District" "Population"
2022-07-10 16:35:53.904 "5" "Amsterdam" "NLD" "Noord-Holland" "731200"

・・・省略・・・

3. Execute the shell script and Aurora MySQL minor version upgrade

  • Execute the shell script.
[root@ip-10-0-0-74 mysql]# ./zdp_test.sh
  • After execute the shell script I executed the Aurora minor version upgrade command.
[root@ip-10-0-0-74 ~]# aws rds modify-db-cluster \
>     --db-cluster-identifier zdp-test-db \
>     --engine-version 5.7.mysql_aurora.2.10.1 \
>     --allow-major-version-upgrade \
>     --apply-immediately
{
    "DBCluster": {
        "MasterUsername": "admin", 
        "ReaderEndpoint": "zdp-test-db.cluster-ro-cgduinwy350o.ap-northeast-1.rds.amazonaws.com", 
        "HttpEndpointEnabled": false, 
        "ReadReplicaIdentifiers": [], 
        "VpcSecurityGroups": [
            {
                "Status": "active", 
                "VpcSecurityGroupId": "sg-0a3f9f50b20fee38f"
            }
        ], 
        "CopyTagsToSnapshot": true, 
        "HostedZoneId": "Z24O6O9L7SGTNB", 
        "EngineMode": "provisioned", 
        "Status": "available", 
        "MultiAZ": false, 
        "LatestRestorableTime": "2022-07-10T07:34:00.075Z", 
        "DomainMemberships": [], 
        "PreferredBackupWindow": "14:17-14:47", 
        "DBSubnetGroup": "stockdb-subnetgroup", 
        "AllocatedStorage": 1, 
        "BackupRetentionPeriod": 1, 
        "PreferredMaintenanceWindow": "tue:16:29-tue:16:59", 
        "Engine": "aurora-mysql", 
        "Endpoint": "zdp-test-db.cluster-cgduinwy350o.ap-northeast-1.rds.amazonaws.com", 
        "AssociatedRoles": [], 
        "EarliestRestorableTime": "2022-07-10T04:41:21.812Z", 
        "CrossAccountClone": false, 
        "IAMDatabaseAuthenticationEnabled": false, 
        "ClusterCreateTime": "2022-07-10T04:39:23.284Z", 
        "EngineVersion": "5.7.mysql_aurora.2.10.0", 
        "DeletionProtection": false, 
        "DBClusterIdentifier": "zdp-test-db", 
        "DbClusterResourceId": "cluster-FPWYUJQLII672IWJSIG7U4RPCY", 
        "DBClusterMembers": [
            {
                "IsClusterWriter": true, 
                "DBClusterParameterGroupStatus": "pending-reboot", 
                "PromotionTier": 1, 
                "DBInstanceIdentifier": "zdp-test-db-instance-1"
            }
        ], 
        "DBClusterArn": "arn:aws:rds:ap-northeast-1:424739134352:cluster:zdp-test-db", 
        "KmsKeyId": "arn:aws:kms:ap-northeast-1:424739134352:key/331d9574-100b-4fe0-9889-c2e328693c97", 
        "StorageEncrypted": true, 
        "DatabaseName": "", 
        "DBClusterParameterGroup": "aurora-mysql5-7-custom-cl-pg", 
        "AvailabilityZones": [
            "ap-northeast-1c", 
            "ap-northeast-1a", 
            "ap-northeast-1d"
        ], 
        "Port": 3306
    }
}
[root@ip-10-0-0-74 ~]#
  • I confirm that Aurora status has changed.

スクリーンショット 2022-07-10 16.37.38.png
"Status": "upgrading", 

[root@ip-10-0-0-74 ~]# aws rds describe-db-clusters --db-cluster-identifier zdp-test-db
{
    "DBClusters": [
        {
            "MasterUsername": "admin", 
            "ReaderEndpoint": "zdp-test-db.cluster-ro-cgduinwy350o.ap-northeast-1.rds.amazonaws.com", 
            "HttpEndpointEnabled": false, 
            "ReadReplicaIdentifiers": [], 
            "VpcSecurityGroups": [
                {
                    "Status": "active", 
                    "VpcSecurityGroupId": "sg-0a3f9f50b20fee38f"
                }
            ], 
            "CopyTagsToSnapshot": true, 
            "HostedZoneId": "Z24O6O9L7SGTNB", 
            "EngineMode": "provisioned", 
            "Status": "upgrading", 
            "MultiAZ": false, 
            "LatestRestorableTime": "2022-07-10T07:34:00.075Z", 
            "DomainMemberships": [], 
            "PreferredBackupWindow": "14:17-14:47", 
            "DBSubnetGroup": "stockdb-subnetgroup", 
            "AllocatedStorage": 1, 
            "ActivityStreamStatus": "stopped", 
            "BackupRetentionPeriod": 1, 
            "PreferredMaintenanceWindow": "tue:16:29-tue:16:59", 
            "Engine": "aurora-mysql", 
            "Endpoint": "zdp-test-db.cluster-cgduinwy350o.ap-northeast-1.rds.amazonaws.com", 
            "AssociatedRoles": [], 
            "EarliestRestorableTime": "2022-07-10T04:41:21.812Z", 
            "CrossAccountClone": false, 
            "IAMDatabaseAuthenticationEnabled": false, 
            "ClusterCreateTime": "2022-07-10T04:39:23.284Z", 
            "EngineVersion": "5.7.mysql_aurora.2.10.0", 
            "DeletionProtection": false, 
            "DBClusterIdentifier": "zdp-test-db", 
            "DbClusterResourceId": "cluster-FPWYUJQLII672IWJSIG7U4RPCY", 
            "DBClusterMembers": [
                {
                    "IsClusterWriter": true, 
                    "DBClusterParameterGroupStatus": "pending-reboot", 
                    "PromotionTier": 1, 
                    "DBInstanceIdentifier": "zdp-test-db-instance-1"
                }
            ], 
            "DBClusterArn": "arn:aws:rds:ap-northeast-1:424739134352:cluster:zdp-test-db", 
            "KmsKeyId": "arn:aws:kms:ap-northeast-1:424739134352:key/331d9574-100b-4fe0-9889-c2e328693c97", 
            "StorageEncrypted": true, 
            "DatabaseName": "", 
            "DBClusterParameterGroup": "aurora-mysql5-7-custom-cl-pg", 
            "AvailabilityZones": [
                "ap-northeast-1c", 
                "ap-northeast-1a", 
                "ap-northeast-1d"
            ], 
            "Port": 3306
        }
    ]
}
[root@ip-10-0-0-74 ~]#
  • Side by side I confirm the shell script log.
・・・省略・・・
-------------------------------------------------------------------------
2022-07-10 16:40:40.195 "ID" "Name" "CountryCode" "District" "Population"
2022-07-10 16:40:40.195 "282" "Ilhéus" "BRA" "Bahia" "254970"
-------------------------------------------------------------------------
2022-07-10 16:40:41.216 "ID" "Name" "CountryCode" "District" "Population"
2022-07-10 16:40:41.216 "283" "Vitória da Conquista" "BRA" "Bahia" "253587"
-------------------------------------------------------------------------
2022-07-10 16:40:42.251 "ID" "Name" "CountryCode" "District" "Population"
2022-07-10 16:40:42.251 "284" "Uberaba" "BRA" "Minas Gerais" "249225"
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
2022-07-10 16:40:54.455 "ID" "Name" "CountryCode" "District" "Population"
2022-07-10 16:40:54.455 "296" "Ribeirão das Neves" "BRA" "Minas Gerais" "232685"
-------------------------------------------------------------------------
2022-07-10 16:40:55.489 "ID" "Name" "CountryCode" "District" "Population"
2022-07-10 16:40:55.489 "297" "Governador Valadares" "BRA" "Minas Gerais" "231724"
-------------------------------------------------------------------------
2022-07-10 16:40:56.518 "ID" "Name" "CountryCode" "District" "Population"
2022-07-10 16:40:56.518 "298" "Taubaté" "BRA" "São Paulo" "229130"
-------------------------------------------------------------------------

・・・省略・・・
  • I found that there is no SELECT result from 16:40:43〜16:40:53（about 10seconds）.
  • Then I got standard output in console which executed the shell script.
ERROR 2003 (HY000): Can't connect to MySQL server on 'zdp-test-db-instance-1.cgduinwy350o.ap-northeast-1.rds.amazonaws.com' (111)
  • Finally the Aurora minor version upgrade was completed.
    スクリーンショット 2022-07-10 16.59.41.png
    "EngineVersion": "5.7.mysql_aurora.2.10.1", 
[root@ip-10-0-0-74 ~]# aws rds describe-db-clusters --db-cluster-identifier zdp-test-db
{
    "DBClusters": [
        {
            "MasterUsername": "admin", 
            "ReaderEndpoint": "zdp-test-db.cluster-ro-cgduinwy350o.ap-northeast-1.rds.amazonaws.com", 
            "HttpEndpointEnabled": false, 
            "ReadReplicaIdentifiers": [], 
            "VpcSecurityGroups": [
                {
                    "Status": "active", 
                    "VpcSecurityGroupId": "sg-0a3f9f50b20fee38f"
                }
            ], 
            "CopyTagsToSnapshot": true, 
            "HostedZoneId": "Z24O6O9L7SGTNB", 
            "EngineMode": "provisioned", 
            "Status": "available", 
            "MultiAZ": false, 
            "LatestRestorableTime": "2022-07-10T07:40:05.959Z", 
            "DomainMemberships": [], 
            "PreferredBackupWindow": "14:17-14:47", 
            "DBSubnetGroup": "stockdb-subnetgroup", 
            "AllocatedStorage": 1, 
            "ActivityStreamStatus": "stopped", 
            "BackupRetentionPeriod": 1, 
            "PreferredMaintenanceWindow": "tue:16:29-tue:16:59", 
            "Engine": "aurora-mysql", 
            "Endpoint": "zdp-test-db.cluster-cgduinwy350o.ap-northeast-1.rds.amazonaws.com", 
            "AssociatedRoles": [], 
            "EarliestRestorableTime": "2022-07-10T04:41:21.812Z", 
            "CrossAccountClone": false, 
            "IAMDatabaseAuthenticationEnabled": false, 
            "ClusterCreateTime": "2022-07-10T04:39:23.284Z", 
            "EngineVersion": "5.7.mysql_aurora.2.10.1", 
            "DeletionProtection": false, 
            "DBClusterIdentifier": "zdp-test-db", 
            "DbClusterResourceId": "cluster-FPWYUJQLII672IWJSIG7U4RPCY", 
            "DBClusterMembers": [
                {
                    "IsClusterWriter": true, 
                    "DBClusterParameterGroupStatus": "in-sync", 
                    "PromotionTier": 1, 
                    "DBInstanceIdentifier": "zdp-test-db-instance-1"
                }
            ], 
            "DBClusterArn": "arn:aws:rds:ap-northeast-1:424739134352:cluster:zdp-test-db", 
            "KmsKeyId": "arn:aws:kms:ap-northeast-1:424739134352:key/331d9574-100b-4fe0-9889-c2e328693c97", 
            "StorageEncrypted": true, 
            "DatabaseName": "", 
            "DBClusterParameterGroup": "aurora-mysql5-7-custom-cl-pg", 
            "AvailabilityZones": [
                "ap-northeast-1c", 
                "ap-northeast-1a", 
                "ap-northeast-1d"
            ], 
            "Port": 3306
        }
    ]
}
[root@ip-10-0-0-74 ~]#

スクリーンショット 2022-07-10 17.26.11.png

Summary

In some cases, zero-downtime patching (ZDP) might not succeed. For example, if long-running queries or transactions are in progress, ZDP might need to cancel these to complete. If data definition language (DDL) statements are running or if temporary tables or table locks are in use for any other reason, ZDP might need to cancel the open transaction. Parameter changes that are in a pending state on your Aurora PostgreSQL DB cluster or its instances also interfere with ZDP.

  • In any case, I think we shouldn't expect/believe the feature so much, I would say we don't use this feature in production environment.
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?