在标准环境下的GCP:MySQL数据库连接被拒绝 [英] GCP at the standard environment: MySQL db connection refused

问题描述

我的目标是在GCP上运行一个可用的Python 2.7项目(MySQL + MS Word文件). 我意识到我需要

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-应用将在其中运行(缩放等).
2. 用作MySQL数据库的Cloud SQL. 为此，我遵循了云SQL for MySQL tut 和
   • 使用root用户创建Cloud SQL实例.
   • App Engine应用程序和Cloud SQL实例都在同一个项目中.

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.

成功创建了第二代SQL实例，并设置了 root 用户.

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

我如何运行或部署

我使用Cloud Shell测试应用程序-dev_appserver.py $PWD，并从Cloud Shell-gcloud app deploy部署应用程序. 它在appspot.com上有效，直到我尝试在其中使用MySQL连接为止.

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连接

MySQL连接代码来自

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()

变量在 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

当应用通过dev_appserver.py $PWD在测试模式下运行时，我选择使用MySQL连接时出现错误:

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代理

我已下载并运行Win-64的云代理( https://dl .google.com/cloudsql/cloud_sql_proxy_x64.exe )，但问题仍然存在...似乎代理后台应用仅用于从本地计算机连接到Cloud SQL.

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.

您无需使用代理或配置SSL即可从App Engine标准或灵活环境连接到Cloud SQL. (源)

为什么拒绝连接?

我应该使用第一代Cloud sql实例来简化与App Engine的连接吗?

我在Cloud Console上编辑代码，到目前为止，Cloud Console运行良好.

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

我已成功通过Cloud Shell连接到sql实例:

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)]>

更新3

对类似问题的评论 Cloud SQL实例和 App Engine应用应位于的区域，即在同一区域.

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实例: us-central1-a
• App Engine应用:我们中心

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

这些是一个地区吗?-原来是一个地区的这些景点.

我本来可以打开数据库连接的:

I could have figured out to open db connection:

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

但这似乎只有在我用同一项目( coral-heuristic-215610 )打开另一个Cloud Shell实例之后才发生.在那种情况下，我已经开始连接到SQL实例，并且成功完成了:

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:

我猜第一个云外壳实例开始连接到数据库，因为第二个实例已将我的IP列入白名单，不是吗?

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?

推荐答案

我通过向app.yaml中添加以下内容来解决了连接被拒绝的问题:

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

beta_settings:
    cloud_sql_instances: "<CONNECTION_NAME>"

这篇关于在标准环境下的GCP:MySQL数据库连接被拒绝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！