$ cat main.tf
/*
* Copyright 2017 Google Inc.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
provider "google" {
version = "~> 2.13"
}
provider "google-beta" {
version = "~> 2.13"
}
provider "random" {
version = "~> 2.2"
}
resource "random_id" "name" {
byte_length = 2
}
resource "google_sql_database_instance" "master" {
name = "example-mysql-${random_id.name.hex}"
project = var.project
region = var.region
database_version = var.database_version
master_instance_name = var.master_instance_name
settings {
tier = var.tier
activation_policy = var.activation_policy
authorized_gae_applications = var.authorized_gae_applications
disk_autoresize = var.disk_autoresize
dynamic "backup_configuration" {
for_each = [var.backup_configuration]
content {
binary_log_enabled = lookup(backup_configuration.value, "binary_log_enabled", null)
enabled = lookup(backup_configuration.value, "enabled", null)
start_time = lookup(backup_configuration.value, "start_time", null)
}
}
dynamic "ip_configuration" {
for_each = [var.ip_configuration]
content {
ipv4_enabled = lookup(ip_configuration.value, "ipv4_enabled", true)
private_network = lookup(ip_configuration.value, "private_network", null)
require_ssl = lookup(ip_configuration.value, "require_ssl", null)
dynamic "authorized_networks" {
for_each = lookup(ip_configuration.value, "authorized_networks", [])
content {
expiration_time = lookup(authorized_networks.value, "expiration_time", null)
name = lookup(authorized_networks.value, "name", null)
value = lookup(authorized_networks.value, "value", null)
}
}
}
}
dynamic "location_preference" {
for_each = [var.location_preference]
content {
follow_gae_application = lookup(location_preference.value, "follow_gae_application", null)
zone = lookup(location_preference.value, "zone", null)
}
}
dynamic "maintenance_window" {
for_each = [var.maintenance_window]
content {
day = lookup(maintenance_window.value, "day", null)
hour = lookup(maintenance_window.value, "hour", null)
update_track = lookup(maintenance_window.value, "update_track", null)
}
}
disk_size = var.disk_size
disk_type = var.disk_type
pricing_plan = var.pricing_plan
replication_type = var.replication_type
availability_type = var.availability_type
}
dynamic "replica_configuration" {
for_each = [var.replica_configuration]
content {
ca_certificate = lookup(replica_configuration.value, "ca_certificate", null)
client_certificate = lookup(replica_configuration.value, "client_certificate", null)
client_key = lookup(replica_configuration.value, "client_key", null)
connect_retry_interval = lookup(replica_configuration.value, "connect_retry_interval", null)
dump_file_path = lookup(replica_configuration.value, "dump_file_path", null)
failover_target = lookup(replica_configuration.value, "failover_target", null)
master_heartbeat_period = lookup(replica_configuration.value, "master_heartbeat_period", null)
password = lookup(replica_configuration.value, "password", null)
ssl_cipher = lookup(replica_configuration.value, "ssl_cipher", null)
username = lookup(replica_configuration.value, "username", null)
verify_server_certificate = lookup(replica_configuration.value, "verify_server_certificate", null)
}
}
timeouts {
create = "60m"
delete = "2h"
}
}
resource "google_sql_database" "default" {
count = var.master_instance_name == "" ? 1 : 0
name = var.db_name
project = var.project
instance = google_sql_database_instance.master.name
charset = var.db_charset
collation = var.db_collation
}
resource "random_id" "user-password" {
byte_length = 8
}
resource "google_sql_user" "default" {
count = var.master_instance_name == "" ? 1 : 0
name = var.user_name
project = var.project
instance = google_sql_database_instance.master.name
host = var.user_host
password = var.user_password == "" ? random_id.user-password.hex : var.user_password
}
$ cat variables.tf
/*
* Copyright 2017 Google Inc.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
variable "project" {
description = "The project to deploy to, if not set the default provider project is used."
default = ""
}
variable "region" {
description = "Region for cloud resources"
default = "us-central1"
}
variable "database_version" {
description = "The version of of the database. For example, `MYSQL_5_6` or `POSTGRES_9_6`."
default = "MYSQL_5_6"
}
variable "master_instance_name" {
description = "The name of the master instance to replicate"
default = ""
}
variable "tier" {
description = "The machine tier (First Generation) or type (Second Generation). See this page for supported tiers and pricing: https://cloud.google.com/sql/pricing"
default = "db-f1-micro"
}
variable "db_name" {
description = "Name of the default database to create"
default = "default"
}
variable "db_charset" {
description = "The charset for the default database"
default = ""
}
variable "db_collation" {
description = "The collation for the default database. Example for MySQL databases: 'utf8_general_ci', and Postgres: 'en_US.UTF8'"
default = ""
}
variable "user_name" {
description = "The name of the default user"
default = "default"
}
variable "user_host" {
description = "The host for the default user"
default = "%"
}
variable "user_password" {
description = "The password for the default user. If not set, a random one will be generated and available in the generated_user_password output variable."
default = ""
}
variable "activation_policy" {
description = "This specifies when the instance should be active. Can be either `ALWAYS`, `NEVER` or `ON_DEMAND`."
default = "ALWAYS"
}
variable "authorized_gae_applications" {
description = "A list of Google App Engine (GAE) project names that are allowed to access this instance."
default = []
}
variable "disk_autoresize" {
description = "Second Generation only. Configuration to increase storage size automatically."
default = true
}
variable "disk_size" {
description = "Second generation only. The size of data disk, in GB. Size of a running instance cannot be reduced but can be increased."
default = 10
}
variable "disk_type" {
description = "Second generation only. The type of data disk: `PD_SSD` or `PD_HDD`."
default = "PD_SSD"
}
variable "pricing_plan" {
description = "First generation only. Pricing plan for this instance, can be one of `PER_USE` or `PACKAGE`."
default = "PER_USE"
}
variable "replication_type" {
description = "Replication type for this instance, can be one of `ASYNCHRONOUS` or `SYNCHRONOUS`."
default = "SYNCHRONOUS"
}
variable "database_flags" {
description = "List of Cloud SQL flags that are applied to the database server"
default = []
}
variable "backup_configuration" {
description = "The backup_configuration settings subblock for the database setings"
default = {}
}
variable "ip_configuration" {
description = "The ip_configuration settings subblock"
default = {}
}
variable "location_preference" {
description = "The location_preference settings subblock"
default = {}
}
variable "maintenance_window" {
description = "The maintenance_window settings subblock"
default = {}
}
variable "replica_configuration" {
description = "The optional replica_configuration block for the database instance"
default = {}
}
variable "availability_type" {
description = "This specifies whether a PostgreSQL instance should be set up for high availability (REGIONAL) or single zone (ZONAL)."
default = "ZONAL"
}
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$
$ cat outputs.tf
/*
* Copyright 2017 Google Inc.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
output "instance_name" {
description = "The name of the database instance"
value = google_sql_database_instance.master.name
}
output "instance_address" {
description = "The IPv4 address of the master database instnace"
value = google_sql_database_instance.master.ip_address.0.ip_address
}
output "instance_address_time_to_retire" {
description = "The time the master instance IP address will be retired. RFC 3339 format."
value = google_sql_database_instance.master.ip_address.0.time_to_retire
}
output "self_link" {
description = "Self link to the master instance"
value = google_sql_database_instance.master.self_link
}
output "generated_user_password" {
description = "The auto generated default user password if no input password was provided"
value = random_id.user-password.hex
sensitive = true
}
$ terraform init
$ terraform providers
Providers required by configuration:
.
├── provider[registry.terraform.io/hashicorp/google] ~> 2.13
├── provider[registry.terraform.io/hashicorp/random] ~> 2.2
└── provider[registry.terraform.io/hashicorp/google-beta] ~> 2.13
$ terraform plan -out=tfplan
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:
# google_sql_database.default[0] will be created
+ resource "google_sql_database" "default" {
+ charset = (known after apply)
+ collation = (known after apply)
+ id = (known after apply)
+ instance = (known after apply)
+ name = "default"
+ project = (known after apply)
+ self_link = (known after apply)
}
# google_sql_database_instance.master will be created
+ resource "google_sql_database_instance" "master" {
+ connection_name = (known after apply)
+ database_version = "MYSQL_5_6"
+ first_ip_address = (known after apply)
+ id = (known after apply)
+ ip_address = (known after apply)
+ master_instance_name = (known after apply)
+ name = (known after apply)
+ private_ip_address = (known after apply)
+ project = (known after apply)
+ public_ip_address = (known after apply)
+ region = "us-central1"
+ self_link = (known after apply)
+ server_ca_cert = (known after apply)
+ service_account_email_address = (known after apply)
+ replica_configuration {}
+ settings {
+ activation_policy = "ALWAYS"
+ authorized_gae_applications = []
+ availability_type = "ZONAL"
+ crash_safe_replication = (known after apply)
+ disk_autoresize = true
+ disk_size = 10
+ disk_type = "PD_SSD"
+ pricing_plan = "PER_USE"
+ replication_type = "SYNCHRONOUS"
+ tier = "db-f1-micro"
+ version = (known after apply)
+ backup_configuration {
+ start_time = (known after apply)
}
+ ip_configuration {
+ ipv4_enabled = true
}
+ location_preference {}
+ maintenance_window {}
}
+ timeouts {
+ create = "60m"
+ delete = "2h"
}
}
# google_sql_user.default[0] will be created
+ resource "google_sql_user" "default" {
+ host = "%"
+ id = (known after apply)
+ instance = (known after apply)
+ name = "default"
+ password = (sensitive value)
+ project = (known after apply)
}
# random_id.name will be created
+ resource "random_id" "name" {
+ b64 = (known after apply)
+ b64_std = (known after apply)
+ b64_url = (known after apply)
+ byte_length = 2
+ dec = (known after apply)
+ hex = (known after apply)
+ id = (known after apply)
}
# random_id.user-password will be created
+ resource "random_id" "user-password" {
+ b64 = (known after apply)
+ b64_std = (known after apply)
+ b64_url = (known after apply)
+ byte_length = 8
+ dec = (known after apply)
+ hex = (known after apply)
+ id = (known after apply)
}
Plan: 5 to add, 0 to change, 0 to destroy.
Changes to Outputs:
+ generated_user_password = (sensitive value)
+ instance_address = (known after apply)
+ instance_address_time_to_retire = (known after apply)
+ instance_name = (known after apply)
+ self_link = (known after apply)
Saved the plan to: tfplan
To perform exactly these actions, run the following command to apply:
terraform apply "tfplan"
$ terraform apply tfplan
random_id.user-password: Creating...
random_id.name: Creating...
random_id.name: Creation complete after 0s [id=g90]
random_id.user-password: Creation complete after 0s [id=z3WVxe8AvS8]
google_sql_database_instance.master: Creating...
google_sql_database_instance.master: Still creating... [10s elapsed]
google_sql_database_instance.master: Still creating... [4m50s elapsed]
google_sql_database_instance.master: Creation complete after 4m51s [id=example-mysql-83dd]
google_sql_database.default[0]: Creating...
google_sql_user.default[0]: Creating...
google_sql_user.default[0]: Creation complete after 1s [id=default/%/example-mysql-83dd]
google_sql_database.default[0]: Creation complete after 1s [id=example-mysql-83dd:default]
Apply complete! Resources: 5 added, 0 changed, 0 destroyed.
Outputs:
generated_user_password = <sensitive>
instance_address = "34.16.6.104"
instance_address_time_to_retire = ""
instance_name = "example-mysql-83dd"
self_link = "https://www.googleapis.com/sql/v1beta4/projects/qwiklabs-gcp-02-d67626ac6119/instances/example-mysql-83dd"
$ ls -al
-rw-r--r-- 1 student_04_c8c5bf4567bf student_04_c8c5bf4567bf 5095 Dec 3 2019 main.tf
-rw-r--r-- 1 student_04_c8c5bf4567bf student_04_c8c5bf4567bf 1457 Dec 3 2019 outputs.tf
drwxr-xr-x 3 student_04_c8c5bf4567bf student_04_c8c5bf4567bf 4096 Jul 29 21:23 .terraform
-rw-r--r-- 1 student_04_c8c5bf4567bf student_04_c8c5bf4567bf 3194 Jul 29 21:23 .terraform.lock.hcl
-rw-r--r-- 1 student_04_c8c5bf4567bf student_04_c8c5bf4567bf 8717 Jul 29 21:30 terraform.tfstate
-rw-r--r-- 1 student_04_c8c5bf4567bf student_04_c8c5bf4567bf 7830 Jul 29 21:24 tfplan
-rw-r--r-- 1 student_04_c8c5bf4567bf student_04_c8c5bf4567bf 4206 Dec 3 2019 variables.tf
$ terraform state list
google_sql_database.default[0]
google_sql_database_instance.master
google_sql_user.default[0]
random_id.name
random_id.user-password
04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$ MYSQL_DB_NAME=$(terraform output -json | jq -r '.instance_name.value')
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$ echo $MYSQL_DB_NAME
example-mysql-83dd
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$ MYSQL_CONN_NAME="${GOOGLE_PROJECT}:us-central1:${MYSQL_DB_NAME}"
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$ echo $MYSQL_CONN_NAME
qwiklabs-gcp-02-d67626ac6119:us-central1:example-mysql-83dd
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$ ./cloud_sql_proxy -instances=${MYSQL_CONN_NAME}=tcp:3306
2023/07/29 21:36:36 current FDs rlimit set to 1048576, wanted limit is 8500. Nothing to do here.
2023/07/29 21:36:37 Listening on 127.0.0.1:3306 for qwiklabs-gcp-02-d67626ac6119:us-central1:example-mysql-83dd
2023/07/29 21:36:37 Ready for new connections
2023/07/29 21:36:38 Generated RSA key in 376.968708ms
2023/07/29 21:37:56 New connection for "qwiklabs-gcp-02-d67626ac6119:us-central1:example-mysql-83dd"
2023/07/29 21:37:56 refreshing ephemeral certificate for instance qwiklabs-gcp-02-d67626ac6119:us-central1:example-mysql-83dd
2023/07/29 21:37:56 Scheduling refresh of ephemeral certificate in 55m0s
2023/07/29 21:37:56 Client closed local connection on 127.0.0.1:3306
2023/07/29 21:38:16 New connection for "qwiklabs-gcp-02-d67626ac6119:us-central1:example-mysql-83dd"
2023/07/29 21:40:30 Client closed local connection on 127.0.0.1:3306
^C2023/07/29 21:40:34 Received TERM signal. Waiting up to 0s before terminating.
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$ pwd
/home/student_04_c8c5bf4567bf/sql-with-terraform
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$ cat main.tf
/*
* Copyright 2017 Google Inc.
project [PROJECT_ID]” to change to a different project.
student_04_c8c5bf4567bf@cloudshell:~ (qwiklabs-gcp-02-d67626ac6119)$ cd ~/sql-with-terraform
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$ echo MYSQL_PASSWORD=$(terraform output -json | jq -r '.generated_user_password.value')
MYSQL_PASSWORD=cf7595c5ef00bd2f
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$ mysql -udefault -p --host 127.0.0.1 default
Enter password:
ERROR 1045 (28000): Access denied for user 'default'@'cloudsqlproxy~35.233.169.10' (using password: YES)
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$ mysql -udefault -p --host 127.0.0.1 default
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 83
Server version: 5.6.51-google (Google)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> use bike;
ERROR 1049 (42000): Unknown database 'bike'
mysql> select * from london1;
ERROR 1146 (42S02): Table 'default.london1' doesn't exist
mysql> create database ketan;
Query OK, 1 row affected (0.04 sec)
mysql> use ketan;
Database changed
mysql> create table ketantable;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table ketantable
-> id(num)
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id(num)' at line 2
mysql> exit
Bye
student_04_c8c5bf4567bf@cloudshell:~/sql-with-terraform (qwiklabs-gcp-02-d67626ac6119)$
No comments:
Post a Comment