问如何从App Engine连接到云SQL MySQL数据库？
EN

Stack Overflow用户

提问于 2018-07-13 23:22:26

回答 2查看 6.4K关注 0票数 1

我目前正在App Engine Flexible Environment上安装一个名为Mautic的开源营销软件。但是，我在使用MySQLi将App Engine连接到Cloud SQL MySQL数据库时遇到了问题。Mautic是一个构建在Symfony之上的PHP应用程序。我尝试使用/cloudsql/<instance_connection_name>作为主机，但不起作用。有没有人有在App Engine上部署Mautic的经验，或者知道如何解决这个问题？

php

mysql

google-app-engine

google-cloud-sql

mautic

回答 2

Stack Overflow用户

发布于 2020-02-11 00:54:38

使用TCP和UNIX域套接字2020将应用程序引擎(FLEX和标准)连接到云SQL

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

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

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.设置计算项目信息元数据：

 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.启用Service Networking Api：

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

6.创建2个云sql实例，一个内网ip，一个外网ip- https://cloud.google.com/sql/docs/mysql/create-instance

6.有外部ip的云Sql实例：

#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

6.b内网ip云Sql实例：

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实例，我们将使用无服务器访问和TCP从App Engine Standard连接到数据库-ipinternal，使用unix域套接字从App Engine Standard连接到数据库-外部，使用TCP从App Engine Flex连接到数据库-ipinternal，使用unix域套接字从App Engine Flex连接到数据库-外部。

7.开启云SQL管理API

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

8.目前，App Engine标准环境不支持使用TCP连接到Cloud SQL实例。您的代码不应尝试使用IP地址(如127.0.0.1或172.17.0.1)访问实例，除非您已经配置了无服务器私有网络Access.So，让我们来配置无服务器私有网络访问。

8.a确保您的项目开启了无Serverless VPC访问接口：

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.a使用服务账号对您的云SQL连接进行授权。此服务帐户必须具有正确的IAM权限才能成功连接。除非另有配置，否则默认服务帐户的格式为service-PROJECT_NUMBER@gae-api-prod.google.com.iam.gserviceaccount.com.确保您的服务的服务帐户具有以下IAM角色: Cloud SQL Client，并且为了从App Engine Standard连接到内部ip上的Cloud Sql，我们还需要角色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标准连接到云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'

成功！

票数 6

Stack Overflow用户

发布于 2018-07-15 06:37:23

TCP提供了两种不同的连接方式:通过unix套接字或AppEngine端口。我看不出从Mautic连接到MySQL有什么特别之处，所以请确保您的连接设置与您的app.yaml一致。

通过Unix套接字连接

您的app.yaml应包含以下内容：

beta_settings:
    cloud_sql_instances: "<CONNECTION_NAME>"

您的连接字符串将如下所示：mysql:dbname=DATABASE;unix_socket=/cloudsql/CONNECTION_NAME

通过TCP端口连接

您的app.yaml应该如下所示：

beta_settings:
    cloud_sql_instances: "<CONNECTION_NAME>=tcp:3306"

您的连接字符串将如下所示："mysql:host=127.0.0.1;port=3306;dbname=DATABASE;"

您可以在GAE-Flexible/CloudSQL documentation中找到更多信息和示例。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/51328359

复制

相似问题

问如何从App Engine连接到云SQL MySQL数据库？
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从App Engine连接到云SQL MySQL数据库？EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从App Engine连接到云SQL MySQL数据库？
EN