How to Set Up MySQL on Google Compute Engine

Last reviewed 2021-11-23 UTC

You have several options for deploying MySQL as part of your Google Cloud project. You can use Cloud SQL, Google Cloud Marketplace, or manually install MySQL on Compute Engine.

Cloud SQL offers MySQL as a web service. You can use Cloud SQL to host your MySQL database in Google's cloud, and let Google Cloud handle administrative duties like replication, patch management, and database management.

Cloud Marketplace provides a simple click-to-deploy interface that makes it easy to install MySQL onto a Compute Engine instance. Cloud Marketplace includes not only a standalone MySQL installation, but also several web development stacks that use MySQL, including LAMP stacks, LEMP stacks, and Percona MySQL clusters.

If you prefer to manually install and customize MySQL, you can use Compute Engine to create a MySQL database in a matter of minutes. This document provides guidance on which option to choose and walks through the manual installation of a MySQL database on Compute Engine.

How to choose the right MySQL deployment option

Cloud SQL is a great option if you want the convenience of having Google Cloud take care of the backend database and server administration chores. For example, Cloud SQL provides automated backups and point-in-time recovery. Moreover, your data is replicated across multiple zones for greater availability and resiliency.

You might prefer to install MySQL on Compute Engine if you require a MySQL feature that is not supported by Cloud SQL. For example, Cloud SQL does not support user defined functions or the SUPER privilege. For more information, see the Cloud SQL FAQ.

If you decide to install MySQL on Compute Engine, you can either use Cloud Marketplace to deploy a MySQL installation, or you can manually install MySQL on a Compute Engine instance. Cloud Marketplace provides a convenient way to deploy MySQL as part of larger development stacks. Cloud Marketplace offers several options for MySQL installations, including a standalone MySQL installation, LAMP stacks, LEMP stacks, Nginx Stacks, a Percona MySQL Cluster installation, and several other options.

If the Cloud Marketplace offerings don't meet your needs, you can manually install MySQL on a Compute Engine instance. You might, for example, want to deploy MySQL on a custom image that you have created, or you might want to have complete control of the installation process.

To manually install MySQL on a Compute Engine instance, you need only create a Compute Engine instance and install MySQL directly onto the instance.

For more information about each option, see the following resources:

• For Cloud SQL, see the Cloud SQL for MySQL documentation.

• For Cloud Marketplace, see the options for MySQL installations in the Google Cloud console:

  Go to Cloud Marketplace

• For manual installation of MySQL on a Compute Engine instance, see the remainder of this document.

Objectives

• Create a Compute Engine instance
• Install MySQL
• Connect to MySQL

Prerequisites

1. Create a new project in the Google Cloud console. You can use an existing project, but creating a new project makes cleanup easier.

   You can complete all of the steps in this document using the Google Cloud console, but if you prefer to use the gcloud CLI, follow these steps to enable the Compute Engine API and install the Google Cloud CLI.

2. Use the Google Cloud console to enable the Compute Engine API.

3. Install the gcloud CLI.

4. Configure your workspace to make commands less verbose. Substitute your project's values for PROJECT_ID, REGION, and ZONE in the following commands. For the full list of zones, see Available regions & zones.

   gcloud config set project PROJECT_ID
   gcloud config set compute/region REGION
   gcloud config set compute/zone ZONE
   

Important: This tutorial uses Compute Engine, which is a billable component of Google Cloud. Use the Pricing Calculator to generate a cost estimate based on your projected usage. New Google Cloud users may be eligible for a free trial.

Create a Compute Engine instance and establish an SSH connection

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

Console

Create the Compute Engine instance

To create the Compute Engine instance in the Google Cloud console, do the following:

1. In the Google Cloud console, go to the Compute Engine VM instances page.

   Go to Compute Engine VM instances.

2. Select your newly created project and click Continue.

3. Click Create instance (New instance if you have existing instances). Name the instance mysql-test.

4. To specify an operating system other than the default value, in the Boot disk section, click Change to configure the properties for the boot disk. In the Public images tab, select an operating system and then click Save.

5. To conform with security best practices, create your instance without an external IP address.

   Expand Advance options and expand Networking. Under Network interfaces, expand the default interface, and in the External IPv4 address menu, select None.

   Note: Your instance will need to reach public package repositories during the MySQL installation. To allow internet access, configure Cloud NAT to manage network address translation.

6. Click Create.

Set up network address translation

To set up and manage network address translation with Cloud NAT, do the following:

1. In the Google Cloud console, go to the Cloud NAT page.

   Go to Cloud NAT

2. Click Get started or Create Cloud NAT gateway.

3. For Gateway name, enter nat-simple-configuration.

4. To select the Cloud Router, in the Select Cloud Router section, do the following:

   1. For Network, select default.
   2. For Region, select the region that you specified for your VM.

   3. For Cloud Router, select Create new router.

      In the Create a router dialog that appears, provide a Name for the router—for example, nat-router-simple-configuration—and click Create.

5. Click Advanced configurations.

6. In the Logging section, select Translation and errors. This setting configures Cloud NAT to send all logs to Cloud Logging.

7. Click Create.

Establish an SSH connection to the VM

To establish an SSH connection, do the following:

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

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

gcloud

1. To create a Compute Engine instance, use the gcloud compute instances create command. To specify the operating system, add the --image-family parameter followed by the image family or the --image parameter followed by the image name for a specific image version. For example, to use the most recent image from the Debian 10 family, add --image-family debian-10. When using public images, the image project must be provided using the --image-project parameter.

   gcloud compute instances create \
       --image-family debian-10 \
       --image-project debian-cloud \
       --no-address \
     mysql-test
   

   Note: To conform with security best practices, the --no-address option creates this instance without an external IP address. However, your instance will need to reach public package repositories during the MySQL installation. To allow internet access, configure Cloud NAT to manage network address translation.

2. Create a Cloud Router.

   gcloud compute routers create nat-router-simple-configuration \
       --network=default
   

3. Set up and manage network address translation with Cloud NAT.

   gcloud compute routers nats create nat-simple-configuration \
       --router=nat-router-simple-configuration \
       --auto-allocate-nat-external-ips \
       --nat-all-subnet-ip-ranges \
       --enable-logging
   

4. Connect to the instance using ssh.

   gcloud compute ssh \
       --tunnel-through-iap \
       mysql-test
   

   The SSH connection is made using TCP forwarding that is handled by Identity-Aware Proxy (IAP).

   Note: Depending on your VPC network configuration or organization policies, it may be necessary to prepare your project for IAP TCP forwarding.

Install MySQL

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

Debian 10+

These instructions install MySQL 8 or 5.7.

Versions 10 and later of Debian contain MariaDB instead of MySQL as part of its package management system. MariaDB maintains compatibility with the MySQL protocol, but has an independently evolving feature set. For more details, see MariaDB vs. MySQL.

To install MySQL, download the release package and manually install using the dpkg command.

1. Install the wget dependency.

   sudo apt-get install -y wget
   

2. Download the MySQL Community Server release package.

   export DEB_FILE=mysql-apt-config_0.8.20-1_all.deb
   cd /tmp
   curl -L --output ${DEB_FILE} \
        https://dev.mysql.com/get/${DEB_FILE}
   

3. Verify the integrity of the release package file.

   cat > ${DEB_FILE}.md5 << EOL
   799bb0aefb93d30564fa47fc5d089aeb ${DEB_FILE}
   EOL
   md5sum --check ${DEB_FILE}.md5
   

   The authenticity and integrity of the file are verified if you see the following output.

   mysql-apt-config_0.8.20-1_all.deb: OK
   

4. After you have verified the file, add the MySQL package to the local package repository.

   sudo dpkg -i ${DEB_FILE}
   

   You are prompted to confirm the installation options including the MySQL version.

5. With the top MySQL Server & Cluster menu option selected, press Return and then use the arrow keys to choose a server version.

   This guide expects you to choose either MySQL 8.0 or 5.7. Press Return on your keyboard after you have selected the version.

6. When you are satisfied with the options selected in the configuration menu, use the arrow keys to select Ok in the menu and presss Return on your keyboard.

7. Update the package cache.

   sudo apt-get update
   

   If you receive a GPG error similar to The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 467B942D3A79BD29, import the missing GPG key(s) with a command that follows this pattern:

   sudo apt-key adv \
       --keyserver keyserver.ubuntu.com \
       --recv-keys 467B942D3A79BD29
   

   In the demonstrative error message, the missing public key is 467B942D3A79BD29; provide this value with the --recv-keys option.

   Repeat the command to update the package cache.

8. Install MySQL. The installation process starts the MySQL service for you.

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

   You are prompted to provide some details for the installation such as the root password.

Debian 9

These instructions install MySQL 8 or 5.7.

By Default, some versions of Debian, such as Debian 9, install MariaDB as the default MySQL server. MariaDB is designed to be largely compatible with MySQL and can be invoked using the same commands. For details on how MariaDB differs from standard MySQL, see Moving from MySQL to MariaDB in Debian 9.

To install MySQL, follow these instructions:

1. Download the MySQL Community Server release package.

   export DEB_FILE=mysql-apt-config_0.8.17-1_all.deb
   cd /tmp
   curl -L --output ${DEB_FILE} \
        https://dev.mysql.com/get/${DEB_FILE}
   

2. Verify the integrity of the release package file.

   cat > ${DEB_FILE}.md5 << EOL
   9e393c991311ead61dcc8313aab8e230 ${DEB_FILE}
   EOL
   md5sum --check ${DEB_FILE}.md5
   

   The authenticity and integrity of the file are verified if you see the following output.

   mysql-apt-config_0.8.17-1_all.deb: OK
   

3. After you have verified the file, add the MySQL package to the local package repository.

   sudo dpkg -i ${DEB_FILE}
   

   You are prompted to confirm the installation options including the MySQL version.

4. With the top MySQL Server & Cluster menu option selected, press Return and then use the arrow keys to choose a server version.

   This guide expects you to choose either MySQL 8.0 or 5.7. Press Return on your keyboard after you have selected the version.

5. When you are satisfied with the options selected in the configuration menu, use the arrow keys to select Ok in the menu and presss Return on your keyboard.

6. Update the package cache.

   sudo apt-get update
   

   If you receive a GPG error similar to The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 467B942D3A79BD29, import the missing GPG key(s) with a command that follows this pattern:

   sudo apt-key adv \
       --keyserver keyserver.ubuntu.com \
       --recv-keys 467B942D3A79BD29
   

   In the demonstrative error message, the missing public key is 467B942D3A79BD29; provide this value with the --recv-keys option.

   Repeat the command to update the package cache.

7. Install MySQL. The installation process starts the MySQL service for you.

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

Ubuntu

For Ubuntu 1804 and earlier these instructions install MySQL 5.7.
For Ubuntu 2004 and later these instructions install MySQL 8.

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
   

3. By default in Ubuntu distributions, mysql authenticates the root user with the auth_socket plugin. Run this query to verify this default configuration:

   echo "SELECT user, authentication_string, plugin, host
           FROM mysql.user WHERE user='root' ;" \
        | sudo mysql -t -u root
   

   The output shows that the root user connecting from localhost will use the auth_socket plugin:

   +------+-----------------------+-------------+-----------+
   | user | authentication_string | plugin      | host      |
   +------+-----------------------+-------------+-----------+
   | root |                       | auth_socket | localhost |
   +------+-----------------------+-------------+-----------+
   

4. Change this setting, and change the root password to one that is difficult to guess:

   export PASSWORD=`uuidgen`; echo "Root password is : $PASSWORD"
   echo "ALTER USER 'root'@'localhost'
         IDENTIFIED WITH mysql_native_password BY '$PASSWORD'" \
         | sudo mysql -u root
   

   The output contains the new root password:

   Root password is : 25fe2177-778a-414f-b80e-5fb8c8671de3
   

CentOS or RHEL 7

These instructions install MySQL 8.

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

1. Download the MySQL Community Server release package.

   export RPM_FILE=mysql80-community-release-el7-4.noarch.rpm
   cd /tmp
   curl -L --output ${RPM_FILE} \
        https://dev.mysql.com/get/${RPM_FILE}
   

2. Verify the integrity of the release package file.

   cat > ${RPM_FILE}.md5 << EOL
   8b55d5fc443660fab90f9dc328a4d9ad ${RPM_FILE}
   EOL
   md5sum --check ${RPM_FILE}.md5
   

   The authenticity and integrity of the file are verified if you see the following output.

   mysql80-community-release-el7-4.noarch.rpm: OK
   

3. Update the package manager to include MySQL.

   sudo rpm -Uvh ${RPM_FILE}
   

4. Install MySQL.

   sudo yum -y install mysql-community-server
   

5. Start MySQL server.

   sudo /usr/bin/systemctl start mysqld
   

6. Obtain the temporary root password from server logs

   sudo cat /var/log/mysqld.log | grep -i 'temporary password'
   

Rocky Linux or RHEL 8

These instructions install MySQL 8.

Version 8 of Rocky Linux, CentOS Stream, and RHEL contain MariaDB instead of MySQL as part of its package management system. To install MySQL, you must first update the package manager.

1. Download the MySQL Community Server release package.

   export RPM_FILE=mysql80-community-release-el8-2.noarch.rpm
   cd /tmp
   curl -L --output ${RPM_FILE} \
        https://dev.mysql.com/get/${RPM_FILE}
   

2. Verify the integrity of the release package file.

   cat > ${RPM_FILE}.md5 << EOL
   0ba3feb1c9ee35d30e5ae683accaf54b ${RPM_FILE}
   EOL
   md5sum --check ${RPM_FILE}.md5
   

   The authenticity and integrity of the file are verified if you see the following output.

   mysql80-community-release-el8-2.noarch.rpm: OK
   

3. Update the package manager to include MySQL.

   sudo rpm -iUvh ${RPM_FILE}
   

4. Install MySQL. During the installation, disable the AppStream repository, so that the installation will use the community server.

   sudo yum module disable -y mysql
   sudo yum install -y \
       --disablerepo=appstream \
     mysql-community-server
   

5. Start MySQL server.

   sudo /usr/bin/systemctl start mysqld
   

6. Obtain the temporary root password from server logs

   sudo cat /var/log/mysqld.log | grep -i 'temporary password'
   

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

Connect to MySQL

The following steps describe how to connect to MySQL from your mysql-test instance.

MySQL 8.0

1. Connect to MySQL using the MySQL client.

   sudo mysql -u root -p
   

   Note: In most cases, you should use a non-root user account to access your MySQL database. For details on adding a user account, see Adding User Accounts in the MySQL documentation.

   When you connect to MySQL, the prompt changes to mysql>.

   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             |
   +----+-----------------+-----------+------+---------+------+------------------------+------------------+
   |  5 | event_scheduler | localhost | NULL | Daemon  | 1889 | Waiting on empty queue | NULL             |
   | 14 | root            | localhost | NULL | Query   |    0 | init                   | show processlist |
   +----+-----------------+-----------+------+---------+------+------------------------+------------------+
   2 rows in set (0.00 sec)

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

   mysql> SELECT User, Host, authentication_string FROM mysql.user;

   +------------------+-----------+------------------------------------------------------------------------+
   | User             | Host      | authentication_string                                                  |
   +------------------+-----------+------------------------------------------------------------------------+
   | mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
   | mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
   | mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
   | root             | localhost | $A$005$,BS{G+*#cVYxb6x40q0aFS5dp2/Kz6u2vennR5qe0eBKVA/6VW5B            |
   +------------------+-----------+------------------------------------------------------------------------+
   4 rows in set (0.00 sec)

2. When you are done running commands, use the exit command to quit out of the MySQL client, and then use exit again to sign out of the Compute Engine instance.

   mysql> exit

   Bye

MySQL 5.7

1. Connect to MySQL using the MySQL client.

   sudo mysql -u root -p
   

   Note: In most cases, you should use a non-root user account to access your MySQL database. For details on adding a user account, see Adding User Accounts in the MySQL documentation.

   When you connect to MySQL, the prompt changes to mysql>.

   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             |
   +----+------+-----------+------+---------+------+-------+------------------+
   | 51 | root | localhost | 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, authentication_string FROM mysql.user;
   

   +---------------+-----------+-------------------------------------------+
   | User          | Host      | authentication_string                     |
   +---------------+-----------+-------------------------------------------+
   | root          | localhost | *A047B05AAB007B33F8F2BD1FD404661D167D6348 |
   | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
   | mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
   +---------------+-----------+-------------------------------------------+
   3 rows in set (0.00 sec)

2. When you are done running commands, use the exit command to quit out of the MySQL client, and then use exit again to sign out of the Compute Engine instance.

   mysql> exit

   Bye

Cleaning up

After you finish the tutorial, you can clean up the resources that you created so that they stop using quota and incurring charges. 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 that you created for the tutorial.

To delete the project:

Caution: Deleting a project has the following effects:

• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an appspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
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 Google Cloud console, go to the VM instances page.

   Go to VM instances

2. Select the checkbox for the instance that you want to delete.
3. To delete the instance, click More actions, click Delete, and then follow the instructions.

What's next

You've now seen how to install MySQL server on Compute Engine. To see more complex applications that use MySQL, browse the wide variety of development stacks on Cloud Marketplace 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. Cloud Marketplace 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.

Explore reference architectures, diagrams, and best practices about Google Cloud. Take a look at our Cloud Architecture Center.