AWS RDS(MySQL)をAWS CLIで最低限のオプションで設定した場合のデフォルトパラメータ

# AWS CLIでRDS (MySQL)を作成
https://awscli.amazonaws.com/v2/documentation/api/latest/reference/rds/create-db-instance.html#examples

AWS CLI Command Referenceに従って最低限のオプションでRDSインスタンスを作成
## (変数設定)RDSインスタンス識別子
```:コマンド
DB_IDENTITY="test-mysql-instance" \
&& echo ${DB_IDENTITY}
```
```:出力
[cloudshell-user@ip-10-134-8-157 ~]$ DB_IDENTITY="test-mysql-instance" \
> && echo ${DB_IDENTITY}
test-mysql-instance
```
## RDSインスタンス作成
```:コマンド
aws rds create-db-instance \
    --db-instance-identifier ${DB_IDENTITY} \
    --db-instance-class db.t3.micro \
    --engine mysql \
    --master-username admin \
    --master-user-password secret99 \
    --allocated-storage 20
```
```:出力
[cloudshell-user@ip-10-134-8-157 ~]$ aws rds create-db-instance \
>     --db-instance-identifier ${DB_IDENTITY} \
>     --db-instance-class db.t3.micro \
>     --engine mysql \
>     --master-username admin \
>     --master-user-password secret99 \
>     --allocated-storage 20
{
    "DBInstance": {
        "DBInstanceIdentifier": "test-mysql-instance",
        "DBInstanceClass": "db.t3.micro",
        "Engine": "mysql",
        "DBInstanceStatus": "creating",
        "MasterUsername": "admin",
        "AllocatedStorage": 20,
        "PreferredBackupWindow": "16:07-16:37",
        "BackupRetentionPeriod": 1,
        "DBSecurityGroups": [],
        "VpcSecurityGroups": [
            {
                "VpcSecurityGroupId": "sg-0109570e93f4a220f",
                "Status": "active"
            }
        ],
        "DBParameterGroups": [
            {
                "DBParameterGroupName": "default.mysql8.0",
                "ParameterApplyStatus": "in-sync"
            }
        ],
        "DBSubnetGroup": {
            "DBSubnetGroupName": "default",
            "DBSubnetGroupDescription": "default",
            "VpcId": "vpc-090c14ab4d18c8e0b",
            "SubnetGroupStatus": "Complete",
            "Subnets": [
                {
                    "SubnetIdentifier": "subnet-06dac05f30d70fadf",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1d"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-0e58505daeffcca69",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1a"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-024f020ed79b42984",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1c"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                }
            ]
        },
        "PreferredMaintenanceWindow": "sat:13:58-sat:14:28",
        "PendingModifiedValues": {
            "MasterUserPassword": "****"
        },
        "MultiAZ": false,
        "EngineVersion": "8.0.35",
        "AutoMinorVersionUpgrade": true,
        "ReadReplicaDBInstanceIdentifiers": [],
        "LicenseModel": "general-public-license",
        "OptionGroupMemberships": [
            {
                "OptionGroupName": "default:mysql-8-0",
                "Status": "in-sync"
            }
        ],
        "PubliclyAccessible": true,
        "StorageType": "gp2",
        "DbInstancePort": 0,
        "StorageEncrypted": false,
        "DbiResourceId": "db-E7RRGBVNOKWWIMCCL6GVJXG2BQ",
        "CACertificateIdentifier": "rds-ca-rsa2048-g1",
        "DomainMemberships": [],
        "CopyTagsToSnapshot": false,
        "MonitoringInterval": 0,
        "DBInstanceArn": "arn:aws:rds:ap-northeast-1:999999999999:db:test-mysql-instance",
        "IAMDatabaseAuthenticationEnabled": false,
        "PerformanceInsightsEnabled": false,
        "DeletionProtection": false,
        "AssociatedRoles": [],
        "TagList": [],
        "CustomerOwnedIpEnabled": false,
        "BackupTarget": "region",
        "NetworkType": "IPV4",
        "StorageThroughput": 0,
        "CertificateDetails": {
            "CAIdentifier": "rds-ca-rsa2048-g1"
        },
        "DedicatedLogVolume": false
    }
}
```
# インスタンスの詳細を見やすいようにテーブル形式で確認
```:コマンド
aws rds describe-db-instances \
    --db-instance-identifier ${DB_IDENTITY} \
    --output table
```
```:出力
[cloudshell-user@ip-10-134-8-157 ~]$ aws rds describe-db-instances \
>     --db-instance-identifier ${DB_IDENTITY} \
>     --output table
----------------------------------------------------------------------------------------------------------
|                                           DescribeDBInstances                                          |
+--------------------------------------------------------------------------------------------------------+
||                                              DBInstances                                             ||
|+-----------------------------------+------------------------------------------------------------------+|
||  ActivityStreamStatus             |  stopped                                                         ||
||  AllocatedStorage                 |  20                                                              ||
||  AutoMinorVersionUpgrade          |  True                                                            ||
||  AvailabilityZone                 |  ap-northeast-1d                                                 ||
||  BackupRetentionPeriod            |  1                                                               ||
||  BackupTarget                     |  region                                                          ||
||  CACertificateIdentifier          |  rds-ca-rsa2048-g1                                               ||
||  CopyTagsToSnapshot               |  False                                                           ||
||  CustomerOwnedIpEnabled           |  False                                                           ||
||  DBInstanceArn                    |  arn:aws:rds:ap-northeast-1:999999999999:db:test-mysql-instance  ||
||  DBInstanceClass                  |  db.t3.micro                                                     ||
||  DBInstanceIdentifier             |  test-mysql-instance                                             ||
||  DBInstanceStatus                 |  available                                                       ||
||  DbInstancePort                   |  0                                                               ||
||  DbiResourceId                    |  db-E7RRGBVNOKWWIMCCL6GVJXG2BQ                                   ||
||  DedicatedLogVolume               |  False                                                           ||
||  DeletionProtection               |  False                                                           ||
||  Engine                           |  mysql                                                           ||
||  EngineVersion                    |  8.0.35                                                          ||
||  IAMDatabaseAuthenticationEnabled |  False                                                           ||
||  InstanceCreateTime               |  2024-04-27T09:19:40.994000+00:00                                ||
||  IsStorageConfigUpgradeAvailable  |  False                                                           ||
||  LatestRestorableTime             |  2024-04-27T09:19:55.189000+00:00                                ||
||  LicenseModel                     |  general-public-license                                          ||
||  MasterUsername                   |  admin                                                           ||
||  MonitoringInterval               |  0                                                               ||
||  MultiAZ                          |  False                                                           ||
||  NetworkType                      |  IPV4                                                            ||
||  PerformanceInsightsEnabled       |  False                                                           ||
||  PreferredBackupWindow            |  16:07-16:37                                                     ||
||  PreferredMaintenanceWindow       |  sat:13:58-sat:14:28                                             ||
||  PubliclyAccessible               |  True                                                            ||
||  StorageEncrypted                 |  False                                                           ||
||  StorageThroughput                |  0                                                               ||
||  StorageType                      |  gp2                                                             ||
|+-----------------------------------+------------------------------------------------------------------+|
|||                                         CertificateDetails                                         |||
||+----------------------------------+-----------------------------------------------------------------+||
|||  CAIdentifier                    |  rds-ca-rsa2048-g1                                              |||
|||  ValidTill                       |  2025-04-27T09:18:19+00:00                                      |||
||+----------------------------------+-----------------------------------------------------------------+||
|||                                          DBParameterGroups                                         |||
||+------------------------------------------------------+---------------------------------------------+||
|||  DBParameterGroupName                                |  default.mysql8.0                           |||
|||  ParameterApplyStatus                                |  in-sync                                    |||
||+------------------------------------------------------+---------------------------------------------+||
|||                                            DBSubnetGroup                                           |||
||+----------------------------------------------------+-----------------------------------------------+||
|||  DBSubnetGroupDescription                          |  default                                      |||
|||  DBSubnetGroupName                                 |  default                                      |||
|||  SubnetGroupStatus                                 |  Complete                                     |||
|||  VpcId                                             |  vpc-090c14ab4d18c8e0b                        |||
||+----------------------------------------------------+-----------------------------------------------+||
||||                                              Subnets                                             ||||
|||+----------------------------------------+---------------------------------------------------------+|||
||||  SubnetIdentifier                      |  subnet-06dac05f30d70fadf                               ||||
||||  SubnetStatus                          |  Active                                                 ||||
|||+----------------------------------------+---------------------------------------------------------+|||
|||||                                     SubnetAvailabilityZone                                     |||||
||||+---------------------------+--------------------------------------------------------------------+||||
|||||  Name                     |  ap-northeast-1d                                                   |||||
||||+---------------------------+--------------------------------------------------------------------+||||
||||                                              Subnets                                             ||||
|||+----------------------------------------+---------------------------------------------------------+|||
||||  SubnetIdentifier                      |  subnet-0e58505daeffcca69                               ||||
||||  SubnetStatus                          |  Active                                                 ||||
|||+----------------------------------------+---------------------------------------------------------+|||
|||||                                     SubnetAvailabilityZone                                     |||||
||||+---------------------------+--------------------------------------------------------------------+||||
|||||  Name                     |  ap-northeast-1a                                                   |||||
||||+---------------------------+--------------------------------------------------------------------+||||
||||                                              Subnets                                             ||||
|||+----------------------------------------+---------------------------------------------------------+|||
||||  SubnetIdentifier                      |  subnet-024f020ed79b42984                               ||||
||||  SubnetStatus                          |  Active                                                 ||||
|||+----------------------------------------+---------------------------------------------------------+|||
|||||                                     SubnetAvailabilityZone                                     |||||
||||+---------------------------+--------------------------------------------------------------------+||||
|||||  Name                     |  ap-northeast-1c                                                   |||||
||||+---------------------------+--------------------------------------------------------------------+||||
|||                                              Endpoint                                              |||
||+-----------------+----------------------------------------------------------------------------------+||
|||  Address        |  test-mysql-instance.clacqicsiqrt.ap-northeast-1.rds.amazonaws.com               |||
|||  HostedZoneId   |  Z24O6O9L7SGTNB                                                                  |||
|||  Port           |  3306                                                                            |||
||+-----------------+----------------------------------------------------------------------------------+||
|||                                       OptionGroupMemberships                                       |||
||+----------------------------------------------+-----------------------------------------------------+||
|||  OptionGroupName                             |  default:mysql-8-0                                  |||
|||  Status                                      |  in-sync                                            |||
||+----------------------------------------------+-----------------------------------------------------+||
|||                                          VpcSecurityGroups                                         |||
||+-----------------------------------------------+----------------------------------------------------+||
|||  Status                                       |  active                                            |||
|||  VpcSecurityGroupId                           |  sg-0109570e93f4a220f                              |||
||+-----------------------------------------------+----------------------------------------------------+||
```
PubliclyAccessible がTrue <font color="red">**(セキュリティ的には不可)**</font>
PubliclyAccessible が可能なのでCloudShellからSQL接続も可能
※VPCセキュリティグループにCloudShellのグローバルIPの許可設定の追加が必要
# CloudShellからSQL接続やってみる
<font color="red">**※下記のSQL接続は通信が暗号化されていません。検証用です。**</font>
## CloudShellのグローバルIPを変数に設定
```:コマンド
CLOUDSHELL_GIP=`curl ifconfig.me` \
 && echo ${CLOUDSHELL_GIP}
```
```:出力
[cloudshell-user@ip-10-134-8-157 ~]$ CLOUDSHELL_GIP=`curl ifconfig.me` \
>  && echo ${CLOUDSHELL_GIP}
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    13  100    13    0     0      4      0  0:00:03  0:00:03 --:--:--     4
57.180.29.143
```
## VPCセキュリティグループにルール追加
### (変数設定)VPCセキュリティグループ ID取得
```:コマンド
RDS_SG_ID=$( \
  aws rds describe-db-instances \
    --db-instance-identifier ${DB_IDENTITY} \
    --query 'DBInstances[].VpcSecurityGroups[].VpcSecurityGroupId' \
    --output text
) \
&& echo ${RDS_SG_ID}
```
```:出力
[cloudshell-user@ip-10-134-8-157 ~]$ RDS_SG_ID=$( \
>   aws rds describe-db-instances \
>     --db-instance-identifier ${DB_IDENTITY} \
>     --query 'DBInstances[].VpcSecurityGroups[].VpcSecurityGroupId' \
>     --output text
> ) \
> && echo ${RDS_SG_ID}
sg-0109570e93f4a220f
```
### セキュリティグループにMySQL用ルール追加
```:コマンド
SG_RULE_ID=$( 
  aws ec2 authorize-security-group-ingress \
    --group-id ${RDS_SG_ID} \
    --protocol tcp \
    --port 3306 \
    --cidr ${CLOUDSHELL_GIP}/32 \
    --query 'SecurityGroupRules[].SecurityGroupRuleId' \
    --output text
) \
&& echo ${SG_RULE_ID}
```
```:出力
[cloudshell-user@ip-10-134-8-157 ~]$ SG_RULE_ID=$( 
>   aws ec2 authorize-security-group-ingress \
>     --group-id ${RDS_SG_ID} \
>     --protocol tcp \
>     --port 3306 \
>     --cidr ${CLOUDSHELL_GIP}/32 \
>     --query 'SecurityGroupRules[].SecurityGroupRuleId' \
>     --output text
> ) \
> && echo ${SG_RULE_ID}
sgr-08482328dbab4854c
```
## CloudShellからSQL接続
### (変数設定)RDSエンドポイントアドレス取得
```:コマンド
RDS_ENDPOINT_ADR=$( \
  aws rds describe-db-instances \
    --db-instance-identifier ${DB_IDENTITY} \
    --query 'DBInstances[].Endpoint[].Address' \
    --output text
) \
&& echo ${RDS_ENDPOINT_ADR}
```
```:出力
[cloudshell-user@ip-10-134-8-157 ~]$ RDS_ENDPOINT_ADR=$( \
>   aws rds describe-db-instances \
>     --db-instance-identifier ${DB_IDENTITY} \
>     --query 'DBInstances[].Endpoint[].Address' \
>     --output text
> ) \
> && echo ${RDS_ENDPOINT_ADR}
test-mysql-instance.clacqicsiqrt.ap-northeast-1.rds.amazonaws.com
```
### SQL接続
```:コマンド
mysql -h ${RDS_ENDPOINT_ADR} -P 3306 -u admin -p
```
```:出力
[cloudshell-user@ip-10-134-8-157 ~]$ mysql -h ${RDS_ENDPOINT_ADR} -P 3306 -u admin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 8.0.35 Source distribution

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)]> quit
Bye
```
# 後片付け
## RDSインスタンスの削除
```:コマンド
aws rds delete-db-instance \
    --db-instance-identifier ${DB_IDENTITY} \
    --skip-final-snapshot \
    --delete-automated-backups
```
```:出力
[cloudshell-user@ip-10-130-44-53 ~]$ aws rds delete-db-instance \
>     --db-instance-identifier ${DB_IDENTITY} \
>     --skip-final-snapshot \
>     --delete-automated-backups
{
    "DBInstance": {
        "DBInstanceIdentifier": "test-mysql-instance",
        "DBInstanceClass": "db.t3.micro",
        "Engine": "mysql",
        "DBInstanceStatus": "deleting",
        "MasterUsername": "admin",
        "Endpoint": {
            "Address": "test-mysql-instance.clacqicsiqrt.ap-northeast-1.rds.amazonaws.com",
            "Port": 3306,
            "HostedZoneId": "Z24O6O9L7SGTNB"
        },
        "AllocatedStorage": 20,
        "InstanceCreateTime": "2024-04-27T09:19:40.994000+00:00",
        "PreferredBackupWindow": "16:07-16:37",
        "BackupRetentionPeriod": 1,
        "DBSecurityGroups": [],
        "VpcSecurityGroups": [
            {
                "VpcSecurityGroupId": "sg-0109570e93f4a220f",
                "Status": "active"
            }
        ],
        "DBParameterGroups": [
            {
                "DBParameterGroupName": "default.mysql8.0",
                "ParameterApplyStatus": "in-sync"
            }
        ],
        "AvailabilityZone": "ap-northeast-1d",
        "DBSubnetGroup": {
            "DBSubnetGroupName": "default",
            "DBSubnetGroupDescription": "default",
            "VpcId": "vpc-090c14ab4d18c8e0b",
            "SubnetGroupStatus": "Complete",
            "Subnets": [
                {
                    "SubnetIdentifier": "subnet-06dac05f30d70fadf",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1d"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-0e58505daeffcca69",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1a"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-024f020ed79b42984",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1c"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                }
            ]
        },
        "PreferredMaintenanceWindow": "sat:13:58-sat:14:28",
        "PendingModifiedValues": {},
        "LatestRestorableTime": "2024-04-27T12:55:00+00:00",
        "MultiAZ": false,
        "EngineVersion": "8.0.35",
        "AutoMinorVersionUpgrade": true,
        "ReadReplicaDBInstanceIdentifiers": [],
        "LicenseModel": "general-public-license",
        "OptionGroupMemberships": [
            {
                "OptionGroupName": "default:mysql-8-0",
                "Status": "in-sync"
            }
        ],
        "PubliclyAccessible": true,
        "StorageType": "gp2",
        "DbInstancePort": 0,
        "StorageEncrypted": false,
        "DbiResourceId": "db-E7RRGBVNOKWWIMCCL6GVJXG2BQ",
        "CACertificateIdentifier": "",
        "DomainMemberships": [],
        "CopyTagsToSnapshot": false,
        "MonitoringInterval": 0,
        "DBInstanceArn": "arn:aws:rds:ap-northeast-1:999999999999:db:test-mysql-instance",
        "IAMDatabaseAuthenticationEnabled": false,
        "PerformanceInsightsEnabled": false,
        "DeletionProtection": false,
        "AssociatedRoles": [],
        "TagList": [],
        "CustomerOwnedIpEnabled": false,
        "BackupTarget": "region",
        "NetworkType": "IPV4",
        "StorageThroughput": 0,
        "DedicatedLogVolume": false
    }
}
```
## セキュリティグループからMySQL用ルール削除
```:コマンド
aws ec2 revoke-security-group-ingress \
  --group-id ${RDS_SG_ID} \
  --security-group-rule-ids ${SG_RULE_ID}
```
```:出力
[cloudshell-user@ip-10-130-44-53 ~]$ aws ec2 revoke-security-group-ingress \
>   --group-id ${RDS_SG_ID} \
>   --security-group-rule-ids ${SG_RULE_ID}
{
    "Return": true
}
```