insert data on mysql with terraform

Google Cloud Database
1

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

2

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.
  1. 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.
  1. 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.
  1. 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.
  1. 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.
2 Likes