Amazon Web Services RDS的读/写URI [英] Read/Read-Write URIs for Amazon Web Services RDS

问题描述

我正在使用HAProxy来为我的应用(使用Flask编写)实现AWS RDS(MySQL)负载平衡.

I am using HAProxy to for AWS RDS (MySQL) load balancing for my app, that is written using Flask.

HAProxy.cfg文件对数据库具有以下配置

The HAProxy.cfg file has following configuration for the DB

listen mysql
bind 127.0.0.1:3306
mode tcp
balance roundrobin
option mysql-check user haproxy_check
option log-health-checks
server db01 MASTER_DATABSE_ENDPOINT.rds.amazonaws.com
server db02 READ_REPLICA_ENDPOINT.rds.amazonaws.com

我正在使用SQLALCHEMY，它的URI是:

I am using SQLALCHEMY and it's URI is:

SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://USER:PASSWORD@127.0.0.1:3306/DATABASE'

但是当我在测试环境中运行API时，仅从DB中读取内容的API的执行情况就很好，但是向DB写东西的API却给了我以下错误:

but when I am running an API in my test environment, the APIs that are just reading stuff from DB are executing just fine but the APIs that are writing something to DB are giving me errors mostly that:

(pymysql.err.InternalError) (1290, 'The MySQL server is running with the --read-only option so it cannot execute this statement')

在这种情况下，我认为我现在需要使用2个URL，一个用于read-only操作，另一个用于writes.

I think I need to use 2 URLs now in this scenario, one for read-only operation and one for writes.

这如何与Flask和带有HAProxy的SQLALCHEMY一起使用? 如何告诉我的APP使用一个URL进行write操作，使用另一个HAProxy URL进行read-only操作?

How does this work with Flask and SQLALCHEMY with HAProxy? How do I tell my APP to use one URL for write operations and other HAProxy URL to read-only operations?

我从SQLAlchemy的文档中找不到任何帮助.

I didn't find any help from the documentation of SQLAlchemy.

推荐答案

绑定

Flask-SQLAlchemy可以轻松连接到多个数据库.实现 它预先配置了SQLAlchemy以支持多个绑定".

Flask-SQLAlchemy can easily connect to multiple databases. To achieve that it preconfigures SQLAlchemy to support multiple "binds".

SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://USER:PASSWORD@DEFAULT:3306/DATABASE'
SQLALCHEMY_BINDS = {
    'master': 'mysql+pymysql://USER:PASSWORD@MASTER_DATABSE_ENDPOINT:3306/DATABASE',
    'read': 'mysql+pymysql://USER:PASSWORD@READ_REPLICA_ENDPOINT:3306/DATABASE'
}

引用绑定:

db.create_all(bind='read') # from read only
db.create_all(bind='master') # from master

这篇关于Amazon Web Services RDS的读/写URI的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！