Aurora MySQL zero-downtime patching


- 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 

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.Minor.zdp
>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
1. Create a shell script to execute the SELECT command to MySQL DB every 1 seconds
1. 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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142483/110e957a-1a6a-af50-2396-ca556f4f3ffc.png)
![スクリーンショット 2022-07-10 16.31.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142483/dc046943-3a39-d8a3-f4eb-355899defaae.png)

```bash
[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` 

```sql
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)

・・・以下省略・・・
```

```sql
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.


```bash
[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 `

```bash
-------------------------------------------------------------------------
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.
```bash
[root@ip-10-0-0-74 mysql]# ./zdp_test.sh

```

- After execute the shell script I executed the Aurora minor version upgrade command.

```bash
[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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142483/1f70f1c9-dc49-6d4f-4309-7f00ff1849b4.png)
`"Status": "upgrading", `
```bash
[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.

```bash
・・・省略・・・
-------------------------------------------------------------------------
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"
-------------------------------------------------------------------------

・・・省略・・・
```

- <font color="Red">I found that there is no SELECT result from 16:40:43〜16:40:53（about 10seconds）.</font>
- <font color="Red">Then I got standard output in console which executed the shell script.</font>
```console
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142483/43067616-d78b-1b9f-d944-3e45e24234bd.png)
`"EngineVersion": "5.7.mysql_aurora.2.10.1", `
```bash
[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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142483/8dd46e72-d7a5-1808-5411-a4eb51078e21.png)


# Summary 

- From the above test result I couldn't upgrade the Aurora minor version with zero-downtime.
- There is possibility that the above test didn't meet the following assumptions.
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.Minor.zdp
>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.