In this article, we are going to create an instance of a MySQL database on Google’s Cloud Platform and connect our local computer to it via proxy.
Let’s get started by going to www.console.cloud.google.com and creating a project. Create an account if you don’t already have one, and navigate to the Dashboard. Next to Google Cloud Platform in the top left, you will see a dropdown of project options. Use this to create or choose the project for which you would like to create an instance.
Follow the prompts to create a project.
Once your project is created, you will navigate back to the Dashboard. Take note of your project ID, as it is required for many features when using Cloud SQL. Then, on the left you will want to click on SQL.
On your Instance page, click + Create Instance. Cloud SQL offers instances for MySQL, Postgres, and SQL servers. For this tutorial, we want to choose MySQL.
Follow the prompts for creating your instance. Choose an Instance ID that is easy to remember because you will not be able to change it later. Then, Create. On your SQL Overview page, make note of your Instance Connection Name.
On the top right of your SQL Overview page, you will see a small box. This is your Cloud Shell. This will open your console where you can now begin to build your database, if you do not choose to use the option shown above in the navigation bar.
Run the following to connect to your instance:
gcloud sql connect <instanceID> -user=root (two dashed before user, but Medium’s automatic styling won’t let me put two dashes). If you created another user, you can also use that user. Follow the prompt and enter your password. We didn’t specify a password above, so I just click ENTER and the MySql shell opens! Note: Your instanceID is different then your instance connection name that we noted above!
- gcloud sql connect <instanceID> -user=root;
- show databases;
- create database <your desired database name>;
- create table <table name> values (id INT AUTO_INCREMENT, […other values], primary key (id));
- describe <table name>;
- select * from <table name>; (Use this once you have information in your database.)
So far, we have created an instance and created a database with a table. Now, let’s give our local machine (our computer) access to our instance so we don’t have to navigate to console.cloud.google.com every time.
Now we are going to set up a proxy. To begin, we will Enable the API and Install the Proxy using our a terminal on our local machine. For example, I am using iTerm2.
Next, we create a service account.
- Select your project
- Select + Create Select Service Account
- Name your service account
- Assign the roles of Cloud SQL Client, Cloud SQL Editor, and Cloud SQL Admin.
Next, create a key.
We are nearing the finish line! Time to start the proxy. Still referring to these docs, we will start the proxy with a Unix Socket.
- In your local machine’s terminal (iTerm2, Ubuntu, etc), run….
- :::: sudo mkdir /cloudsql; sudo chmod 777 /cloudsql :::: (It will prompt you to provide Admin password.)
- Open a new terminal window, and we will use the proxy invocation string using the service account we created earlier. The command looks like this:
- ./cloud_sql_proxy -dir=/cloudsql -instances=<INSTANCE_CONNECTION_NAME> \
-credential_file=<PATH_TO_KEY_FILE>
Once you see, ‘Ready for new connections’, you are ready to start the client session. In a new terminal window, run: (this is all one line)
- mysql -u <USERNAME> -p -S /cloudsql/<INSTANCE_CONNECTION_NAME>
This is what my code ended up looking like:
Your proxy is now set up and you have remote access to your instance on your local machine. Hope this guide was clear and helpful.
Thanks for reading!
