Setting up client access over private IP address to MySQL on Compute Engine

This tutorial walks you through the process of running a MySQL database on a private network in Google Cloud Platform (GCP) to allow secure, remote access to the database by using Compute Engine.

Use this tutorial if you want to install your own MySQL database on Compute Engine, but want to restrict access to only authorized MySQL clients also running on Compute Engine. You might want to manage your own MySQL instance instead of using the managed service, due to cross-region instances, advanced usage of parameters, and specific performance needs.

This tutorial describes how to configure your MySQL server app 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 Compute Engine.

This tutorial assumes that you are familiar with the following:

  • Basic Linux commands
  • Ubuntu-server 18.04
  • MySQL 5.7
  • Compute Engine

Architecture

In this tutorial, you deploy two Compute Engine instances. One instance is the server and the other instance is the client as depicted in the following diagram:

Architecture of two deployed instances

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.
  • Remove public access to the MySQL server.
  • Connect remotely to MySQL.
  • Create a VPC Service Controls firewall rule.

Costs

本教學課程使用下列 Google Cloud Platform 計費元件:

  • Compute Engine
  • Cloud Storage

您可以使用 Pricing Calculator,根據您的預測使用量來產生預估費用。 初次使用 GCP 的使用者可能符合申請免費試用的資格。

Before you begin

  1. 登入您的 Google 帳戶。

    如果您沒有帳戶,請申請新帳戶

  2. 選取或建立 Google Cloud Platform 專案。

    前往「Manage resources」(管理資源) 頁面

  3. 請確認您已啟用 Google Cloud Platform 專案的計費功能。

    瞭解如何啟用計費功能

  4. 啟用Compute Engine API。

    啟用 API

When you finish this tutorial, you can avoid continued billing by deleting the resources you created. See Cleaning up for more detail.

Creating Compute Engine instances

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

Create a Compute Engine client instance

  • 建立新的 Compute Engine 執行個體。執行個體的設定方式如下:
    • 將執行個體命名為my-client
    • --zone 標記設定為 您要建立執行個體的區域
    • --image-project 標記設定為ubuntu-os-cloud
    • --image-family 標記設定為ubuntu-1804-lts
    • --scopes 標記設定為https://www.googleapis.com/auth/cloud-platform
    gcloud compute instances create my-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

  • 建立新的 Compute Engine 執行個體。執行個體的設定方式如下:
    • 將執行個體命名為my-server
    • --zone 標記設定為 您要建立執行個體的區域
    • --image-project 標記設定為ubuntu-os-cloud
    • --image-family 標記設定為ubuntu-1804-lts
    • --scopes 標記設定為https://www.googleapis.com/auth/cloud-platform
    gcloud compute instances create my-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 Engine instance.

    1. 在 GCP 主控台中,前往「VM Instances」(VM 執行個體) 頁面。

      前往「VM Instances」(VM 執行個體) 頁面

    2. 在虛擬機器執行個體清單中,找到您要建立連線的執行個體,然後在該列中按一下 [SSH]

    3. Update the apt-get package manager.
      sudo apt-get update
      
    4. Install the MySQL 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 Engine instance.

    1. 在 GCP 主控台中,前往「VM Instances」(VM 執行個體) 頁面。

      前往「VM Instances」(VM 執行個體) 頁面

    2. 在虛擬機器執行個體清單中,找到您要建立連線的執行個體,然後在該列中按一下 [SSH]

    3. Update the apt-get package manager.
      sudo apt-get update
      
    4. Install the MySQL server package.
      sudo apt-get -y install mysql-server-5.7

    Improve MySQL installation security

    You must establish a root password for MySQL and perform basic security maintenance on your MySQL server configuration. For more information, see the MySQL documentation for mysql_secure_installation.

    1. In Cloud Shell, improve the security of your MySQL installation.

      sudo mysql_secure_installation
      
    2. Press enter to skip setting up the VALIDATE PASSWORD plugin.

    3. Enter a new root password twice.

    4. To remove anonymous users, enter Y and press enter.

    5. To prevent remote root login, enter Y and press enter.

    6. To remove the test database, enter Y and press enter.

    7. To reload the privilege tables, enter Y and press enter.

    Configuring the MySQL server

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

    All MySQL client commands must include certain command-line flags (for example, to authenticate). The MySQL commands in this section include the following flags: --user for the username, -p for the password, and -e to execute the given statement and immediately quit. For more information, see the MySQL 5.7 command options reference.

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

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

      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
      
    3. Restart the MySQL service to apply the changes to the running server.

      sudo service mysql restart
      
    4. Verify that the server is running locally. Replace [ROOT_PASSWORD] with the MySQL server root password you established in a previous step.

      sudo mysql --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 preceding mysql_secure_installation command. You need to create a new user with the necessary permissions to allow remote connections.

    1. In Cloud Shell, create an environment variable for the my-client internal IP address.

      CLIENT_IP=$(gcloud compute instances describe my-client \
          --zone=[ZONE] \
          --format='value(networkInterfaces[0].networkIP)')
      
    2. Create a new MySQL user with a password. Replace [MY_PASSWORD] with your password, [ROOT_PASSWORD] with your MySQL root user password.

      sudo mysql -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 the internal IP address of my-client.

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

    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.

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

      gcloud compute instances delete-access-config my-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 on my-server from your my-client instance.

    1. In Cloud Shell, use SSH to connect to my-client instance.
    2. Test your connection by listing the databases.

      sudo mysql --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 MySQL server over the internal IP address.

    Firewall considerations in production environments

    The default network configuration in GCP includes a firewall rule, default-allow-internal, that allows internal traffic between Compute Engine instances on a wide range of ports, including the MySQL port, port 3306. In non-default environments with an established security footprint, you might need to create a firewall rule to allow your my-client instance to communicate with your my-server instance over the network.

    You can base firewall rules 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.

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

    gcloud compute instances add-tags my-client --tags mysql-client --zone=[ZONE]
    
    gcloud compute instances add-tags my-server --tags mysql-server --zone=[ZONE]
    

    Add a new firewall rule

    The following steps describe how to create a new firewall rule to enable instances with the my-client tag to communicate with instances that have the my-server tag by using port 3306.

    • In Cloud Shell, create a firewall rule to allow communications from mysql-client to mysql-server.

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

    You can now connect to MySQL from my-client.

    Access considerations from external clients

    This tutorial covers access from MySQL clients to MySQL servers both running on Compute 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 to my-server to allow external connectivity.
    • Add the source IP address of your external client to the firewall rules.
    • Modify the TESTUSER account, or create a user account, that is bound to the source IP address of your external client.

    Cleaning up

    To avoid incurring charges to your GCP account for the resources used in this tutorial, you can either delete the project or delete the instances.

    Delete the project

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

    To delete the project:

    1. 前往 GCP 主控台的「Projects」(專案) 頁面。

      前往專案頁面

    2. 在專案清單中選取要刪除的專案,然後按一下 [Delete] (刪除)
    3. 在對話方塊中輸入專案 ID,按一下 [Shut down] (關閉) 即可刪除專案。

    Delete instances

    To delete a Compute Engine instance:

    1. 在 GCP 主控台中,前往「VM Instances」(VM 執行個體) 頁面。

      前往 VM 執行個體頁面

    2. 找到 您的 my-server 執行個體,然後按一下旁邊的核取方塊。
    3. 按一下頁面頂端的 [刪除] 按鈕,刪除該執行個體。

    What's next

    本頁內容對您是否有任何幫助?請提供意見:

    傳送您對下列選項的寶貴意見...

    這個網頁
    Solutions