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:

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
    

Create a Compute Engine instance

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

To create a Compute Engine instance in the Google Cloud console:

  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.

  6. Click Create.

To set up and manage network address translation with Cloud NAT:

  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. For the VPC network for the NAT gateway, choose default.

  5. For the Region for the NAT gateway, choose the region you chose above.

  6. For Cloud Router, choose Create new router.

  7. In the dialog, provide a Name for the router, nat-router-simple-configuration, and click Create.

  8. Click Advanced configuration.

  9. Under Stackdriver logging, select Translation and errors. This sends all logs to Cloud Logging.

  10. Click Create.

To establish an SSH connection:

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

  2. In the Connect column, 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
    
  2. Create a simple 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 connections is made via TCP forwarding that is handled by Identity-Aware Proxy (IAP).

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 compatilibity 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 commmand.

  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
    

    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
    

    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:

  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.