CloudFormation : Template RDS Mysql to Create DB, Tables, User, or Load Schema

Question

Using CloudFormation, I'm trying to figure out a few use cases.

The first case is having an existing AWS RDS MySQL server, how to create a database, tables, and a user account though a CloudFormation template.

After looking at the Cloudformation docs, I thought there could be a AWS::RDS::DBInstance property that would allow me to do this action; however, though I couldn't find how to specify a database host source (or I may have misread it) that would allow.

aws-properties-rds-database-instance

then, looking at the Cloudformation RDS templates from AWS, I saw an fn::join command that stood out. Would my first case be done with fn::join?

"UserData": { "Fn::Base64": { "Fn::Join": ["", [....

Next, in the second case, I'm also having trouble finding how using an existing RDS MySQL server, load a scheme from a MySQL mysqldump.

I've looked at the docs in the link above for answers but not quite sure. I noticed AWS templates mostly allow you to create resources.

Lastly, to solve the above two cases, I've also been reading that it could or should(?) be done using a two tool approach by way of using an AWS CloudFormation template and calls to Chef.

Thoughts?

Answer 1

Short answer is : you can't, right now, automatically run SQL queries at instance creation (in the future, who knows...).

I would use the following way to resolve this classic problem:

• Use an EC2 to run the MySQL code you need (not sure if you need to put a "DependsOn" clause in the EC2, since you will have to refer to the RDS endpoint with a GetAtt, which will probably put an implicit DependsOn clause). You put this in the user data or any script you want, as long as you pass the RDS endpoint somehow. Of course, you will need the MySQL client package installed on your EC2.

As you wrote, CloudFormation allows you to create AWS resources, but populationg those resources is up to you. Once again, a MySQL client will do the trick (since a MySQL dump is no more than SQL code in a file, same as your initial requirement).

Using Chef or Puppet will not solve your problem alone, since both need an agent installed on an EC2, you cannot run your agent in the RDS server. So you can have an EC2 using a Chef or Puppet agent, which will in turn launch the MySQL script, but this will not give you much more than the previous solution