Today we’ll cover connecting MySQL Workbench to the GCP MySQL instance, and installing the schema.
Let’s get to it.
There are several ways to connect to the db. We’ll be covering accessing it via the Cloud Shell and a MySQL Command Line, via a unix shell running on your local machine and the MySQL Command Line, and via MySQL Workbench running on your local machine.
First, lets try out the Cloud Shell. From the GCP console, select the Cloud Shell button: . The Cloud Shell is a browser based command line interface running a bash shell by default. If you’re not familiar with it, read up on some of the documentation.
To open up the mysql command line (See the documentation for more info) type “gcloud beta sql connect mybooks-db –user=root”. The gcloud command should automatically add the command shell ip to the sql instance authorization table. It may take a while.
Enter the password you didn’t save in a password manager or remember from step 1.
You now have root access to the mysql server. So, let’s exit the mysql command shell (“exit”)
Setting up Google Cloud SQL Proxy
If you want to use either a Local Shell and MySQL CLI, or MySQL Workbench, you’ll need to allow access from your development machine. There are several ways to to it. You can go play around with it at your leisure, but for our purposes (since I’m on a unix machine), the Google Cloud SQL Proxy setup is fine. I’d recommend using a Service Account, but ymmv. Install the Cloud SQL Proxy as per the directions.
When you get to the Start the proxy step, first navigate over to the SQL Dashboard (Home->SQL) for the mybooks-db instance. In the Properties section of the Overview tab you will find the Instance connection name. This is the name we need to use when we start the proxy.
Assuming your proxy is up and running its time to start connecting.
Install MySQL Workbench and Utilities
MySQL Command Line on Local Machine
mysql -u root -p –host 127.0.0.1. Enter the password you forgot and didn’t store in you password manager. 😉
Once its working go ahead and exit (“exit”).
Bring up MySQL Workbench
Add a new connection.
- Connection Name: Name it something meaningful. Like mybooks-db or FieFieBananaFofanna…
- Connection Method: Standard (TCP/IP)
- Hostname: 127.0.0.1
- Port: 3306
- Username: root [we’re cutting corners, every admin user should have their own account and root should be offlimits, but that’s my ‘paranoia’]
- Password: depending on your security requirements you may or may not want to store it in your keychain.
- Default Schema: leave it blank for now.
It’s a good idea to Test Connection before saving it. 🙂
Open the connection to make sure it works. Then we’ll get on to the next step:
Our Database Schema:
You should know how to read a basic ERD. If you don’t, there are a ton of online resources to get you going.
This is one approach. There are others (obviously). But at first pass, this represents what’s important to me, and sufficient to build a working example that has the functionality I’ve decided upon. Some things to be aware of:
- When we get the book info from whatever external webservice(s) we end up using, we’ll have to make sure we create the author(s) or reuse them correctly.
- I’m using an independent ID key as the primary key on each of the non join tables, except for locations, where the primary key is locationName. I didn’t really have an effective way of representing that, so the ERD doesn’t show it.
- I could have probably used name as a primary key in format, and publisher. But since I’m not sure how clean the data from the external webservice(s) we use will be, I’ll leave it in external form and pay the price of doing joins. Which won’t be that high.
- I’ll be creating unique indexes on the primary key and the following unique indexes for their primary tables:
- authors.firstName, authors.lastName
- In our givens and druthers, I stated I wanted to be able to search on loan status. Rather than introduce more complexity, I’ve decided to indicate loan status by overloading location. This suits my purposes, but obviously if you have a situation where you loan out a lot of books, it wouldn’t work well. But this is my tutorial, so there ya go 😉 .
- I’ll also be creating indexes on books to cover the searches we discussed in our given and druthers. Namely:
If you’d like, you can create the db from scratch, or you can download the tutorial step from github and install it from the CLI (or MySQL workbench, but I’ll leave that to you if that’s what you want to do)
Download from GitHub and Load the schema using mysql command line.
Create your local working directory and clone my github repository https://github.com/rpcarver/mybooks.
git clone https://github.com/rpcarver/mybooks.git
Move to the mybooks/sql directory, then using the mysql command line via the GCP SQL Proxy create the database via the sql script:
mysql -u root -p --host 127.0.0.1 < myBooksSchema.sql
Congratulations, you just set up a database, you can confirm it via any of the three ways we’ve discussed here (or any other way if you’re playing around 😉 )
I thought we’d get to sample data, but we’ll add that in the next session when we create the test database to use as a baseline for our webservices tests.
As always, feel free to leave feedback or suggestions!