How to use Terraform to execute SQL script on RDS MySQL?

Question

I created the aws_db_instance to provision the RDS MySQL database using Terraform configuration. Now my next question is to execute the SQL Script (CREATE TABLE and INSERT statements) on the RDS. I did the following but there is no effect. terraform plan cannot even see my changes on executing the sql. What did I miss here? Thanks.

resource "aws_db_instance" "mydb" {
  # ...

  provisioner "remote-exec" {
    inline = [
      "chmod +x script.sql",
      "script.sql args",
    ]
  }
}

I don't think remote-exec is what you need. You need to use `local-exec` provisioner to achieve this. Documentation: https://www.terraform.io/docs/provisioners/local-exec.html. I believe, `remote-exec` is similar to user data (terraform logs into the instance - linux/windows and runs some script there). — krishna_mee2004, Mar 28 '18 at 18:30
if you switch to local exec, you'll need to open your IP address on the security group, also you need to be able to connect to it, either thru a vpn or a if the db is on a public subnet then your ip needs to be open. — strongjz, Mar 28 '18 at 18:54
Does this answer your question? [How to apply SQL Scripts on RDS with Terraform](https://stackoverflow.com/questions/45394458/how-to-apply-sql-scripts-on-rds-with-terraform) — Daniel Widdis, Oct 11 '20 at 15:24

score 3 · Answer 1 · edited Oct 16 '20 at 20:23

Check out this post: How to apply SQL Scripts on RDS with Terraform

If you're just trying to setup user's and permissions (you shouldn't use the root pw you set when you generate the RDS) there is a terraform provider for that:

https://www.terraform.io/docs/providers/mysql/index.html

But you're looking for DB schema and seeding. That provider cannot do that.

If you're open to doing it another way, you may want to check out using ssm automation documents and/or lambda. I'd use lambda. Pick a language that you're comfortable with. Set the role of the lambda to have permissions to read the password it needs to do the work. You can save the password in ssm parameter store. Then script your DB work.

Then do a local exec in terraform that simply calls the lambda and pass it the ID of the RDS and the path to the secret in ssm parameter store. That will ensure that the DB operations are done from compute inside the VPC without having to setup an EC2 bastion just for that purpose.

Here's how javascript can get this done, for example: https://www.w3schools.com/nodejs/nodejs_mysql_create_table.asp

How to use Terraform to execute SQL script on RDS MySQL?

1 Answers1