In this tutorial we will provision a MySQL Server with Docker and then use Terraform to provision MySQL Users, Database Schemas and MySQL Grants with the MySQL Terraform Provider.
About
Terraform is super powerful and can do a lot of things. And it shines when it provisions Infrastructure. So in a scenario where we use Terraform to provision RDS MySQL Database Instances, we might still want to provision extra MySQL Users, or Database Schemas and the respective MySQL Grants.
Usually you will logon to the database and create them manually with sql syntax. But in this tutorial we want to make use of Docker to provision our MySQL Server and we would like to make use of Terraform to provision the MySQL Database Schemas, Grants and Users.
Instead of using AWS RDS, I will be provisioning a MySQL Server on Docker so that we can keep the costs free, for those who are following along.
We will also go through the steps on how to rotate the database password that we will be provisioning for our user.
MySQL Server
First we will provision a MySQL Server on Docker Containers, I have a docker-compose.yaml which is available in my quick-starts github repository:
If you don’t have Terraform installed, you can install it from their documentation.
If you want the source code of this example, its available in my terraform-mysql/petoju-provider repository. Which you can clone and jump into the terraform/mysql/petoju-provider directory.
variable "database_name"{description="The name of the database that you want created."type= string
default= null
}variable "database_username"{description="The name of the database username that you want created."type= string
default= null
}variable "password_version"{description="The password rotates when this value gets updated."type= number
default= 0
}
Now we are ready to run our terraform code, which will ultimately create a database, user and grants. Outputs the encrypted string of your password which was encrypted with your keybase_username.
Initialise Terraform:
1
terraform init
Run the plan to see what terraform wants to provision:
1
terraform plan
And we can see the following resources will be created:
Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
+ create
Terraform will perform the following actions:
# mysql_database.user_db will be created + resource "mysql_database""user_db"{ + default_character_set="utf8mb4" + default_collation="utf8mb4_general_ci" + id=(known after apply) + name="foobar"}# mysql_grant.user_id will be created + resource "mysql_grant""user_id"{ + database="foobar" + grant=false + host="%" + id=(known after apply) + privileges=[ + "SELECT",
+ "UPDATE",
] + table="*" + tls_option="NONE" + user="ruanb"}# mysql_user.user_id will be created + resource "mysql_user""user_id"{ + host="%" + id=(known after apply) + plaintext_password=(sensitive value) + tls_option="NONE" + user="ruanb"}# random_password.user_password will be created + resource "random_password""user_password"{ + bcrypt_hash=(sensitive value) + id=(known after apply) + keepers={ + "password_version"="0"} + length= 24
+ lower=true + min_lower= 0
+ min_numeric= 0
+ min_special= 2
+ min_upper= 0
+ number=true + numeric=true + override_special="!#$%^&*()-_=+[]{}<>:?" + result=(sensitive value) + special=true + upper=true}Plan: 4 to add, 0 to change, 0 to destroy.
Changes to Outputs:
+ password=(sensitive value) + user="ruanb"
Run the apply which will create the database, the user, sets the password and applies the grants:
1
terraform apply
Then our returned output should show something like this:
If we want to rotate the mysql password for the user, we can update the password_version variable either in our terraform.tfvars or via the cli. Let’s pass the variable in the cli and do a terraform plan to verify the changes:
1
terraform plan -var password_version=1
And due to our value for the random resource keepers parameter being updated, it will trigger the value of our password to be changed, and that will let terraform update our mysql user’s password:
12345678910111213141516171819202122232425
Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
~ update in-place
-/+ destroy and then create replacement
Terraform will perform the following actions:
# mysql_user.user_id will be updated in-place ~ resource "mysql_user""user_id"{id="ruanb@%" ~ plaintext_password=(sensitive value)# (5 unchanged attributes hidden)}# random_password.user_password must be replaced-/+ resource "random_password""user_password"{ ~ bcrypt_hash=(sensitive value) ~ id="none" -> (known after apply) ~ keepers={# forces replacement ~ "password_version"="0" -> "1"} ~ result=(sensitive value)# (11 unchanged attributes hidden)}Plan: 1 to add, 1 to change, 1 to destroy.
To validate that the password has changed, we can try to logon to mysql by using the password variable that was created initially:
1
docker exec -it mysql mysql -u ruanb -p$DBPASS
And as you can see authentication failed:
12
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045(28000): Access denied for user 'ruanb'@'localhost'(using password: YES)
Set the new password to the variable again:
1
DBPASS=$(terraform output -raw password)
Then try to logon again:
1
docker exec -it mysql mysql -u ruanb -p$DBPASS
And we can see we are logged on again:
12345
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.33 MySQL Community Server - GPL
mysql>