GCP at the standard environment: MySQL db connection refused

user_1230477 0
Viewed 1993

My goal is to take a working Python 2.7 project (MySQL + MS Word files) to work at GCP. I realize that I need

  1. App Engine - where the app will be running (scaling, etc).
  2. Cloud SQL working as MySQL db. For that I've followed that Cloud SQL for MySQL tut and
    • Cloud SQL instance is created with root user.
    • Both App Engine app and Cloud SQL instance are in the same project.
  3. Cloud Storage

The SQL second generation instance is successfully created and a root user is set.

How I run or deploy

I use Cloud Shell to test the app - dev_appserver.py $PWD and deploy the app from Cloud Shell - gcloud app deploy. It works at appspot.com till I try to use MySQL connection in it.

MySQL connection

The MySQL connection code is taken from here:

import MySQLdb
import webapp2    

CLOUDSQL_CONNECTION_NAME = os.environ.get('CLOUDSQL_CONNECTION_NAME')
CLOUDSQL_USER = os.environ.get('CLOUDSQL_USER')
CLOUDSQL_PASSWORD = os.environ.get('CLOUDSQL_PASSWORD')    
DB_NAME='test-db'
def connect_to_cloudsql():
    # When deployed to App Engine, the `SERVER_SOFTWARE` environment variable
    # will be set to 'Google App Engine/version'.
    if os.getenv('SERVER_SOFTWARE', '').startswith('Google App Engine/'):
        # Connect using the unix socket located at
        # /cloudsql/cloudsql-connection-name.
        cloudsql_unix_socket = os.path.join(
            '/cloudsql', CLOUDSQL_CONNECTION_NAME)

        db = MySQLdb.connect(
            unix_socket=cloudsql_unix_socket,
            user=CLOUDSQL_USER,
            passwd=CLOUDSQL_PASSWORD)

    # If the unix socket is unavailable, then try to connect using TCP. This
    # will work if you're running a local MySQL server or using the Cloud SQL
    # proxy, for example:
    #
    #   $ cloud_sql_proxy -instances=your-connection-name=tcp:3306
    #
    else:
        db = MySQLdb.connect(
            host='127.0.0.1', user=CLOUDSQL_USER, passwd=CLOUDSQL_PASSWORD, db=DB_NAME)

    return db
db = connect_to_cloudsql()

Variables are set in app.yaml:

runtime: python27
api_version: 1
threadsafe: true
env_variables:
    CLOUDSQL_CONNECTION_NAME: coral-heuristic-215610:us-central1:db-basic-1
    CLOUDSQL_USER: root
    CLOUDSQL_PASSWORD: xxxxx

When app is run in test mode thru dev_appserver.py $PWD and I choose to use MySQL connection I got an error: 

ERROR    2018-09-13 08:37:42,492 wsgi.py:263]
Traceback (most recent call last):
  ...
  File "/home/.../mysqldb.py", line 35, in connect_to_cloudsql
    host='127.0.0.1', user=CLOUDSQL_USER, passwd=CLOUDSQL_PASSWORD)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 204, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (2003, 'Can\'t connect to MySQL server on \'127.0.0.1\' (111 "Connection refused")')

Cloud SQL Proxy

I've downloaded and run the Cloud Proxy for Win-64 (https://dl.google.com/cloudsql/cloud_sql_proxy_x64.exe ) yet still the problem persists... Seems that proxy background app is only for connection to Cloud SQL from my local machine.

You do not need to use the proxy or configure SSL to connect to Cloud SQL from the App Engine standard or flexible environment. (source)

Why is the connection refused?

Should I use rather first generation Cloud sql instance to simplify connection from App Engine?

Update 1

I edit code at the Cloud Console and so far Cloud Console works good.

Update 2

I've succeded to conenect to the sql instance thru Cloud Shell:

(coral-heuristic-215610)$ gcloud sql connect db-basic-1 --user=root

Whitelisting your IP for incoming connection for 5 minutes...done.
Connecting to database with SQL user [root].Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 48841
Server version: 5.7.14-google-log (Google)
MySQL [(none)]>

Update 3

The comment on a similar issue concerns the regions where Cloud SQL instance and App Engine app should be, that is in the same region.

In my case I've checked:

  • Cloud SQL instance to connect to: us-central1-a
  • App Engine app: us-central

Are these of one region? - turned out these of one region.

Update 4

I could have figured out to open db connection:

DB connection: <_mysql.connection open to '127.0.0.1' at 7f628c02bc00>

But this seems happened only after I've opened another Cloud Shell instance with the same project (coral-heuristic-215610). At that instance I've started connection to SQL instance and it was successful:

(coral-heuristic-215610)$ gcloud sql connect db-basic-1 --user=root

Whitelisting your IP for incoming connection for 5 minutes...done.
Connecting to database with SQL user [root].Enter password:

I guess that the first cloud shell instance started to connect to db because the second instance has white-listed my IP, isn't it?

google-cloud-sql google-app-engine-python google-cloud-platform google-app-engine python
2 Answers

The GAE app and Google Cloud SQL instance have to be deployed in the same region if you’re using MySQL First Generation, otherwise, I verified that they can be in different regions as long as you’re using MySQL Second Generation.

I had trouble understanding where are you trying to connect from. I assume you want to connect from the Google Cloud Shell using the proxy and the Cloud SDK Credentials. According to the documentation regarding Cloud SQL Proxy:

The Cloud SQL Proxy provides secure access to your Cloud SQL Second Generation instances without having to whitelist IP addresses or configure SSL.

The Cloud SQL Proxy works by having a local client, called the proxy, running in the local environment. Your application communicates with the proxy with the standard database protocol used by your database. The proxy uses a secure tunnel to communicate with its companion process running on the server.

Remember, since you’re not deploying the application, it is not using the environment variables you have established in the app.yaml. Therefore you have to export and set them yourself in the local machine:

export CLOUDSQL_CONNECTION_NAME=your-connection-name
export CLOUDSQL_USER=root
export CLOUDSQL_PASSWORD=xxxxx

Verify that they are set by doing e.g echo $CLOUDSQL_CONNECTION_NAME. When you deploy your app with gcloud app deploy, this is not necessary since GAE sets the whatever environment variables are specified in the app.yaml.

The proxy has to launched before trying to establish a connection following these steps:

Download the proxy with:

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy

Give it execution permission with:

chmod +x cloud_sql_proxy

Start the proxy replacing <INSTANCE_CONNECTION_NAME> with your Cloud SQL instance connection name:

./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306

You should see at the end something similar to this:

 2018/11/09 13:24:32 Rlimits for file descriptors set to {&{8500 1048576}} 
 2018/11/09 13:24:35 Listening on 127.0.0.1:3306 for my-project:cloud-sql-region:cloud-sql-name 
 2018/11/09 13:24:35 Ready for new connections

At this point, is when you can connect to the proxy running locally from the Google Cloud Shell instance, which in turn will connect you to the Cloud SQL instance.

Open another Cloud Shell session (or tab) and launch your code with python myapp.py. You will be connected to the proxy running locally. You can also test the connection by running mysql -h 127.0.0.1 --user=root -p.

user_91468200

I solved the connection refused problem by adding to the app.yaml the following:

beta_settings:
    cloud_sql_instances: "<CONNECTION_NAME>"
user_54593430
Related