Google Cloud SQL is a fully-managed database service that makes it easy to set-up, maintain, manage and administer your relational MySQL databases in the cloud.
In this lab, we will create a new, empty Cloud SQL instance, connect to it using the MySQL command line interface, and create a new, empty database.
What you'll learn
- How to access the Cloud SQL web UI in the Google Cloud Console.
- How to create a Cloud SQL instance using the Google Cloud Console.
- How to set a root password using the Google Cloud Console.
- How to connect to your Cloud SQL instance via the Cloud Shell.
- How to delete your Cloud SQL instance using the Google Cloud Console.
What you'll need
How will you use use this tutorial?
How would you rate your experience with using Google Cloud Platform services and Firebase?
Self-paced environment setup
If you don't already have a Google Account (Gmail or Google Apps), you must create one. Sign-in to Google Cloud Platform console (console.cloud.google.com) and create a new project:
Remember the project ID, a unique name across all Google Cloud projects (the name above has already been taken and will not work for you, sorry!). It will be referred to later in this codelab as PROJECT_ID.
Next, you'll need to enable billing in the Cloud Console in order to use Google Cloud resources.
Running through this codelab shouldn't cost you more than a few dollars, but it could be more if you decide to use more resources or if you leave them running (see "cleanup" section at the end of this document).
New users of Google Cloud Platform are eligible for a $300 free trial.
In the Google Cloud Console, click the Menu icon on the top left of the screen:
Scroll down and select SQL in the Storage subsection:
You should now see the Cloud SQL web UI, and assuming you are using a project that does not currently have any Cloud SQL instances, you will see a dialog box inviting you to create a new Cloud SQL instance.
If you happen to be using a project that already has one or more Cloud SQL instances, you will instead see a list of those instances:
Assuming your project has no Cloud SQL instances, press the Create instance button to create one:
If you are using a project that already has one or more Cloud SQL instances, press the Create instance button on the Instances screen as shown here:
In either case, you should now see the Choose an instance type page. In this codelab, we are going to create a Cloud SQL Second Generation instance. Click Choose Second Generation.
This will take you to the Create an instance page. Here you must specify a name for your instance. This name must be unique within your project, but not across projects, so you can use the same instance name codelab-0 shown in this example, assuming you have no other Cloud SQL instances in your project. Enter "codelab-0" into the Instance ID box. For the purposes of this codelab, the rest of the configuration can be left with their default values.
Note that when you delete a Cloud SQL instance, you cannot reuse that instance name for up to a week. If you're planning to use this project for more than experimentation, be sure to choose an instance name that is different from any instance name you might want to use for real work, or you could find yourself unable to re-use that instance name for a period of time.
Scroll to the bottom of the page, and press Create.
You will be taken to the Instances page, where you will see your new instance with a spinner on the left, indicating it is still being created:
In two or three minutes, the instance will be available, and a green checkmark will replace the blue spinner. Make sure to refresh the page from time to time:
Your Cloud SQL instance is now ready to use.
Click on your instance name to bring up the instance details page, then click on Access Control:
On the Access Control tab, press Users:
On the Users tab, click Change root password:
Enter a new password on the Change password page and press OK:
Activate Google Cloud Shell by pressing the prompt button in the Google Cloud Console:
If this is the first time you have used the Google Cloud Shell for this project, you will see the Google Cloud Shell screen. Press Start Cloud Shell
The new Cloud Shell will be displayed at the bottom of the Google Cloud Console, either after you press Start Cloud Shell on the Google Cloud Shell screen if this is the first time it has been used with this project, or immediately after pressing the Activate Google Cloud Shell button if it has been used before.
Connect to your Cloud SQL instance using the gcloud sql command shown below, but replace "codelab-0" with the name of your instance, if different:
gcloud sql connect codelab-0 --user=root
You should see:
$ gcloud sql connect codelab-0 --user=root Whitelisting your IP for incoming connection for 1 minute...done. Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11302 Server version: 5.6.29-google-log (Google) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective Owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
As shown here, you will be prompted to enter the password you set in the previous step. You now have a fully functional MySQL prompt, signed in as "root". You can use this to enter SQL statements, such as this one to create a new database called "codelab":
mysql> CREATE DATABASE codelab; Query OK, 1 row affected (0.00 sec)
If the Query comes back with an OK your instance works. Let's exit and load some sample data to run some real queries.
Type ‘exit;' and press enter to exit the mysql prompt.
We will load the employee sample data set which you find in the official MySQL documentation.
First we need to download the scripts and data:
$ wget https://codeload.github.com/datacharmer/test_db/zip/master -O sampledb.zip $ unzip sampledb.zip && cd test_db-master
Second we need to create the database and tables, and load the data. Again, replace codelab-0 with the name of your instance, if different:
$ gcloud sql connect codelab-0 --user=root < employees.sql
Now we can query the data, you find some sample queries below. You can also create some queries yourself. To familiarize yourself with the db schema, look into the employees.sql file. First login to the sql prompt and switch to the employee database. Again, replace codelab-0 with the name of your instance, if different:
$ gcloud sql connect codelab-0 --user=root mysql> USE employees; Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT avg(s.salary) avg_salary_by_hire_year, YEAR(e.hire_date) FROM employees e, salaries s WHERE e.emp_no = s.emp_no GROUP BY YEAR(e.hire_date); +-------------------------+-------------------+ | avg_salary_by_hire_year | YEAR(e.hire_date)| +-------------------------+-------------------+ | 66966.7550 | 1985 | | 66187.3453 | 1986 | ... 16 rows in set (2.70 sec) mysql> SELECT de.dept_no, sum(s.salary) sum_salaries_per_department FROM employees e, salaries s, dept_emp de WHERE e.emp_no = de.emp_no AND e.emp_no = s.emp_no GROUP BY de.dept_no; +---------+-----------------------------+ | dept_no | sum_salaries_per_department| +---------+-----------------------------+ | d001 | 13725425266 | | d002 | 11650834677 | ... +---------+-----------------------------+ 9 rows in set (4.22 sec) mysql> exit;
After you're done querying the sample employees dataset use the ‘exit' command to exit the mysql prompt.
This step is optional. If you want to continue to experiment with your Cloud SQL instance, you do not need to delete it at this time. However, the project you are using will continue to be charged for the instance. If you have no further need for this instance, then you should delete it at this time to avoid these charges.
Note that when you delete a Cloud SQL instance, you cannot reuse that instance name for up to a week.
You have two options to delete your Cloud SQL instance.
1) Use gcloud in your Cloud Shell environment. Again, replace codelab-0 with the name of your instance, if different:
$ gcloud sql instances delete codelab-0 All of the instance data will be lost when the instance is deleted. Do you want to continue (Y/n)? Y Deleting Cloud SQL instance...done. Deleted $
or
2) Return to the Cloud SQL instances page by clicking on SQL in the upper left corner:
Open the drop down menu next to the instance you want to delete, and choose "Delete":
Deleting an instance is permanent! The data from that instance cannot be recovered. To help prevent accidentally deleting the wrong instance, you must confirm the deletion by entering the instance name in a confirmation dialog:
Enter your instance name and press Delete.
You have created a new Cloud SQL instance, configured a root password, created an empty database, and (optionally) deleted the Cloud SQL instance.
What we've covered
- Creating Cloud SQL instances via the Google Cloud Console.
- Setting a SQL root password via the Google Cloud Console.
- Connecting to the database using the mysql client.
- Creating a new MySQL database via the mysql prompt.
- Deleting the Cloud SQL instance.
Learn More
- View the Google Cloud SQL documentation