Performing RDS DB initialization with CloudFormation Custom Resource
When creating an RDS resource with CloudFormation, we also want to initialize the DB (create DBs and tables, add test records, etc.).
In this case, we will initialize the DB using a CloudFormation custom resource.
Environment
We will create four main resources.
The first is an RDS DB instance.
In this case, we will create a MySQL type DB instance.
The second is an EC2 instance.
This will be used as a client to connect to the DB instance.
The instance is the latest version of Amazon Linux 2.
The third is a Lambda function.
We associate a function with a CloudFormation custom resource so that this function will be automatically executed when the CloudFormation stack is created.
The function’s function is to initialize a DB instance.
The runtime environment for the function is Python 3.8.
The fourth is the SSM parameter store.
The SQL statement to be executed by the Lambda function is stored as a string in the parameter store.
The following AWS official page was used as a reference for the contents to be executed in the initialization process.
CloudFormation Template Files
The above configuration is built using CloudFormation.
The CloudFormation templates are located at the following URL
https://github.com/awstut-an-r/awstut-fa/tree/main/062
Explanation of key points of the template files
SSM Parameter Store
Resources:
SQLParameter:
Type: AWS::SSM::Parameter
Properties:
Name: !Ref Prefix
Type: String
Value: |
CREATE database tutorial;
USE tutorial;
CREATE TABLE planet (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(30), PRIMARY KEY(id));
INSERT INTO planet (name) VALUES ("Mercury");
INSERT INTO planet (name) VALUES ("Venus");
INSERT INTO planet (name) VALUES ("Earth");
INSERT INTO planet (name) VALUES ("Mars");
INSERT INTO planet (name) VALUES ("Jupiter");
INSERT INTO planet (name) VALUES ("Saturn");
INSERT INTO planet (name) VALUES ("Uranus");
INSERT INTO planet (name) VALUES ("Neptune");
Code language: YAML (yaml)Register the SQL statements to be executed by the Lambda function in the SSM parameter store.
After creating the DB and tables, save the test records.
CloudFormation Custom Resource
First, check the Lambda functions to be executed in the custom resource.
Resources:
Function:
Type: AWS::Lambda::Function
Properties:
Environment:
Variables:
DB_ENDPOINT_ADDRESS: !Ref DBInstanceEndpointAddress
DB_ENDPOINT_PORT: !Ref MySQLPort
DB_PASSWORD: !Ref DBMasterUserPassword
DB_USER: !Ref DBMasterUsername
REGION: !Ref AWS::Region
SQL_PARAMETER: !Ref SQLParameter
Code:
ZipFile: |
import boto3
import cfnresponse
import mysql.connector
import os
db_endpoint_port = os.environ['DB_ENDPOINT_PORT']
db_endpoint_address = os.environ['DB_ENDPOINT_ADDRESS']
db_password = os.environ['DB_PASSWORD']
db_user = os.environ['DB_USER']
region = os.environ['REGION']
sql_parameter = os.environ['SQL_PARAMETER']
CREATE = 'Create'
response_data = {}
def lambda_handler(event, context):
try:
if event['RequestType'] == CREATE:
client = boto3.client('ssm', region_name=region)
response = client.get_parameter(Name=sql_parameter)
sql_statements = response['Parameter']['Value']
conn = mysql.connector.connect(
host=db_endpoint_address,
port=db_endpoint_port,
user=db_user,
password=db_password
)
cur = conn.cursor()
for sql in sql_statements.splitlines():
print(sql)
cur.execute(sql)
cur.close()
conn.commit()
cfnresponse.send(event, context, cfnresponse.SUCCESS, response_data)
except Exception as e:
print(e)
cfnresponse.send(event, context, cfnresponse.FAILED, response_data)
FunctionName: !Sub "${Prefix}-function"
Handler: index.lambda_handler
Layers:
- !Ref LambdaLayer
Runtime: !Ref Runtime
Role: !GetAtt FunctionRole.Arn
Timeout: !Ref Timeout
VpcConfig:
SecurityGroupIds:
- !Ref FunctionSecurityGroup
SubnetIds:
- !Ref PrivateSubnet
Code language: YAML (yaml)Define the code to be executed by the Lambda function in inline notation.
For more information, please refer to the following page
Use the cfnresponse module to implement functions as Lambda-backed custom resource.
For more information, please refer to the following page
In this configuration, the Lambda function is placed in the VPC.
The VpcConfig property specifies the subnet where the function will be placed and the security group to be applied.
The code to be executed is as follows.
- retrieve the environment variables defined in the CloudFormation template by accessing os.environ.
- Create a client object for SSM with Boto3.
- Use the client object to access the SSM parameter store and retrieve the SQL statement described above.
- connect to the DB instance using MySQL client and execute SQL one statement at a time.
MySQL client for DB instance connection, but use MySQL Connector.
https://dev.mysql.com/doc/connector-python/en/
In this case, we will prepare the MySQL Connector package as a Lambda layer.
For more information on the Lambda layer, please refer to the following page
For your information, the IAM role for the functions is as follows
Resources:
FunctionRole:
Type: AWS::IAM::Role
Properties:
AssumeRolePolicyDocument:
Version: 2012-10-17
Statement:
- Effect: Allow
Action: sts:AssumeRole
Principal:
Service:
- lambda.amazonaws.com
ManagedPolicyArns:
- arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole
Policies:
- PolicyName: GetSSMParameterPolicy
PolicyDocument:
Version: 2012-10-17
Statement:
- Effect: Allow
Action:
- ssm:GetParameter
Resource:
- !Sub "arn:aws:ssm:${AWS::Region}:${AWS::AccountId}:parameter/${SQLParameter}"
Code language: YAML (yaml)In addition to the AWS management policy AWSLambdaVPCAccessExecutionRole, grant permission to retrieve parameters from the SSM parameter store.
Then check the CloudFormation custom resource body.
Resources:
CustomResource:
Type: Custom::CustomResource
Properties:
ServiceToken: !Ref FunctionArn
Code language: YAML (yaml)Specifies the Lambda function described above.
VPC Endpoint
In this configuration, multiple VPC endpoints will be created.
Here we focus on the VPC endpoint for SSM.
Resources:
SSMEndpoint:
Type: AWS::EC2::VPCEndpoint
Properties:
PrivateDnsEnabled: true
SecurityGroupIds:
- !Ref EndpointSecurityGroup
ServiceName: !Sub "com.amazonaws.${AWS::Region}.ssm"
SubnetIds:
- !Ref PrivateSubnet
VpcEndpointType: Interface
VpcId: !Ref VPC
Code language: YAML (yaml)Use this VPC endpoint to connect to the SSM parameter store from a Lambda function deployed in the VPC.
By creating a VPC endpoint, you can connect to AWS resources outside the VPC without going through the Internet.
(Reference) RDS DB Instance
Resources:
DBInstance:
Type: AWS::RDS::DBInstance
Properties:
AllocatedStorage: !Ref DBAllocatedStorage
AvailabilityZone: !Sub "${AWS::Region}${AvailabilityZone}"
DBInstanceClass: !Ref DBInstanceClass
DBInstanceIdentifier: dbinstance
DBSubnetGroupName: !Ref DBSubnetGroup
Engine: !Ref DBEngine
EngineVersion: !Ref DBEngineVersion
MasterUsername: !Ref DBMasterUsername
MasterUserPassword: !Ref DBMasterUserPassword
VPCSecurityGroups:
- !Ref DBSecurityGroup
Code language: YAML (yaml)DB instance.
No special configuration is required to initialize the DB using CloudFormation custom resources.
(Reference) EC2 Instance
Resources:
Instance:
Type: AWS::EC2::Instance
Properties:
IamInstanceProfile: !Ref InstanceProfile
ImageId: !Ref ImageId
InstanceType: !Ref InstanceType
NetworkInterfaces:
- DeviceIndex: 0
SubnetId: !Ref PrivateSubnet
GroupSet:
- !Ref InstanceSecurityGroup
UserData: !Base64 |
#!/bin/bash -xe
yum update -y
yum install -y mariadb
Code language: YAML (yaml)Define the initialization process of the instance with user data.
Install the MySQL client package to connect to a MySQL type DB instance from an EC2 instance.
For more information, please refer to the following page
Architecting
Use CloudFormation to build this environment and check the actual behavior.
Preliminary Preparation
As a preliminary preparation, prepare a deployment package for the Lambda layer.
Specifically, execute the following commands
$ mkdir python
$ pip3 install mysql-connector-python -t ./python
$ zip -r layer.zip python
Code language: Bash (bash)Upload the created deployment package to the S3 bucket.
Create CloudFormation stacks and check resources in stacks
Create a CloudFormation stacks.
For information on how to create stacks and check each stack, please refer to the following page
After checking the resources in each stack, information on the main resources created this time is as follows
- RDS DB instance: dbinstance
- RDS DB instance endpoint: dbinstance.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com
- EC2 instance: i-0b3d18375ca48f06b
- Lambda function: fa-062-function
- Parameters in SSM parameter store: fa-062
Check each resource from the AWS Management Console.
First, check the CloudFormation custom resource.
You can see that the Lambda function and the custom resource itself have been successfully created.
Next, check the values stored in the SSM parameter store.
You can see that the SQL statement for DB initialization is saved.
Checking Action
Now that everything is ready, let’s check the Operation.
Execution Result of Lambda Function
First, check the execution result of the Lambda function in the CloudWatch Logs log group.
From the log, we can see that the SQL stored in the SSM parameter store is executed one statement at a time.
This means that the DB initialization process was successfully executed.
We can also see that the function returns “SUCCESS” as a CloudFormation custom resource.
This means that the function has successfully acted as a custom resource.
EC2 Instance
Next, connect to the EC2 instance and check the initialization status of the DB.
Use SSM Session Manager to access the instance.
% aws ssm start-session --target i-0b3d18375ca48f06b
Starting session with SessionId: root-0b2e1f71f0d2aea98
sh-4.2$
Code language: Bash (bash)For more information on SSM Session Manager, please refer to the following page
First, check the execution status of the initialization process of the EC2 instance with user data.
sh-4.2$ yum list installed | grep mariadb
mariadb.aarch64 1:5.5.68-1.amzn2 @amzn2-core
mariadb-libs.aarch64 1:5.5.68-1.amzn2 installed
sh-4.2$ mysql -V
mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (aarch64) using readline 5.1
Code language: Bash (bash)You will see that the MySQL client package has been successfully installed.
Use this client package to connect to the DB instance.
sh-4.2$ mysql -h dbinstance.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com -P 3306 -u testuser -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.28 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)]>
Code language: Bash (bash)You have successfully connected.
Select DB.
MySQL [(none)]> USE tutorial;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [tutorial]>
Code language: Bash (bash)You could change the DB.
This means that the initialization process with the CloudFormation custom resource has been successfully executed.
Finally, we retrieve all records.
MySQL [tutorial]> select * from planet;
+----+---------+
| id | name |
+----+---------+
| 1 | Mercury |
| 2 | Venus |
| 3 | Earth |
| 4 | Mars |
| 5 | Jupiter |
| 6 | Saturn |
| 7 | Uranus |
| 8 | Neptune |
+----+---------+
8 rows in set (0.01 sec)
Code language: Bash (bash)A test record has been returned.
It means that the CREATE TABLE and INSERT statements were successfully executed.
As described above, we were able to initialize the DB instance with the CloudFormation custom resource.
Summary
We have shown how to initialize a DB using CloudFormation custom resources.