Solved: Re: insert data on mysql with terraform

joarodrigues · 12-27-2023 03:51 AM

Hello

I have a terraform script that creates a mysql instance correctly.

I canot find a way to execute a sql files containing all my insert statements automaticaly with teraform

Can someone help ?

Thanks

ms4446

Executing SQL files containing insert statements automatically with Terraform on a Google Cloud MySQL instance involves a few steps. Terraform itself does not directly execute SQL queries; it's primarily used for infrastructure as code. However, you can use Terraform to set up the necessary infrastructure and then use additional tools or scripts to execute your SQL files.

Here's a general approach to achieve this:

1. Create the MySQL Instance with Terraform:

Utilize Terraform to provision the MySQL instance on Google Cloud.
Verify its correct configuration and accessibility.

2. Store SQL Files:

Place your SQL files in a location accessible to the system running Terraform.
This could be your local system, a cloud storage bucket, or a version control repository.

3. Employ a Provisioner:

Use Terraform's local-exec provisioner to execute scripts after the MySQL instance is created.
This provisioner allows for script execution on the local machine.

4. Craft a Script to Execute SQL Files:

Write a shell script (or a script in another language) to connect to the MySQL database and execute the SQL commands in your file.
This script will be invoked by the Terraform provisioner.

5. Integrate the Script with Terraform:

Within your Terraform configuration, use the local-exec provisioner to call your script.
Configure it to trigger after the MySQL instance is successfully created.

Example Terraform Configuration:

resource "google_sql_database_instance" "default" {
  // ... configuration for the MySQL instance ...
}

resource "null_resource" "db_setup" {
  depends_on = [google_sql_database_instance.default]

  provisioner "local-exec" {
    command = "sh execute-sql.sh"
  }
}

Example execute-sql.sh Script:

 
#!/bin/bash
# execute-sql.sh

# Connect to the database and execute SQL file
mysql -h [HOSTNAME] -u [USERNAME] -p[PASSWORD] [DATABASE] < /path/to/your/sqlfile.sql 
Replace placeholders with actual database credentials.

Important Considerations:

Security: Handle credentials with care. Avoid hardcoding them in scripts or Terraform files. Use environment variables or a secret management solution.
Idempotency: Ensure SQL script execution can be repeated without issues. Terraform is designed to be idempotent.
Error Handling: Implement robust error handling in your script. Report any SQL execution failures clearly.
Testing: Thoroughly test your script and Terraform configuration in a safe environment before deploying to production.

View solution in original post

ms4446