This post is a step-by-step guide for you to learn how to host your MySQL database on Google Cloud SQL.
Obviously, you need a Google account and a credit card to enable billing. If it's your first time, Google will give you $300 in credits to try any service, so you can learn, explore, and even evaluate the prices charged.
All the services you configure on GCloud are grouped by projects, so the first thing to do is create or select a project.
Access the projects screen at the link https://console.cloud.google.com/cloud-resource-manager and create a project with the name you want.
Now let's go to the Database area. In GCloud, they call it Cloud SQL, since they also offer other databases that do not use SQL language.
Click on Create instance. As of the date of this post, we have three options: SQL Server (2017), MySQL (5.6 and 5.7), and PostgreSQL (9.6, 10, and 11). In this case, we'll go with MySQL!
In the following form, we have the following fields:
| Instance ID | It is a unique and permanent name to identify your database instance and differentiate it from other instances. |
| Root password | Password for the root user created by default. |
| Region | Physical location of the server that will be used to run your Database. |
| Zone | Zone within the region chosen above. |
| Database version |
|
After clicking on Create, you will be redirected to the list of instances and must wait until your instance is created.
Once created and ready for use, we can try to access it using the shell script from the Google Cloud site, the Cloud Shell.
Once it finishes initializing, type the command below. Remember to replace the word IdInstance with the name of the instance you chose during creation.
gcloud sql connect IdInstance --user=root
You will be prompted for the root user's password that you also set during creation.
Now let's create a database, a table, and insert a record. This is a very simple task, especially for those who have already taken our course Mastering Database with MYSQL.
- First, command to create a database:
- CREATE DATABASE hcodedb;
- Second, select the database hcodedb:
- USE hcodedb;
- Third, command to create a table:
- CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(256) NOT NULL, PRIMARY KEY(id));
- Fourth, command to insert a record:
- INSERT INTO users (name) VALUES('João');
Very well! Now do a SELECT to see if everything went well:
SELECT * FROM users;
Check in the result if the user we just inserted is listed.
But of course, in day-to-day operations, you may not always want to access your MySQL database this way. One method we like is using MySQL Workbench. So if you don't have it installed, download and install it so we can continue.
Going back to the list of instances in Google Cloud SQL, click on the name of the instance you created. You will be redirected to a panel with all the details of your instance. But what we want is the public IP address of your instance.
We also need to create a user with remote access permission, after all, it wouldn't be good to do this with the root user that has full access to our database service.
Click on the Users tab and then on the Create user account button.
On this screen, you can define whether this user can connect from any host or only from a specific one. It’s important to allow any host as our IP (Host) changes frequently.
After clicking on Create, we go to the Connections tab, because the user can connect from any IP, but our server does not allow connections from anywhere (this is standard for security).
In the Connectivity section, check the public IP option and click on add network.
Enter the following network: 0.0.0.0/0
This will allow any IPv4 client to connect.
Now let’s go to Workbench.
When you click to add a new connection, this screen will be presented. Here you will need to fill in:
| Connection Name | Name of the connection for you to remember later. |
| Hostname | IP address of your MySQL server (In this case, from Google Cloud SQL) |
| Username | The user we created with access from any host. |
| Password | The password of the user we created. |
| Default Schema | The default database when connecting. This field is optional, but in this case, we can enter the database hcodedb that we already created via Cloud Shell. |
If everything went well, you will notice that we already have our database with the table users here:
Let’s do the same SELECT that we already did in Cloud Shell.
SELECT * FROM users;
You should get the same result.
Perfect! Everything is working, now you have your MySQL database server on the internet ready to receive your projects and be available for any application anywhere in the world.
If you haven't checked out our course Mastering Database with MySQL, now is the right time for that. Click here and truly master this amazing database.