Getting started with GCloud, MySQL and the Command line

I’ve put together this guide for how I connect to my MySQL instance using google cloud after this tweet from @ddiversion:

Would you know of any good resources to learn command line SQL queries (from connecting to a db to running SELECT queries, the whole thing)? Thanks!

— skae (@ddiversion) August 2, 2018

Google cloud has a command line interface from the website when you navigate to your project:

Google have put together some awesome tutorials for getting started with Google Cloud, here is their guide for setting up a MySQL database. I’m just going to go over how I connect to it in this tutorial.

There are different ways to access the google cloud console:

Once the console is up, you will be presented with this view with thew console down in the bottom of your browser:

To connect to my SQL instance, I type in:

gcloud sql connect bog-logger-dev-database --user=root

And type in the password I’ve set up for my root user.

Then I can type commands like

show databases;

use bogs_logged_dev_database;

show tables;

select * from bogs_logged_table;

Previously I had created this table and inserted data using thew following commands:

CREATE TABLE bogs_logged_table( time DATETIME NOT NULL, device_uuid VARCHAR(36) NOT NULL, type INT(1), notes VARCHAR(140) );

INSERT INTO bogs_logged_table(time, device_uuid, type) VALUES(NOW(), "d3ad458d-eca1-4cf3-be34-fb10facd5992", 4);

INSERT INTO bogs_logged_table(time, device_uuid, type, notes) VALUES(NOW(), "d3ad458d-eca1-4cf3-be34-fb10facd5992", 2, "very painful, a little bit of blood");

INSERT INTO bogs_logged_table(time, device_uuid, notes) VALUES(NOW(), "d3ad458d-eca1-4cf3-be34-fb10facd5992", "tried to poo for half an hour");

So when I do a

select * from bogs_logged_table;

SSH

If you aren’t using Google Cloud there is a guide here that starts with an SSH command. The general structure of an SSH command is

ssh user@location

Location could be anything like an IP address (e.g. 192.168.1.2 for a locally setup maching) to a host name like test.environment.com, SSH will ask you to put in the password and once you are in and assuming MySQL is installed you can go your merry way.

What are your favourite resources for learning MySQL from the command line? MySQL command line help docs is probably the next step from here.