您的位置: 首页  >  技术问答  >  无法将App Engine连接到mysql实例的Cloud sql

无法将App Engine连接到mysql实例的Cloud sql

分类: 技术问答 • 2022-02-15 10:00:14

无法将App Engine连接到mysql实例的Cloud sql

问题描述：

sqlalchemy.exc.OperationalError:(pymysql.err.OperationalError)(2003，无法连接到'localhost'上的MySQL服务器([Errno 2]没有这样的文件或目录)"))(此错误的背景: http://sqlalche.me/e/e3q8 )

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' ([Errno 2] No such file or directory)") (Background on this error at: http://sqlalche.me/e/e3q8)

很长时间以来我一直被这个错误所困扰，我正在尝试将我的App Engine python代码连接到mysql实例的云sql上.这是我第一次使用Google Cloud.以下是我编写的代码 1.在app.yaml中

Its been long time i am stuck at this error , I am trying to connect my app engine python code to a cloud sql for mysql instance. This is the first time i am working with google cloud.below is the code i have written 1. in app.yaml

runtime:
    python37
vpc_access_connector:
    name: "projects/projectnameandcode/locations/location name/connectors/connector name"

2.requirements.txt

2.requirements.txt

sqlalchemy
pymysql

  1. 在main.py

  1. in main.py

导入pymysql

db = sqlalchemy.create_engine(

db = sqlalchemy.create_engine(

sqlalchemy.engine.url.URL(
    drivername="mysql+pymysql",
    username="username",
    password=password,
    database="databasename",
    query={"unix_socket": "/cloudsql/{}".format("instance name")},
),

) a = db.connect()

) a= db.connect()

为什么我要面对这个问题?我的Iam角色是所有者或管理员.

why am i facing this problem ? My Iam roles are owner or admin.

答

App Engine标准环境不支持连接到云 使用TCP的SQL实例.您的代码不应尝试访问 使用IP地址(例如127.0.0.1或172.17.0.1)的实例，除非 您已经配置了无服务器VPC访问.

App Engine standard enviroments do not support connecting to the Cloud SQL instance using TCP. Your code should not try to access the instance using an IP address (such as 127.0.0.1 or 172.17.0.1) unless you have configured Serverless VPC Access.

根据您的问题，我了解到您正在使用vpc_access_connector.因此，我假设您配置了无服务器VPC访问.

From your question I understand that you are using vpc_access_connector. Therefore I assume that you configured Serverless VPC Access.

main.py中使用的代码用于使用unix域套接字而不是TCP连接到Cloud SQL实例.

The code used in main.py is for connecting to Cloud SQL instance's using unix domain socket and not TCP.

1.创建一个新项目

gcloud projects create con-ae-to-sql
gcloud config set project con-ae-to-sql
gcloud projects describe con-ae-to-sql

2.为您的项目启用帐单: https://cloud .google.com/billing/docs/how-to/modify-project

2.Enable billing on you project: https://cloud.google.com/billing/docs/how-to/modify-project

3.运行以下gcloud命令以启用App Engine并创建关联的应用程序资源

3.Run the following gcloud command to enable App Engine and create the associated application resources

gcloud app create -region europe-west2
gcloud app describe
#Remember the location of you App Engine aplication, because we will create all our resources on the same region

4.设置计算项目信息元数据:

4.Set the compute project-info metadata:

 gcloud compute project-info describe --project con-ae-to-sql
 #Enable the Api, and you can check that default-region,google-compute-default-zone are not set. Set the metadata.
 gcloud compute project-info add-metadata --metadata google-compute-default-region=europe-west2,google-compute-default-zone=europe-west2-b

5.启用服务网络API:

5.Enable Service Networking Api:

gcloud services list --available
gcloud services enable servicenetworking.googleapis.com

6.创建2个云sql实例，(一个使用internall ip，一个使用公共ip)- https://cloud.google.com/sql/docs/mysql/create-instance :

6.Create 2 cloud sql instances, (one with internall ip and one with public ip)- https://cloud.google.com/sql/docs/mysql/create-instance:

6.a具有外部ip的Cloud Sql实例:

6.a Cloud Sql Instance with external ip:

#Create the sql instance in the same region as App Engine Application
gcloud --project=con-ae-to-sql beta sql instances create database-external --region=europe-west2
#Set the password for the "root@%" MySQL user:
gcloud sql users set-password root --host=% --instance database-external --password root 
#Create a user
gcloud sql users create user_name --host=% --instance=database-external  --password=user_password
#Create a database
gcloud sql databases create user_database --instance=database-external
gcloud sql databases list --instance=database-external

具有内部ip的6.b Cloud Sql实例:

6.b Cloud Sql Instance with internal ip:

i.#Create a private connection to Google so that the VM instances in the default VPC network can use private services access to reach Google services that support it.

gcloud compute addresses create google-managed-services-my-network     --global  --purpose=VPC_PEERING --prefix-length=16  --description="peering range for Google"  --network=default --project=con-ae-to-sql
gcloud services vpc-peerings connect --service=servicenetworking.googleapis.com --ranges=google-managed-services-my-network  --network=default  --project=con-ae-to-sql
#Check whether the operation was successful.
gcloud services vpc-peerings operations describe     --name=operations/pssn.dacc3510-ebc6-40bd-a07b-8c79c1f4fa9a
#Listing private connections
gcloud services vpc-peerings list --network=default --project=con-ae-to-sql
 
ii.Create the instance:

gcloud --project=con-ae-to-sql beta sql instances create database-ipinternal --network=default --no-assign-ip  --region=europe-west2
#Set the password for the "root@%" MySQL user:
gcloud sql users set-password root --host=% --instance database-ipinternal --password root
#Create a user
gcloud sql users create user_name --host=% --instance=database-ipinternal  --password=user_password
#Create a database
gcloud sql databases create user_database --instance=database-ipinternal
gcloud sql databases list --instance=database-ipinternal 


gcloud sql instances list
gcloud sql instances describe database-external
gcloud sql instances describe database-ipinternal
#Remember the instances connectionName

好的，所以我们有两个mysql实例，我们将使用Serverless Access和TCP从App Engine Standard连接到database-ipinternal，使用unix域套接字从App Engine Standard连接到database-external，从App Engine Flex到database-ipinternal使用TCP，从App Engine Flex到使用unix域套接字的外部数据库.

OK, so we have two mysql instances, we will connect from App Engine Standard to database-ipinternal using Serverless Access and TCP, from App Engine Standard to database-external using unix domain socket, from App Engine Flex to database-ipinternal using TCP, and from App Engine Flex to database-external using unix domain socket.

7.启用Cloud SQL Admin API

7.Enable the Cloud SQL Admin API

gcloud services list --available
gcloud services enable sqladmin.googleapis.com

8.目前，App Engine标准环境不支持使用TCP连接到Cloud SQL实例.除非您已配置无服务器VPC访问，否则您的代码不应尝试使用IP地址(例如127.0.0.1或172.17.0.1)访问实例.因此，让我们配置无服务器VPC访问.

8.At this time App Engine standard enviroments do not support connecting to the Cloud SQL instance using TCP. Your code should not try to access the instance using an IP address (such as 127.0.0.1 or 172.17.0.1) unless you have configured Serverless VPC Access.So let's configure Serverless VPC Access.

8.a确保为您的项目启用了无服务器VPC访问API:

8.a Ensure the Serverless VPC Access API is enabled for your project:

gcloud services enable vpcaccess.googleapis.com

8.b创建连接器:

gcloud compute networks vpc-access connectors create serverless-connector --network default --region europe-west2 --range 10.10.0.0/28
#Verify that your connector is in the READY state before using it
gcloud compute networks vpc-access connectors describe serverless-connector --region europe-west2

9.App Engine使用服务帐户来授权您与Cloud SQL的连接.此服务帐户必须具有正确的IAM权限才能成功连接.除非另行配置，否则默认服务帐户的格式为service-PROJECT_NUMBER@gae-api-prod.google.com.iam.gserviceaccount.com.确保您服务的服务帐户具有以下IAM角色:Cloud SQL客户端，并且要在内部ip上从App Engine Standard连接到Cloud Sql，我们还需要角色Compute Network User.

9.App Engine uses a service account to authorize your connections to Cloud SQL. This service account must have the correct IAM permissions to successfully connect. Unless otherwise configured, the default service account is in the format service-PROJECT_NUMBER@gae-api-prod.google.com.iam.gserviceaccount.com. Ensure that the service account for your service has the following IAM roles: Cloud SQL Client, and for connecting from App Engine Standard to Cloud Sql on internal ip we need also the role Compute Network User.

gcloud iam service-accounts list
gcloud projects add-iam-policy-binding con-ae-to-sql --member serviceAccount:con-ae-to-sql@appspot.gserviceaccount.com --role roles/cloudsql.client
gcloud projects add-iam-policy-binding con-ae-to-sql --member serviceAccount:con-ae-to-sql@appspot.gserviceaccount.com --role roles/compute.networkUser

现在我已经配置好了

1.使用Tcp和Unix Domanin套接字从App Engine Standard连接到Cloud Sql 

cd app-engine-standard/
ls
#app.yaml  main.py requirements.txt

cat requirements.txt
Flask==1.1.1
sqlalchemy
pymysql
uwsgi==2.0.18

cat app.yaml
runtime: python37
entrypoint: uwsgi --http-socket :8080 --wsgi-file main.py --callable app --master --processes 1 --threads 2
vpc_access_connector:
    name: "projects/con-ae-to-sql/locations/europe-west2/connectors/serverless-connector" 



cat main.py

from flask import Flask
import pymysql
from sqlalchemy import create_engine

# If `entrypoint` is not defined in app.yaml, App Engine will look for an app
# called `app` in `main.py`.
app = Flask(__name__)

@app.route('/')
def hello():
    engine_tcp = create_engine('mysql+pymysql://user_name:user_password@internal-ip-of-database-ipinternal:3306')
    existing_databases_tcp = engine_tcp.execute("SHOW DATABASES;")
    con_tcp = "Connecting from APP Engine Standard to Cloud SQL using TCP: databases => " + str([d[0] for d in existing_databases_tcp]).strip('[]') + "\n"
    engine_unix_socket = create_engine('mysql+pymysql://user_name:user_password@/user_database?unix_socket=/cloudsql/con-ae-to-sql:europe-west2:database-external')
    existing_databases_unix_socket = engine_unix_socket.execute("SHOW DATABASES;")
    con_unix_socket = "Connecting from APP Engine Standard to Cloud SQL using Unix Sockets: tables in sys database:  => " + str([d[0] for d in existing_databases_unix_socket]).strip('[]') + "\n"
    return con_tcp + con_unix_socket



gcloud app deploy -q
gcloud app browse
#Go to https://con-ae-to-sql.appspot.com
#Connecting from APP Engine Standard to Cloud SQL using TCP: databases => 'information_schema', 'user_database', 'mysql', 'performance_schema', 'sys' Connecting from APP Engine Standard to Cloud SQL using Unix Sockets: tables in sys database: => 'information_schema', 'user_database', 'mysql', 'performance_schema', 'sys'

成功！

2.使用Tcp和Unix domanin套接字从App Engine Flex连接到Cloud Sql 

cd app-engine-flex/
ls
#app.yaml  main.py requirements.txt

cat requirements.txt
Flask==1.1.1
gunicorn==19.9.0
sqlalchemy
pymysql

cat app.yaml
runtime: python
env: flex
entrypoint: gunicorn -b :$PORT main:app
runtime_config:
  python_version: 3
#Using TCP and unix sockets domain
beta_settings:
 cloud_sql_instances: con-ae-to-sql:europe-west2:database-ipinternal=tcp:3306,con-ae-to-sql:europe-west2:database-external
manual_scaling:
  instances: 1
resources:
  cpu: 1
  memory_gb: 0.5
  disk_size_gb: 10


cat main.py

from flask import Flask
import pymysql
from sqlalchemy import create_engine

app = Flask(__name__)

@app.route('/')
def hello():
    engine_tcp = create_engine('mysql+pymysql://user_name:user_password@internal-ip-of-database-ipinternal:3306')
    existing_databases_tcp = engine_tcp.execute("SHOW DATABASES;")
    con_tcp = "Connecting from APP Engine Flex to Cloud SQL using TCP: databases => " + str([d[0] for d in existing_databases_tcp]).strip('[]') + "\n"

    engine_unix_socket = create_engine('mysql+pymysql://user_name:user_password@/user_database?unix_socket=/cloudsql/con-ae-to-sql:europe-west2:database-external')
    existing_databases_unix_socket = engine_unix_socket.execute("SHOW DATABASES;")
    con_unix_socket = "Connecting from APP Engine Flex to Cloud SQL using Unix Sockets: tables in sys database:  => " + str([d[0] for d in existing_databases_unix_socket]).strip('[]') + "\n"
    return con_tcp + con_unix_socket


gcloud app deploy -q
gcloud app browse
#Go to https://con-ae-to-sql.appspot.com
#Connecting from APP Engine Flex to Cloud SQL using TCP: databases => 'information_schema', 'marian', 'mysql', 'performance_schema', 'sys' Connecting from APP Engine Flex to Cloud SQL using Unix Sockets: tables in sys database: => 'information_schema', 'marian', 'mysql', 'performance_schema', 'sys'

成功！

相关推荐