Setup on MySQL on Google Cloud Compute Engine

my_sql_on_GCP.md

Create two instances for MySQL—a client and a server instance.

To set your Cloud Platform project in this session use “gcloud config set project [PROJECT_ID]”

gcloud config set project mysql-remote-access-267116

Create the client and server VM instances.

client:

gcloud compute instances create my-client --zone us-central1-f --image-project ubuntu-os-cloud --image-family ubuntu-1804-lts --scopes https://www.googleapis.com/auth/cloud-platform

Server:

gcloud compute instances create my-server --zone us-central1-f --image-project ubuntu-os-cloud --image-family ubuntu-1804-lts --scopes https://www.googleapis.com/auth/cloud-platform

Install packages on client and server VMs

on client:

sudo apt-get update sudo apt-get -y install mysql-client-5.7

on server:

sudo apt-get update sudo apt-get -y install mysql-server-5.7 sudo mysql_secure_installation -press any key to skip the setting up the VALIDATE PASSWORD plugin -set up the root password(to be used for the root user access) -choose y for all the following questions to finish up.

Configuring the MySQL server

configure the database to listen on its internal IP address by updating the mysqld.cnf configuration file

LOCAL_IP=$(curl http://metadata.google.internal/computeMetadata/v1/instance/network-interfaces/0/ip -H "Metadata-Flavor: Google") sudo sed -i "s|bind-address.*|bind-address = $LOCAL_IP|" /etc/mysql/mysql.conf.d/mysqld.cnf

restart and check if the databased can be accessed locally.

sudo service mysql restart sudo mysql --user=root -p=[root user password you set] -e "show databases"

Create a non-root MySQL user for accessing remotely

create an environment variable for the my-client internal IP address.

CLIENT_IP=$(gcloud compute instances describe my-client
--zone=us-central1-f
--format='value(networkInterfaces[0].networkIP)')

Create a new MySQL user

create user account at the client IP.

sudo mysql -uroot -p=[root user password you set]
-e "CREATE USER 'username'@'${CLIENT_IP}' IDENTIFIED BY '[MY_PASSWORD]';"

show the user list using root account

sudo mysql -uroot -p=[root user password you set] -e "use mysql; select user from user;"

Grant the new MySQL user permission to log on to the server from the internal IP address of my-client.

sudo mysql -uroot -p=[ROOT_PASSWORD] -e
"GRANT ALL PRIVILEGES ON . TO 'username'@'${CLIENT_IP}'
IDENTIFIED BY '[MY_PASSWORD]';"

test connection to the MySQL server on my-server from my-client instance

sudo mysql --host=my-server --user=username
--password=your_password -e "SHOW DATABASES;"

set up firewall rules

Add tags

gcloud compute instances add-tags my-client --tags mysql-client --zone=us-central1-f gcloud compute instances add-tags my-server --tags mysql-server --zone=us-central1-f

create a firewall rule

gcloud compute firewall-rules create "mysql-remote-access"
--allow tcp:3306 --source-tags "mysql-client"
--target-tags "mysql-server"