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:
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
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.
Use the Google Cloud console to enable the Compute Engine API.
Install the gcloud CLI.
Configure your workspace to make commands less verbose. Substitute your project's values for
PROJECT_ID
,REGION
, andZONE
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:
In the Google Cloud console, go to the Compute Engine VM instances page.
Select your newly created project and click Continue.
Click Create instance (New instance if you have existing instances). Name the instance mysql-test.
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.
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.
Click Create.
To set up and manage network address translation with Cloud NAT:
In the Google Cloud console, go to the Cloud NAT page.
Click Get started or Create Cloud NAT gateway.
For Gateway name, enter
nat-simple-configuration
.For the VPC network for the NAT gateway, choose default.
For the Region for the NAT gateway, choose the region you chose above.
For Cloud Router, choose Create new router.
In the dialog, provide a Name for the router,
nat-router-simple-configuration
, and click Create.Click Advanced configuration.
Under Stackdriver logging, select Translation and errors. This sends all logs to Cloud Logging.
Click Create.
To establish an SSH connection:
On the VM instances page, find your new VM instance in the list.
In the Connect column, click SSH. The SSH terminal opens in a browser window.
gcloud
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
Create a simple Cloud Router.
gcloud compute routers create nat-router-simple-configuration \ --network=default
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
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.
Install the
wget
dependency.sudo apt-get install -y wget
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}
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
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.
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.
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.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.
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:
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}
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
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.
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.
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.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.
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.
Update the
apt-get
package manager.sudo apt-get update
Install MySQL. The installation process starts the MySQL service for you.
sudo apt-get -y install mysql-server
By default in Ubuntu distributions, mysql authenticates the
root
user with theauth_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 fromlocalhost
will use theauth_socket
plugin:+------+-----------------------+-------------+-----------+ | user | authentication_string | plugin | host | +------+-----------------------+-------------+-----------+ | root | | auth_socket | localhost | +------+-----------------------+-------------+-----------+
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.
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}
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
Update the package manager to include MySQL.
sudo rpm -Uvh ${RPM_FILE}
Install MySQL.
sudo yum -y install mysql-community-server
Start MySQL server.
sudo /usr/bin/systemctl start mysqld
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.
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}
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
Update the package manager to include MySQL.
sudo rpm -iUvh ${RPM_FILE}
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
Start MySQL server.
sudo /usr/bin/systemctl start mysqld
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
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)
When you are done running commands, use the
exit
command to quit out of the MySQL client, and then useexit
again to sign out of the Compute Engine instance.mysql> exit
Bye
MySQL 5.7
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)
When you are done running commands, use the
exit
command to quit out of the MySQL client, and then useexit
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:
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.
Deleting instances
To delete a Compute Engine instance:
- In the Google Cloud console, go to the VM instances page.
- Select the checkbox for the instance that you want to delete.
- 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.