Creating Compute Engine instances

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

Create a Compute Engine client instance

gcloudcomputeinstancescreatemy-client --zone=ZONE --image-project=ubuntu-os-cloud --image-family=ubuntu-1804-lts --scopes=https://www.googleapis.com/auth/cloud-platform

Create a Compute Engine server instance

gcloudcomputeinstancescreatemy-server --zone=ZONE --image-project=ubuntu-os-cloud --image-family=ubuntu-1804-lts --scopes=https://www.googleapis.com/auth/cloud-platform

Installing MySQL client

The following steps describe how to install MySQL on a Compute Engineinstance.

1. To connect to themy-client instance, use thessh command.
2. Update theapt-get package manager.

   sudo apt-get update

   Note: If you receive an errorCould not get lock /var/lib/dpkg/lock, wait another minute for the boot processes to complete and try again.
3. Install theMySQL client package.

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

Installing MySQL server

The following steps describe how to install MySQL on a Compute Engineinstance.

1. To connect to themy-server instance, use thessh command.
2. Update theapt-get package manager.

   sudo apt-get update

   Note: If you receive an error, "Could not get lock /var/lib/dpkg/lock'", wait another minute for the boot processes to complete and try again.
3. Install theMySQL server package.

   sudo apt-get -y install mysql-server-5.7

Improve MySQL installation security

You must establish a root passwordfor MySQL and perform basic security maintenance on your MySQL serverconfiguration. For more information, see the MySQLdocumentation formysql_secure_installation.

1. In the SSH session to yourmy-server instance, use the following command to improve the security of your MySQL installation.

   sudomysql_secure_installation

2. Pressenter to skip setting up theVALIDATE PASSWORD plugin.

3. Enter a new root password twice.

4. To remove anonymous users, enterY and pressenter.

5. To prevent remote root login, enterY and pressenter.

6. To remove the test database, enterY and pressenter.

7. To reload the privilege tables, enterY and pressenter.

Configuring the MySQL server

Before you can remotely connect to the MySQL server, you need to configure it tolisten on its internal IP address. Then, you create a non-root user account forthe MySQL client to connect to the server.

All MySQL client commands must include certaincommand-line flags (for example, to authenticate). The MySQL commands in thissection include the following flags:--user for the username,-p for thepassword, and-e to execute the given statement and immediately quit. Formore information, see theMySQL 5.7 command options reference.

1. In Cloud Shell, use SSH to connect to themy-server instance.

2. Update the/etc/mysql/mysql.conf.d/mysqld.cnf configuration file with thefollowing information:

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

3. Restart the MySQL service to apply the changes to the running server.

   sudoservicemysqlrestart

4. Verify that the server is running locally. Replace[ROOT_PASSWORD] withthe MySQL server root password you established in a previous step.

   sudomysql--user=root-p[ROOT_PASSWORD]-e"show databases"

   The output appears similar to the following:

   +--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+

Create a MySQL user

Remotely connecting as the root user was disabled with the precedingmysql_secure_installation command. You need to create a new user with thenecessary permissions to allow remote connections.

1. In Cloud Shell, create an environment variable for themy-clientinternal IP address.

   CLIENT_IP=$(gcloudcomputeinstancesdescribemy-client\--zone=ZONE\--format='value(networkInterfaces[0].networkIP)')

2. Create a new MySQL user with a password. Replace[MY_PASSWORD] withyour password,[ROOT_PASSWORD] with your MySQL root user password.

   sudomysql-uroot-p[ROOT_PASSWORD]\-e"CREATE USER 'TESTUSER'@'${CLIENT_IP}' IDENTIFIED BY '[MY_PASSWORD]';"

3. Grant the new MySQL user permission to log on to the server from theinternal IP address ofmy-client.

   sudomysql-uroot-p[ROOT_PASSWORD]-e\"GRANT ALL PRIVILEGES ON *.* TO 'TESTUSER'@'${CLIENT_IP}'\    IDENTIFIED BY '[MY_PASSWORD]';"

Remove the external IP address formy-server

Themy-server instance doesn't need an external IP address because theclient can accessmy-server through an internal IP address.

• To remove the external IP address, update the configuration settings inCloud Shell. Replace[ZONE] with your Google Cloud zone.

  gcloudcomputeinstancesdelete-access-configmy-server\--access-config-name"external-nat"\--zone="ZONE"

Verifying remote access from client to server instance

The following steps describe how to connect to the MySQL server onmy-serverfrom yourmy-client instance.

1. In Cloud Shell, use SSH to connect tomy-client instance.

2. Test your connection by listing the databases.

   sudomysql--host=my-server--user=TESTUSER\--password=[MY_PASSWORD]-e"SHOW DATABASES;"

   Output appears similar to the following:

   +--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+

These steps verify that your MySQL client can successfully connect to the MySQLserver over the internal IP address.

Firewall considerations in production environments

The default network configuration in Google Cloud includes a firewall rule,default-allow-internal, that allows internal traffic betweenCompute Engine instances on a wide range of ports, including the MySQLport, port3306. In non-default environments with an established securityfootprint, you might need to create a firewall rule to allow yourmy-clientinstance to communicate with yourmy-server instance over the network. Otherwise,the two instances can't talk to each other.

You can base firewall rules on IPaddress ranges or tags. IP address ranges are useful if you want to grant accessto a wide range of internal IP addresses. Alternatively, if you want to grantaccess to specific instances on your network, tags provide a more flexiblesolution. Tags make it easier to add new clients without granting access to awide range of IP addresses. You need only assign the appropriate tag to the newMySQL client instance. For example, you can create a new firewall rule thatallows traffic from all client instances that are tagged withmysql-client.

To support firewall rules using tags, you can assign the appropriate tags to themy-client andmy-server VMs in Cloud Shell.

gcloudcomputeinstancesadd-tagsmy-client--tagsmysql-client--zone=ZONEgcloudcomputeinstancesadd-tagsmy-server--tagsmysql-server--zone=ZONE

Add a new firewall rule

The following steps describe how to create a new firewall rule to enableinstances with themy-client tag to communicate with instances that have themy-server tag by using port3306.

• In Cloud Shell, create a firewall rule to allow communicationsfrommysql-client tomysql-server.

  gcloudcomputefirewall-rulescreate"mysql-remote-access"\--allowtcp:3306--source-tags"mysql-client"\--target-tags"mysql-server"

You can now connect to MySQL frommy-client.

Access considerations from external clients

This tutorial covers access from MySQL clients to MySQL servers both running onCompute Engine. It is beyond the scope of this tutorial to allow access from a client not running on Compute Engine. If you need to allow non-Compute Engine access, modify the following:

• Add an external IP address tomy-server to allow externalconnectivity.
• Add the source IP address of your external client to thefirewall rules.
• Modify theTESTUSER account, or create a user account, that is bound tothe source IP address of your external client.