Read/Read-Write URIs for Amazon Web Services RDS

Question

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

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

I am using SQLALCHEMY and it's URI is:

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

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

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

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?

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

Answer 1

Binds

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'
}

Referring to Binds:

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