Pieter du Plessis

Here I will be posting about some of the things that I learn and some of my experiences

Connecting to GCP Compute Engine with MySQL on a Remote Host

This will show you how to set up a Compute Engine Virtual Machine Instance on the Google Cloud Platform and how to install a MySQL database and testing the connection with MySQL Workbench. Below is a step-by-step explanation followed by an example with screenshots.

Throughout the article I will be referring to MySQL, although MariaDB is what actually will be installed. MariaDB is forked from MySQL; please refer to the Special Notes before the example.

Steps:

  1. Enable Compute Engine API
  2. Create Compute Engine VM Instance
  3. Install MySQL on VM Instance
  4. Improve MySQL installation Security
  5. Create the User and the Privileges
  6. Configure MySQL Binding Address
  7. Firewall Setup on Google Cloud
  8. Test connection to MySQL on the Instance

There is also an Example at the end.

Requirements:

Step-by-Step Explanation:

Step 1: Enable Compute Engine API

There are 2 options that you can choose from for enabling the API:

Option 1:

Within the Google Cloud Platform:

  • Click on the ‘Products and Services’ button (Top left corner, ).
  • Select ‘APIs & Services’ > ‘Library’.
  • Search for the Compute Engine API and click on it.
  • Select ‘Enable’.

Option 2:

Click here to go straight to enable the API. If you are logged in to Google Cloud Platform it will take you to select the project you want to enable the API for. See the example.

Step 2: Create Compute Engine VM Instance

Within the Google Cloud Platform:

  • Click on the ‘Product and Services’ button (Top left corner, )
  • Select ‘Compute Engine’ > ‘VM instances’.
  • Click on ‘Create Instance’ ().
  • Capture, change and select the fields as required.
  • At the bottom click on the ‘Management, disks, networking, SSH keys’ to show more information ().
  • Under networking add the following tag “mysql-server”. This is required as part of the firewall to access the instance from outside the Google Cloud Platform.
  • Once satisfied, create the instance.
  • This may take a few minutes.

Step 3: Install MySQL on VM Instance

Within the VM Instance page, in the list of virtual machines, connect to the SSH of the instance where you want to install MySQL on. Once the console is running for the instance, you need to follow the following steps:

Sub-Step 1:

Update the apt-get package manager:

$ sudo apt-get update

Always do an update before attempting to install something new, this makes sure that all the required items are up-to-date for the installation.

Sub-Step 2:

$ sudo apt-get –y install mysql-server

The version of MySQL (MariaDB) that will install is the current best version that is available.

Step 4: Improve MySQL Installation Security

To improve the security of the MySQL installation, run the following command and follow the steps through:

$ sudo mysql_secure_installation

With this command you will set up the root password, if not already done, and remove anonymous users, disallow root login remotely, remove test database and access to it and reload privilege table.

When done restart the MySQL server:

$ sudo service mysql restart

Step 5: Create a User and their Privileges

Access MySQL with the root user:

$ sudo mysql –u root –p

Then it will ask for the root user’s password that has been setup in one of the previous steps.

Create a Database (This database will just be used for demonstration purposes):

> CREATE DATABASE testdb;

Create the user and password that we will use to connect to MySQL:

> CREATE USER ‘theUser’@’%’ IDENTIFIED BY ‘thePassword’;

Grant the privileges for the user just created:

> GRANT ALL PRIVILEGES ON testdb.* TO ‘theUser’@’%’;

It is not advised to have a remote user with all privileges. Please see MySQL documents for available privileges that could be given to the user.

Flush the privileges (reload the privilege table):

> FLUSH PRIVILEGES;

Step 6: Setup MySQL Binding Address

Navigate to the server settings file. For my instance it was located in:

$ cd /etc/mysql/mariadb.conf.d/

To access the file with rights to modify the file:

$ sudo nano 50-server.cnf

Find the line with the ‘bind-address’: 

Change the IP address to ‘0.0.0.0’: 

Save and close the file.

Step 7: Firewall Setup on Google Cloud

Within the Google Cloud Platform:

  • Click on the ‘Product and Services’ button (Top left corner, )
  • Select ‘VPC Network’ > ‘Firewall Rules’.
  • Click on ‘Create Instance’ ().
  • Enter a name for the firewall rule.
  • Ensure that by ‘Ingress’ is selected by ‘Direction of traffic’.
  • Ensure that ‘Allow’ is selected by ‘Action on match’.
  • By ‘Target’ select ‘Specified target tags’.
  • By ‘Target tags’ enter ‘mysql-server’.
  • By ‘Source filter’ select ‘IP ranges’.
  • By ‘Source IP ranges’ enter ‘0.0.0.0/0’.
  • Ensure that ‘Specified protocols and ports’ is selected by ‘Protocols and ports’.
  • Enter ‘tcp:3306’ in the field just below (Your port number for MySQL).
  • Click on ‘Create’ to create the rule.

By ‘Source IP ranges’ you can fill in one or more specific IP addresses and only machine where their IP address match will be able to access MySQL. Filling in specific IP addresses is a more secure option than ‘0.0.0.0/0’ as any computer or device with internet will be able to access your MySQL instance. By default the port to MySQL is 3306.

Step 8: Test connection to MySQL on the Instance

Open MySQL Workbench and add a new connection. Use the External IP from the VM instance page for the instance that has the database you want to connect to and fill it in by the hostname:

Fill in the port number, by default it is ‘3306’. The username is the user that you created in step 5. For this example the testdb will be entered by the ‘Default Schema’ as the user only has access to that database on the MySQL server. Once all the information has been entered, test the connection, if it says successful then everything is fine else something went wrong.

Special Notes:

MariaDB is MySQL. Quote from the Wikipedia page of MariaDB:

“MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation”. For compatibility between MariaDB and MySQL please click here. For MariaDB versus MySQL Features, please click here.

At the time of posting all the links worked. I do my utmost best to keep all the links updated as far as possible. This is just a basic setup to connect to the server remotely. It is your own responsibility to administrate and secure your own instance.

Example:

Herewith is an example that I have created for testing the compute engine and the connection. I started off by logging into Google Cloud Platform.

Step 1: Enable Compute Engine API

For enabling the API there are two options.

Option 1:

Go to the API library:

In the API library search for the Compute Engine API and click on it:

Then click on the ‘Enable’ button the enable the API:

Once enabled, you will be taken to the following screen:

Option 2:

Using the link from the explanation while still logged in will take you to the following screen. You can select a current project or create a new one:

Once enabled, you will be directed to the following screen:

Step 2: Create Compute Engine VM Instance

Go to the VM Instances:

From VM Instances, create a new VM Instance:

Capture and fill in the fields as required, I changed the fields marked in red below:

Click on the ‘Management, disks, networking, SSH keys’ button to expand it, then select the ‘Networking’ tab and add the ‘mysql-server’ tag to the network tags. This is required for the firewall rule that we will create later on:

Step 3: Install MySQL on VM Instance

Click on SSH; this will open up a new window to access your instance:

Once connected and ready you will see the following:

Update the system:

Now we will install MySQL. After running the command it will show you the information regarding what you are about to install and let you know how much space will be used and if you want to continue:

After selecting yes it will install MySQL:

You are now done with the installation of MySQL. Next we will improve the security of the installation.

Step 4: Improve MySQL installation Security

To improve installation security of MySQL we will run the ‘mysql_secure_installation’ command. After running the command you will set up a password if not done already and go through a couple of options:

Step 5: Create the User and the Privileges

Now we will access MySQL server, remember to run the command with sudo for the root user:

Now we will create the user and the set the user’s privileges:

Step 6: Configure MySQL Binding Address

Now we will configure the binding address that you can access MySQL from outside the Google Cloud Platform. Go to the folder that contains the file with the binding address:

Open the file containing the binding address with sudo rights:

After opening the file, look for the line that contains the binding address and change it to ‘0.0.0.0’:

When done, exit the file and save the changes:

Do not change the name of the file:

Step 7: Firewall Setup on Google Cloud

Now we will create a firewall rule to allow an IP address outside of the Google Cloud Platform to access MySQL on its port. Go to the Firewall Rules:

Create a new Firewall rule:

Capture and change the following marked fields, when done, click on create:

Now you are ready to access MySQL on you VM Instance from any IP address.

Step 8: Test connection to MySQL on the Instance

Now I will use MySQL Workbench to test the connection by creating a new connection:

Capture the following fields and click on ‘OK’:

You will get a popup request you to enter your password, fill it in and click on ‘OK’ again:

It will then test the connection and if successful it will show you the following popup:

Leave a Reply

Your email address will not be published. Required fields are marked *