Set up MySQL on Google Cloud Platform

Here's a walkthrough of how to set up MySQL on Google Cloud Platform's (GCP) Cloud SQL service.

1. Create a Google account if you don't have one: https://accounts.google.com

2. Sign up for a free trial on: https://console.cloud.google.com

3. Create a GCP project to house your MySQL instance.

4. Create a Cloud SQL instance--obviously, choosing MySQL as the DB type.

5. Create a database in the instance by going to the Databases tab under the Instance. For this post, I'll create one called "test".

6. To add a schema to your DB, you can use the Cloud SQL command line, https://dbdesigner.net or MySQL Workbench. Of course, if you know SQL, you can just type out your schema, but if you want a visual representation of your DB, these tools are handy.

I like the simplicity and no-install aspect of dbdesginer.net, but I ran into a bug, so I don't use it as much as I would otherwise. Hopefully, they'll fix it soon. Until then, I guess I have to recommend MySQL Workbench since it's great in many ways.

7. If you just want to quickly test that your DB is working. You can run some simple queries in the Google Cloud Shell. To connect to your MySQL instance, open the shell (little command line icon in the top-right of the GCP dashboard.) and type in these commands:

googleusername@projectId:~$ gcloud beta sql connect dbinstance --user=dbusername (usually root for username)

You'll be prompted for your password and then you'll see the MySQL prompt. Note that it might take a few seconds before it appears.

Whitelisting your IP for incoming connection for 5 minutes...\
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1234
Server version: 5.7.14-google-log (Google)
Copyright (c) 2000, 2016, 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>;

8. To run your test queries, you'll need to select your DB within your instance.

mysql>; use test
Database changed

Once you've selected the DB, you can run a simple operation to create a table and then use SHOW TABLES; to see the results.

The command I'll use to create a simple table is:

CREATE TABLE `testtable` (
`id` INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
);

This creates a new table with one column called "id".

And this is what it looks like when I paste this command into the Cloud Shell:

mysql>; CREATE TABLE `testtable` (
    >; `id` INT NOT NULL AUTO_INCREMENT,
    >; PRIMARY KEY (`id`)
    >; );
Query OK, 0 rows affected (0.03 sec)
mysql>; show tables;
+----------------+
| Tables_in_test |
+----------------+
| testtable      |
+----------------+
1 row in set (0.00 sec)
mysql>;

9. Now that you have a working DB, you'll want to connect to it from code and possibly from a MySQL client (e.g., the 'dolphin' a.k.a. MySQL Workbench).

To get the info you need, go to the Instance Details page in GCP and look at the Properties under the Overview tab. What you need is the "IPv4 address". Grab that, but you won't be able to connect to your instance yet. First, you have to allow connections.

To allow your client to connect to your DB in the Google cloud, you need to open Authorized Networks and add your network IP. To get your client IP, type "what's my IP" into Google.

10. Now you can access your DB from a MySQL client, but what about a local dev instance of a GCP App Engine application? For that you need a Google Cloud SQL Proxy running.

NOTE: The default setting is for all apps within the same project to be authorized, if you're using the DB from a different GCP project, you'll have to authorize it explicitly.

Without the Cloud SQL Proxy running, your local dev instance of your app will return the error: Error: connect ECONNREFUSED xxx.0.0.xxx:3306.

You'll need to install the Google Cloud SDK (configuration will have you select the project). If you have multiple environments [e.g., test, dev, prod, etc. You may have to change this as some point with this command: cawood$ gcloud config set project projectName]), and enable the Cloud SQL API, if you haven't already. You'll also need a credential file to supply to the proxy. To get this file, create a service account in the IAM & Admin section of GCP. Make sure you grant the account rights to Cloud SQL. When you create the account, you'll be able to create a new private key and download a JSON credential file. The Cloud Proxy will read this credential file.

https://cloud.google.com/sql/docs/mysql/connect-external-app

11. Once you have everything ready, here is the command to start the Cloud Proxy:
./cloud_sql_proxy -instances=instanceConnectionName=tcp:3306 \ -credential_file=credentials.json &

If all goes well, the Cloud Proxy command will return this output:

cawood$ 2017/04/23 22:34:39 Listening on 127.0.0.1:3306 for instanceConnectionName
2017/04/23 22:34:39 Ready for new connections


FYI: From the command line help:
Authorization:
  * On Google Compute Engine, the default service account is used.
    The Cloud SQL API must be enabled for the VM.

  * When gcloud is installed on the local machine, the "active account" is used
    for authentication. Run 'gcloud auth list' to see which accounts are
    installed on your local machine and 'gcloud config list account' to view
    the active account.

  * To configure the proxy using a service account, pass the -credential_file
    flag or set the GOOGLE_APPLICATION_CREDENTIALS environment variable. This
    will override gcloud or GCE credentials (if they exist).