Probably the best and easiest way to edit and view a database is with MySQL Workbench. MySQL Workbench doesn’t require any command line interface (CLI) knowledge and has great intuitive tools to help you manipulate a database even without well-developed SQL skills.
So let’s see how you can get connected to a Google Cloud SQL Instance using MySQL Workbench.
How to connect to Cloud SQL with My SQL Workbench
1. Create your Google Cloud Project & Google Cloud SQL instance
To connect to a Cloud SQL instance, first, you will need to actually create one. Navigate to Google Cloud Console and set up your Google Cloud account.
When you make a Google Cloud instance, you need to be careful when setting the configuration settings. If you choose the default settings, your bill for the month will be hundreds of dollars.
For an in-depth guide on how to set up a Google Cloud Project and create a Cloud SQL instance, check out my tutorial here.
2. Enable a public IP address for your Cloud SQL instance
To connect to a Google Cloud SQL instance, you will need to have the public IP address option enabled. This can be done in the initial configuration of the SQL instance or in the Connections tab of the Cloud SQL console page.
3. Whitelist the IP address that is hosting your MySQL Workbench application
Next, you will need to whitelist the IP address or range of IP addresses from which you’ll be accessing your Cloud SQL instance. Even if it is password protected, Google Cloud SQL won’t allow just any IP address to access your instance.
You will need to navigate to the Connections tab in your Google Cloud Console and add your IP to the list of connections. Find your IP address by Googling “my ip” and copying the result. Under the “Connections” tab, select the “Add Network” button under Authorized Networks. Paste your IP address and save this.
Static versus Dynamic IP addresses
Something quick to note here is that if you have a dynamic IP address then you will need to whitelist your IP address again every time your IP address changes.
An IP address is a numerical address that is assigned to your device by your router. A static IP address doesn’t change and typically refers to desktop computers connected to networks in an office or home. A dynamic IP address refers to an IP address that changes and typically refers to laptops, phones, and tablets that might connect to many different WIFI networks in one day and be assigned a few different IP addresses.
It’s a bit annoying to have to constantly whitelist a new IP address, but this is done for security reasons so don’t get too upset.
Also, it is worth noting that sometimes Google Cloud services don’t like connections coming from foreign countries. There is no documentation supporting this, but I have found that my Google Cloud services won’t act as expected sometimes when I connect from Thailand or South Korea where I lived for a few years. I don’t have a good explanation or description of the strange behavior, but am just noting it here.
4. Create a user and password for your Cloud SQL instance
Next, you’ll need to create user credentials to log in to your MySQL instance. There is a default admin account and this account can also be used.
Navigate to the “Users” tab using the navigation bar on the left side. Click the “Add User Account” button in the middle of the screen. A popup will appear.
Create a username and password. Note that you will be required to Built-in Authentication option if you want to use MySQL Workbench to access your Cloud SQL instance. Select “Allow Any Host (%)” for the Host Name option unless you have a static IP address.
Next, simply click the “Add” button on the bottom left and wait for Cloud Console to create your new account.
5. Open your MySQL Workbench application
Next, open up your MySQL Workbench application. This tutorial won’t go over installing this application, but you can download the installation files here.
6. Click Add New Connection
On the main page of your MySQL Workbench application, you will see a tile display of your saved connections. If this is your first time using MySQL Workbench then there will be no tiles displayed.
Click the small plus button next to the “MySQL Connections” header.
7. Configure your MySQL Connection
Input the following fields.
- Hostname
- User
- Password
The Hostname will be the public IP address associated with your Cloud SQL instance. It is accessible from the Cloud Console SQL page under the “Overview” tab. It should look something like this:
35.232.146.145
Your user and password will just be the credentials you created in the previous step. The port should automatically be set to 3306 as default and the Standard TCP/IP method of connection should already be selected.
8. Test your connection
You can click the “Ok” button or the “Test Connection” button at the bottom right of our popup. The “Test Connection” button will tell you explicitly if you were able to connect. The “Ok” button will try to open up the connection in a separate window and will throw an exception error if the connection fails.
Additional Tips
- Sometimes MySQL Workbench will fail to connect if you are trying to do so from a foreign IP address. Why this occurs, I am not sure, but usually setting a VPN to a US city IP address resolves the issue.
- Use MySQL Workbench instead of Google’s Cloud Shell CLI. This CLI is annoying to do extensive database work with and disconnects frequently. Also, CTRL+C breaks out of a project which I often mistakenly press trying to copy lines.
Conclusion
Thanks for reading and look out for more content from RTL Coding. Hope this article was useful and if you would like to support this blog and this content feel free to donate via Paypal to help support more helpful content.
Sources
- MySQL SQL Dump Options and Examples Documentation
- Amazon Web Service (AWS) SQL Dump Docs
- Microsoft Azure SQL Dump Docs