Google Cloud SQL is a fully managed database service that makes it easy to set up, maintain, manage and administer relational databases on Google Cloud Platform (GCP) in a reliable, secure and simple way. Cloud SQL can be used with either MySQL or PostgreSQL – whichever you prefer.
Nirodha Kathaluwa, part of the R&D team at Mitra Innovation, talks us through the set-up of Google Cloud SQL, in the latest Mitra Innovation ‘Tech How-To-Guide’.
The advantages of using Google Cloud SQL to manage databases
There are a few advantages of using Google Cloud SQL to manage relational databases. They are as follows:
● Scalability – Cloud SQL can scale up to 32 processor cores and can be used with more than 200GB of RAM. Also, Cloud SQL can quickly scale up using replicas.
● Availability – GCP maintains a service called ‘Live Migration’ which allows for hardware and software infrastructure updates without affecting the data. ‘Live Migration’ allows access to data in any zone without errors.
● Performance – Cloud SQL is designed to work with small scale developments of projects to larger ‘performance-intensive’ workloads.
● Fully Managed – Data can be replicated in Cloud SQL. Since GCP provides for backup services, it ensures data security as well as, protects against data loss.
Setting up Cloud SQL
Follow the instructions below to see how to set up the Google Cloud SQL:
In the GCP menu (Left side)>Dashboard existing projects are displayed. First, make sure that you have created the particular project in the GCP. If not create a project there.
(Fig 1 – New Project listed on GCP Dashboard)
In order to make use of GCP, enable billing in the Billing Section of the GCP Dashboard:
(Fig 2 – Manage billing accounts on GCP)
Next, enable an API for the project. You can access an existing project or you can create a project here.
(Fig 3 – Registering an application for Google Cloud SQL API on GCP)
After registering your application, click on the Continue button and you will see the following message.
(Fig 4 – Google Cloud SQL API enabled screen)
After registering your application, click on the Continue button and you will see the following message.To create a Cloud instance on GCP, click on ‘SQL’ in the menu section.
(Fig 5 – Creating a Cloud Instance on GCP)
(Fig 6 – Follow-through message to creating a Cloud Instance on GCP )
Then choose the Database engine that you are going to use.
Fig 7 – Choosing your preferred Database Engine to creating an instance on GCP )
After selecting or creating the project click on Continue> Create Instance> MySQL> Choose Second Generation:
(Fig 8 – Choosing MySQL Second Generation when creating an instance on Google Cloud Platform)
Enter an ID for myinstance. And provide a strong password for the Instance.
(Fig 9- Creating a MySQL Second Generation Instance on GCP)
After the creating the new instance, the following screen will be displayed:
(Fig 10 – Follow-through screen to creating a new MySQL Second Generation Instance on GCP)
Click on the newly created instance and go to Access Control>Users. There you can create a user and a password or give a password for the default root user.
(Fig 11 – Follow Access Control > Users to create additional users)
(Fig 12 – Creating a new user on your instance on GCP)
After creating a new user, the instance will appear as per Fig 13 below:
(Fig 13 – Follow through screen to creating a new user to your Instance on GCP)
Next, please create a new storage bucket in GCP or alternatively use an existing bucket.
Follow GCP menu(left side)> Storage>Browser
(Fig 14 – Creating a new storage bucket on GCP)
After creating the bucket you can upload files to the bucket. We have added the SQL file that was already available in our PC. After uploading, it will show on the screen as below:
(Fig 15 – Uploading SQL files to your storage bucket on GCP)
Alternatively, use an existing bucket and follow the same uploading procedure.
(Fig 16 – Alternatively, selecting an existing storage bucket on GCP)
Then, go to the Instance details UI and create a database.
(Fig 17 – Creating a database under ‘Instance Details’ on GCP)
Now you can import SQL files via the Import link shown in the instance User Interface (UI) as per Fig 18 below.
If using a CSV file, the database and table names have to be mentioned:
(Fig 18 – Importing data from Cloud Storage on Google Cloud Platform)
Click on the ‘Show advanced options’ to import a particular SQL file to a specified database.
Then select the SQL file in the list as mentioned below.
(Fig 19.1 – Selecting an SQL File to Import)
Connecting Cloud SQL with MySQL client via IP Address
In the overview of the Instance you can see the properties of the instance. Please use the IP Address shown there.
(Fig 20.1 – Connecting Cloud SQL with MySQL Client via IP Address)
Then name your PC’s IP Address as the client’s IP address. You may add any number of clients there.
Follow Instance details>Access Control>Authorization.
In Authorized Networks add the IP address of the client computer in Add Network field. Save it. Add your client’s IP addresses. You may use 0.0.0.0/ 0 for any client PC’s IP Address but at your own risk!
Next, go to client machine SQL and enter the IP Address of cloud SQL as Hostname and give the password for root that was provided in cloud SQL. Then click ‘OK’ to proceed.
(Fig 20.2 – Connecting Cloud SQL with MySQL Client via IP Address)
If you followed the correct procedure you will see the database has loaded onto the client machine.
There are many other functions in Cloud SQL. Remember to disable billing when you aren’t using the GCP service. If not you will continue to be billed!
Enjoy the marvel of Cloud SQL.
About the Author
Written by Nirodha Kathaluwa – Research & Development Team at Mitra Innovation