Got it, let's walk through how to handle MySQL operations (like creating databases, users, and running SQL queries) on your already-deployed RDS instance using Terraform provisioners. I'll break this down into actionable steps with examples so you can implement it easily.

Before diving in, make sure you have these covered:

• The MySQL client installed on the machine where you run Terraform
• Your RDS instance is in the available state (check via AWS Console or aws rds describe-db-instances)
• Your RDS security group allows inbound traffic on port 3306 from the IP of your Terraform execution machine
• You have the RDS admin username and password handy (store these as sensitive Terraform variables, not hardcoded!)

The local-exec provisioner runs commands directly on your local machine, which is perfect for interacting with RDS as long as you can reach the instance's endpoint. We'll use a null_resource to attach the provisioner—this lets us trigger the MySQL operations even if your RDS resource was already created before adding this code.

Here's a complete example:

# Your existing RDS instance configuration (already deployed)
resource "aws_db_instance" "my_rds" {
  identifier             = "my-production-rds"
  engine                 = "mysql"
  engine_version         = "8.0"
  instance_class         = "db.t3.micro"
  allocated_storage      = 20
  storage_type           = "gp3"
  publicly_accessible    = true # Adjust based on your setup
  db_name                = "initial_db" # Optional, we'll create more below
  username               = var.rds_admin_username
  password               = var.rds_admin_password
  skip_final_snapshot    = true
  vpc_security_group_ids = [aws_security_group.rds_sg.id]
}

# Resource to trigger MySQL setup operations
resource "null_resource" "mysql_post_deploy" {
  # Re-run this provisioner if RDS instance state changes (e.g., reboot, endpoint update)
  triggers = {
    rds_endpoint = aws_db_instance.my_rds.endpoint
    rds_status   = aws_db_instance.my_rds.status
  }

  provisioner "local-exec" {
    command = <<EOT
      # Wait until RDS is reachable (avoids race conditions)
      until mysql -h ${aws_db_instance.my_rds.endpoint} -P 3306 -u ${var.rds_admin_username} -p$RDS_ADMIN_PASS -e "SELECT 1"; do
        echo "Waiting for RDS to become accessible..."
        sleep 10
      done

      # Create a new database if it doesn't exist
      mysql -h ${aws_db_instance.my_rds.endpoint} -P 3306 -u ${var.rds_admin_username} -p$RDS_ADMIN_PASS -e "CREATE DATABASE IF NOT EXISTS ${var.app_db_name};"

      # Create a dedicated application user and grant privileges
      mysql -h ${aws_db_instance.my_rds.endpoint} -P 3306 -u ${var.rds_admin_username} -p$RDS_ADMIN_PASS -e "CREATE USER IF NOT EXISTS '${var.app_db_user}'@'%' IDENTIFIED BY '$APP_DB_PASS';"
      mysql -h ${aws_db_instance.my_rds.endpoint} -P 3306 -u ${var.rds_admin_username} -p$RDS_ADMIN_PASS -e "GRANT ALL PRIVILEGES ON ${var.app_db_name}.* TO '${var.app_db_user}'@'%';"
      mysql -h ${aws_db_instance.my_rds.endpoint} -P 3306 -u ${var.rds_admin_username} -p$RDS_ADMIN_PASS -e "FLUSH PRIVILEGES;"

      # Run a SQL script to populate tables/data
      mysql -h ${aws_db_instance.my_rds.endpoint} -P 3306 -u ${var.app_db_user} -p$APP_DB_PASS ${var.app_db_name} < ./scripts/init_schema.sql
    EOT

    # Pass sensitive passwords via environment variables to avoid logging them
    environment = {
      RDS_ADMIN_PASS = var.rds_admin_password
      APP_DB_PASS    = var.app_db_password
    }
  }
}

# Define your sensitive variables (store these in Terraform Cloud, .tfvars, or env vars)
variable "rds_admin_username" {
  type        = string
  description = "RDS admin username"
}

variable "rds_admin_password" {
  type        = string
  description = "RDS admin password"
  sensitive   = true
}

variable "app_db_name" {
  type        = string
  description = "Application database name"
  default     = "my_app_db"
}

variable "app_db_user" {
  type        = string
  description = "Application database user"
  default     = "app_user"
}

variable "app_db_password" {
  type        = string
  description = "Application database user password"
  sensitive   = true
}

Key Details About This Setup: #

• The triggers block ensures the provisioner re-runs if your RDS endpoint or status changes (e.g., after a reboot)
• The until loop waits for RDS to be reachable—critical because RDS can take a minute or two to accept connections even after showing as "available"
• Using environment variables for passwords prevents them from being logged in Terraform output (Terraform automatically redacts sensitive variables)

• Never hardcode passwords: Use sensitive Terraform variables, and store them securely (Terraform Cloud, AWS Secrets Manager, or encrypted .tfvars files)
• Restrict security group access: Don't open port 3306 to 0.0.0.0/0—only allow the IP of your Terraform execution machine and your application servers
• Use IAM Database Authentication (Optional): For extra security, enable IAM auth on your RDS instance and generate temporary tokens instead of using static passwords. Here's a quick snippet for that:

  command = <<EOT
    TOKEN=$(aws rds generate-db-auth-token --hostname ${aws_db_instance.my_rds.endpoint} --port 3306 --username ${var.rds_admin_username})
    mysql -h ${aws_db_instance.my_rds.endpoint} -P 3306 -u ${var.rds_admin_username} --ssl-ca=./rds-ca-2024.pem --enable-cleartext-plugin --password=$TOKEN -e "CREATE DATABASE ${var.app_db_name};"
  EOT
  

• Limit privileges: Don't grant ALL PRIVILEGES unless necessary—grant only the permissions your application needs (e.g., SELECT, INSERT, UPDATE)

If your RDS instance is in a private subnet and your local machine can't reach it, you can use remote-exec to run commands on a bastion host (jump box) that has access to the private subnet. The setup is similar, but you'll need to configure SSH access to the bastion.

内容的提问来源于stack exchange，提问作者Nikit Swaraj