2

I have a simple Java program (not a web application/Google Engine/etc.). I went over every guide in Google Cloud SQL website, and I can't figure out how to connect to the server.

Right now I have

Class.forName("com.mysql.jdbc.Driver");

and

connection = DriverManager.getConnection("jdbc:mysql://x:3306/y?user=root");

Where x is IP address of the instance (taken from the properties of the instance) and y is simply the instance ID.

Am I doing something wrong? I also set a password for the root as one of the tutorials suggested. Should I specify it somewhere in the code?

Thanks.

EDIT

Following mohan rathour's link, I obtained Cloud SQL MySQL Socket Factory and my code now looks like:

Class.forName("com.mysql.jdbc.Driver");

and

String jdbcUrl = String.format("jdbc:mysql://google/%s?cloudSqlInstance=%s&"+"socketFactory=com.google.cloud.sql.mysql.SocketFactory",
                    "dbname",
                    "instanceConnectionName");

connection = DriverManager.getConnection(jdbcUrl, "userName", "password");

I get the following in my console:

INFO: Connecting to Cloud SQL instance [instanceName].
com.google.cloud.sql.mysql.SslSocketFactory getInstance
INFO: First Cloud SQL connection, generating RSA key pair.

But then I get this exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.

SOLUTION

No need to use "Cloud SQL MySQL Socket Factory".

In the Google SQL panel, go to your instance panel -> Access Control -> Authorization, add the following network address and click save.

0.0.0.0/0

Now you can connect with this code:

Class.forName("com.mysql.jdbc.Driver");
...
String url= String.format("jdbc:mysql://x:3306/y");
connection = DriverManager.getConnection(url, z, w);

Where x is your instance IP (get it from the panel), y is the instance name, z is the database (not account) user name (usually "root") and w is the user password (you have to set it. look at Google's tutorials).

John Benedetto
  • 109
  • 1
  • 12
  • If you're getting an error message, you're doing something wrong. Are you getting an error message? – Kayaman Nov 29 '16 at 09:08
  • Yes, I'm getting an SQLException for the `connection=...` line. The exception is: `com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure` – John Benedetto Nov 29 '16 at 09:11
  • Well, there's your problem. – Kayaman Nov 29 '16 at 09:13
  • @Kayaman this is not a duplicate. I have no problem connecting with other MySQL services in exactly the same way. The problem here is related to Google SQL. – John Benedetto Nov 29 '16 at 09:18
  • Well did you read the link provided by mohan rathour? You need to take extra steps to connect from outside the cloud. – Kayaman Nov 29 '16 at 09:58
  • Yes, and now I get a different exception. See updated post. – John Benedetto Nov 29 '16 at 11:37
  • @Kayaman I found a solution. Can you please remove the "duplicated" tag so I can answer and accept my solution? As you can see the solution is not related at all to the post you linked to. – John Benedetto Dec 01 '16 at 07:30
  • It's not a good idea to whitelist 0.0.0.0/0. You are opening your database to the entire world. Best practice is to either whitelist a specific subnet or use the Cloud SQL Socket Library. https://cloud.google.com/sql/docs/external#appaccessIP – Vadim Dec 03 '16 at 04:01

2 Answers2

2

By default, Cloud SQL instances do not allow connections on the public IP. You must add individual subnets from which you would like to connect. You can see the full documentation here: https://cloud.google.com/sql/docs/external#appaccessIP

An alternative that does not require whitelisting IP addresses is using the Cloud SQL Socket Library that uses an SSL session with short-lived certs: https://cloud.google.com/sql/docs/external#java

Whitelisting 0.0.0.0/0 is not recommended as it opens the database to the entire world.

Vadim
  • 4,996
  • 1
  • 26
  • 30
  • Can you expand on why whitelisting `0.0.0.0/0` is not recommended? Users still need a username and password to interact with the database. Having the connection open but requiring a username and password is exactly how e.g. AWS RDS does it. So why go through the extra hoops of whitelisting specific ip addresses? – Kevin Workman Jul 04 '17 at 22:44
  • 1
    Port 3306 is constantly scanned by many actors. If a running database is found, it is probed for logins. It's possible that they might not find an account today, but they will keep probing and might find one in the future. Someone might add a new account that is not as secure without realizing that the DB is open to the world. Most people do not expect a database server to be accessible from the open internet. RDS has "security groups" which serves the same purpose. – Vadim Jul 05 '17 at 18:48
  • Interesting, thanks for the info. I guess my confusion is caused by the RDS "security groups" not being activated by default. Does that mean that RDS by default is open to the same risks you just described? I guess that means Amazon decided it was an acceptable risk, at least by default? I'm surprised these services don't have login attempt rate limiters to solve this problem. Anyway, thanks again for the info! +1 – Kevin Workman Jul 05 '17 at 19:00
  • 1
    I haven't used RDS much, but I'm pretty sure it's locked down by default as well. At the end of the day, both RDS and Cloud SQL run MySQL and are generally limited by what MySQL provides. MySQL has a host-based login attempt limit, but that's not that hard to get around if you can keep changing your source IP. – Vadim Jul 05 '17 at 19:10
  • Gotcha, thanks. The source of my question was because I'm putting together some basic database tutorials. Setting up RDS is as simple as registering for an account and clicking a button to get an instance and an endpoint URL. I didn't have to do anything special to access it. Cloud SQL is a little more complicated because of its access restrictions. For my purposes I think I can just point out the whitelist option and advise them to look into Google's guides on setting up access. Thanks again! – Kevin Workman Jul 05 '17 at 19:14
  • 1
    Understood. We'd like to make the default setup for new Cloud SQL instances easier, but not everything is ready for that yet. – Vadim Jul 05 '17 at 19:19
1

Have you created local instance of google mysql or you can connecting direclty. you can check below link Google mysql database cloud connection in java or check with google driver like Class.forName("com.mysql.jdbc.GoogleDriver");

mohan rathour
  • 420
  • 2
  • 12
  • It works with a local database and also with other free MySQL services. What should I changed the connection URL to, if I'm using Google SQL? – John Benedetto Nov 29 '16 at 09:01
  • 1
    Make sure there is no firewall blocking the access to port 3306 – mohan rathour Nov 29 '16 at 09:05
  • Where should I check it? I already used 3306 as a port for a free MySQL service and it worked fine. Maybe my url is wrong? – John Benedetto Nov 29 '16 at 09:07
  • Have you created local instance of google mysql or you can connecting direclty. you can check below link https://cloud.google.com/sql/docs/external#java or chek with google driver like Class.forName("com.mysql.jdbc.GoogleDriver"); – mohan rathour Nov 29 '16 at 09:11
  • @mohanrathour You should probably edit your answer to be about the link you provided, since that's the real issue here, not the connecting code. – Kayaman Nov 29 '16 at 09:58
  • @mohanrathour thank you. I updated my original question according to your suggestion. What should I do? – John Benedetto Nov 29 '16 at 11:34