Wednesday, June 21, 2017

Google Cloud SQL Proxy on Same Machine as MySQL

As a web developer, it makes sense that you'd want to connect to my Google Cloud SQL instances running in Google Cloud Platform (GCP) via Cloud SQL Proxy and also have MySQL running locally on your machine. Unfortunately, the default configurations for these two systems will cause an error when you try to run the proxy: "bind: address already in use."

One clear reason to run the proxy is that it allows you to securely connect to your Cloud SQL instances with MySQL Workbench. You can get the IP (hostname) and port from GCP no problem, but to connect, you need to be running Google's proxy. (You also need to create a JSON credential file from GCP and add your IP to the Authorized Networks BTW.)

The reason for the error is that both the proxy and the local default install of MySQL will try to communicate on port 3306. When you try to start the proxy, this is the result:

doom:project cawood$ ./cloud_sql_proxy -instances=instancename-9999:us-west1:instance=tcp:3306 -credential_file=CredFileName-dflkjal.json &

doom:project cawood$ using credential file for authentication;
2017/06/05 23:51:24 listen tcp bind: address already in use

To resolve this error, one solution is to create a config file for the local MySQL server and change the port.

One thing to note (as per the MySQL documentation) is that the installer for macOS will not create a config file: "Note As of MySQL 5.7.18, my-default.cnf is no longer included in or installed by distribution packages." If you don't have a config file already... to create the config file, simply run these commands.

$ cd /etc
$ sudo nano my.cnf

This is all you need in the file:

port = 3366

port = 3366

Once you're done use CTRL + o to save and exit Nano.

It's worth noting, that I used to see this error before I installed MySQL locally. The reason was that there was another Cloud SQL Proxy process ("cloud_sql_proxy") already running. The solution to that is simply to kill the process.