How to Set Up Remote Access to MySQL on Google Compute Engine

One of the key use cases for installing MySQL on Google Compute Engine as an alternative to Google Cloud SQL is to keep your MySQL server and client applications on a private network. Cloud SQL automatically assigns a public IPv6 address to every Cloud SQL instance and provides the option to add a public IPv4 address. Moreover, only clients with public IPv4 or IPv6 addresses can remotely access a Cloud SQL instance.

To run your MySQL server and client on a private network, use Compute Engine instances for both the server and the client. This document describes how to configure your MySQL server application to accept remote traffic from a MySQL client that is installed on a Compute Engine instance on the same private network.

For information about how to choose the right MySQL deployment option, see How to Install MySQL on Google Compute Engine.

Objectives

  • Create a Compute Engine instance and install MySQL server
  • Create a Compute Engine instance and install MySQL client
  • Configure MySQL server for remote access
  • Connect remotely to MySQL
  • Create a firewall rule to open port 3306

Prerequisites

You can complete all of the steps in this document using the Cloud Platform Console, but if you prefer to use the gcloud command line tool, follow these steps to enable the Compute Engine API and install the Google Cloud SDK.

  1. Use the Cloud Platform Console to enable the Compute Engine API.

  2. Install the Google Cloud SDK.

  3. Configure your workspace to make commands less verbose. Substitute your project's values for project-id and my-zone in the following commands. For the full list of zones, see Available regions & zones.

    me@local:~$ gcloud config set project project-id
    me@local:~$ gcloud config set compute/zone my-zone
    

Create a Compute Engine instance and install MySQL server

Create an instance for MySQL and establish an SSH connection to the newly created instance. The default operating system is Debian version 7. If you prefer to use a different operating system for this tutorial, you can choose from the options described on the Operating Systems page in the Compute Engine documentation.

To create a Compute Engine instance in the Cloud Platform Console:

Console


  1. In the GCP Console, go to the VM Instances page.

    Go to the VM Instances page

  2. Click the Create button.
  3. Set Name to my-server.
  4. Click the Management tab.
  5. In the Tags section, add mysql-server.
  6. Click the Create button to create the instance.

To establish an SSH connection:

  1. In the Cloud Platform Console, go to the VM Instances page.

    Go to the VM Instances page

  2. In the list of virtual machine instances, click SSH in the row of the instance that you want to connect to.

gcloud


  • Create a new Compute Engine instance. Configure the instance as follows:
    • Name the instance my-server.
    • Set the --zone flag to the zone in which you want to create your instance.
    • Set the --tags flag to mysql-server.
    gcloud compute instances create my-server --zone [ZONE] --tags mysql-server
  • Establish an SSH connection to the my-server instance:

    gcloud compute ssh my-server

    Install MySQL server

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

    Debian & Ubuntu


    1. Update the apt-get package manager.

      $ sudo apt-get update
      
    2. Install MySQL. The installation process starts the MySQL service for you.

      $ sudo apt-get -y install mysql-server
      

    CentOS 6 & RHEL 6


    1. Install MySQL.

      $ sudo yum -y install mysql-server
      
    2. Start MySQL server.

      $ sudo service mysqld start
      

    CentOS 7 & RHEL 7


    Version 7 of CentOS and RHEL now contain MariaDB instead of MySQL as part of its package management system. To install MySQL on CentOS 7, you must first update the package manager.

    1. Update the package manager to include MySQL.

      $ sudo rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
      
    2. Install MySQL.

      $ sudo yum -y install mysql-community-server
      
    3. Start MySQL server.

      $ sudo /usr/bin/systemctl start mysqld
      

    Improve MySQL installation security

    To improve the security of your MySQL installation, run the mysql_secure_installation command. If you didn't set a password during the installation process, create a password in this step. For more information about this command, see the MySQL documentation for mysql_secure_installation.

        $ sudo mysql_secure_installation
    

    Create a Compute Engine instance and install MySQL client

    Create a Compute Engine instance that uses MySQL client to remotely access MySQL server on the my-server instance. The default operating system is Debian version 7. If you prefer to use a different operating system for this tutorial, you can choose from the options described on the Operating Systems page in the Compute Engine documentation.

    Console


    To create a Compute Engine instance in the Cloud Platform Console:

    1. Click New instance. Name the instance my-client.

    2. To specify an operating system other than the default value, in the Boot disk section, click Change, then select the operating system, and then click Select.

    3. Add a tag named mysql-client to the instance by clicking on Management, disk, networking, access & security options. Find the Tags section and enter mysql-client.

    4. Click Create.

    To get the internal IP addresses of both instances:

    1. On the VM instances page in the Cloud Platform Console, find your new instance in the list.

    2. In the Name column, click my-client. Note the IP address listed under Internal IP.

    3. Go back to the VM instances page and find my-server in the list.

    4. In the Name column, click my-server. Note the IP address listed under Internal IP.

    gcloud


    1. To create a Compute Engine instance, use the gcloud compute instances create command. To use a different operating system, add the --image parameter followed by the image name. For example, to use Debian 8, add --image debian-8.

      me@local:~$ gcloud compute instances create my-client --tags 'mysql-client'
      
    2. Run the following command and note the internal IP addresses of the instances listed under the INTERNAL_IP column:

      me@local:~$ gcloud compute instances list
      

    Establish an SSH connection to my-client

    Follow these instructions to establish an SSH connection to your newly created my-client instance.

    Console


    To establish an SSH connection:

    1. On the VM instances page in the Cloud Platform Console, find my-client in the list of instances.

    2. In the Connect column, click SSH. The SSH terminal opens in a browser window.

    gcloud


    Connect to the instance using SSH.

        me@local:~$ gcloud compute ssh my-client
    

    Install MySQL client

    The following steps describe how to install MySQL client my-client.

    Debian & Ubuntu


    1. Update the apt-get package manager.

      $ sudo apt-get update
      
    2. Install the MySQL client. The installation process starts the MySQL service for you.

      $ sudo apt-get -y install mysql-client
      

    CentOS 6 & RHEL 6


    Install the MySQL client.

        $ sudo yum -y install mysql-client
    

    CentOS 7 & RHEL 7


    Version 7 of CentOS and RHEL now contain MariaDB instead of MySQL as part of its package management system. To install MySQL on CentOS 7, you must first update the package manager.

    1. Update the package manager to include MySQL.

      $ sudo rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
      
    2. Install the MySQL client.

      $ sudo yum -y install mysql-community-client
      

    Configure MySQL server on my-server

    Use your existing SSH connection to my-server to configure MySQL server. If you closed your SSH connection to my-server, follow these instructions to re-establish an SSH connection.

    Console


    To establish an SSH connection:

    1. On the VM instances page in the Cloud Platform Console, find your my-server instance in the list.

    2. In the Connect column, click SSH. The SSH terminal opens in a browser window.

    gcloud


    Connect to the instance using SSH.

        me@local:~$ gcloud compute ssh my-server
    

    To configure MySQL server so that it accepts remote connections from a MySQL client on my-client, modify the MySQL configuration file and then add the user to the MySQL user table.

    1. Open the my.cnf configuration file for editing.

      $ sudo nano /etc/mysql/my.cnf
      
    2. Find the line that sets the bind-address to 127.0.0.1:

      bind-address            = 127.0.0.1
      

      and replace 127.0.0.1 with the internal IP address of the my-server instance.

      bind-address            = <internal-ip-my-server>
      
    3. Save the change and exit the text editor.

    4. Restart the MySQL service.

      $ sudo service mysql restart
      
    5. Sign in to MySQL server as the root MySQL user.

      $ mysql --user=root -p
      

      When you connect to MySQL, the prompt changes to:

      mysql>
      
    6. Create a new user named TESTUSER, again replacing <internal-ip-my-client> with the internal IP address of the my-client instance and <some-password> with a password of your choosing. You will need the password to remotely access MySQL server.

      mysql> CREATE USER 'TESTUSER'@'<internal-ip-my-client>' IDENTIFIED BY '<some-password>';
      
    7. Grant the necessary privileges to the newly created TESTUSER.

      mysql> GRANT ALL PRIVILEGES ON *.* TO 'TESTUSER'@'<internal-ip-my-client>' IDENTIFIED BY '<some-password>';
      
    8. Quit the MySQL command line and end the SSH connection to my-server.

      mysql> exit
      $ exit
      

    Remove the external IP address for my-server

    The my-server instance doesn't need an external IP address because the client can access my-server through an internal IP address. We could also remove the my-client instance's external IP address, but we'll leave it in place because it's easier to establish an SSH connection to my-client if it still has an external IP address.

    Follow these instructions to remove the external IP address for the my-server instance.

    Console


    To remove the external IP address:

    1. On the VM instances page in the Cloud Platform Console, find my-server in the list of instances.

    2. In the Name column, click my-server.

    3. Find External IP and click Edit.

    4. Select None from the drop-down list and click Save

    gcloud


    To remove the external IP address, update the configuration settings.

        me@local:~$ gcloud compute instances delete-access-config my-server --access-config-name "external-nat"
    

    Access MySQL server remotely

    The following steps describe how to connect to MySQL server on my-server from your my-client instance.

    1. Connect to the MySQL server by using the MySQL client.

      $ mysql --host=my-server --user=TESTUSER --password
      
    2. You can then run MySQL commands. For example, the following command shows the threads running, including the current connection.

      mysql> show processlist;
      +----+----------+---------------------------------------+------+---------+------+-------+------------------+
      | Id | User     | Host                                  | db   | Command | Time | State | Info             |
      +----+----------+---------------------------------------+------+---------+------+-------+------------------+
      | 38 | TESTUSER | my-client.c.mysql-1043.internal:59154 | NULL | Query   |    0 | NULL  | show processlist |
      +----+----------+---------------------------------------+------+---------+------+-------+------------------+
      1 row in set (0.00 sec)
      

      You can use the following command to generate a list of users.

      mysql> SELECT User, Host, Password FROM mysql.user;
      +------------------+--------------+-------------------------------------------+
      | User             | Host         | Password                                  |
      +------------------+--------------+-------------------------------------------+
      | root             | localhost    | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
      | TESTUSER         | 10.240.88.10 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
      | root             | 127.0.0.1    | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
      | root             | ::1          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
      | debian-sys-maint | localhost    | *619310B1BAC2FD6F6174D5C73C9E5A35F3B8F729 |
      +------------------+--------------+-------------------------------------------+
      5 rows in set (0.00 sec)
      
    3. When you are done running commands, use the exit command to quit the MySQL client, and then use exit again to sign out of the Compute Engine instance.

      mysql> exit
      my-server:~$ exit
      

    Advanced networking considerations

    Normally, when you set up remote access for MySQL server, you need to open port 3306 on the server. You didn't need to open that port in this tutorial because the default network configuration includes a firewall rule, default-allow-internal, that allows internal traffic between Compute Engine instances on a wide range of ports, including port 3306.

    If you create your own network configuration that doesn't contain a firewall rule that includes port 3306, you'll need to open port 3306. To simulate this condition, modify the default-allow-internal rule so that it excludes port 3306.

    Firewall rules can be based on IP address ranges or tags. IP address ranges are useful if you want to grant access to a wide range of internal IP addresses. Alternatively, if you want to grant access to specific instances on your network, tags provide a more flexible solution. Tags make it easier to add new clients without granting access to a wide range of IP addresses. You need only assign the appropriate tag to the new MySQL client instance. For example, you can create a new firewall rule that allows traffic from all client instances that are tagged with mysql-client.

    Recall that you already assigned tags to both instances you created in this tutorial: mysql-server to the my-server instance and mysql-client to the my-client instance.

    The following sections describe how to modify the default firewall rule to exclude port 3306 and then create a new firewall rule that opens port 3306 on all instances tagged with the mysql-server tag.

    Modify the default firewall rule

    Console


    1. Go to Networking > Firewall Rules in the Cloud Platform Console, select the checkbox next to default-allow-internal.

    2. Click Edit.

    3. In the Allowed protocols and ports section, change tcp:1-65535 to tcp:1-3305 and then click Save.

    gcloud


        $ gcloud compute firewall-rules update default-allow-internal --allow tcp:1-3305,udp:1-65535,icmp
    

    If you try to connect to MySQL from my-client, the request eventually times out.

    Add a new firewall rule

    Console


    1. Add a new firewall rule in the Cloud Platform Console.

    2. Enter the following values:

      • Name: mysql-remote-access

      • Source filter: Instance tags

      • Source Tags: mysql-client

      • Allowed protocols and ports: tcp:3306

      • Target Tags: mysql-server

    3. Click Create.

    gcloud


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

    You should now be able to connect to MySQL from my-client.

    Cleaning up

    After you've finished the MySQL tutorial, you can clean up the resources you created on Google Cloud Platform so you won't be billed for them in the future. The following sections describe how to delete or turn off these resources.

    Deleting the project

    The easiest way to eliminate billing is to delete the project you created for the tutorial.

    To delete the project:

    1. In the Cloud Platform Console, go to the Projects page.

      Go to the Projects page

    2. In the project list, select the project you want to delete and click Delete project. After selecting the checkbox next to the project name, click
      Delete project
    3. In the dialog, type the project ID, and then click Shut down to delete the project.

    Deleting instances

    To delete a Compute Engine instance:

    1. In the Cloud Platform Console, go to the VM Instances page.

      Go to the VM Instances page

    2. Click the checkbox next to your my-server instance.
    3. Click the Delete button at the top of the page to delete the instance.

    Next steps

    You've now seen how to access MySQL server remotely. To see more complex applications that use MySQL, browse the wide variety of development stacks on Google Cloud Launcher that use MySQL.

    If your requirements include high availability and scalability, consider installing MySQL Cluster on Compute Engine. MySQL Cluster provides high availability and scalability through shared-nothing clustering and auto-sharding. Google Cloud Launcher provides a click-to-deploy option for Percona, an open source solution for MySQL clustering.

    Another open source solution for MySQL scalability is Vitess, which has served all YouTube database traffic since 2011. Vitess is well-suited for applications that run in containers. For more information on using Vitess in a containerized environment, see Running Vitess on Kubernetes.

    For more information about MySQL, see the official MySQL documentation.

    Try out other Google Cloud Platform features for yourself. Have a look at our tutorials.

    Send feedback about...