读从，读写主设置 [英] Read slave, read-write master setup

问题描述

我有一个 Flask，SQLAlchemy webapp，它使用一个 mysql 服务器.我想扩展数据库设置以拥有一个只读从服务器，这样我就可以在继续写入主数据库服务器的同时在主从服务器之间分散读取.

I have a Flask, SQLAlchemy webapp which uses a single mysql server. I want to expand the database setup to have a read-only slave server such that I can spread the reads between both master and slave while continuing to write to the master db server.

我看了几个选项，我相信我不能用普通的 SQLAlchemy 做到这一点.相反，我计划在我的 web 应用程序中创建 2 个数据库句柄，一个用于主数据库服务器和从属数据库服务器.然后使用一个简单的随机值使用SELECT"的主/从数据库句柄.操作.

I have looked at few options and I believe I can't do this with plain SQLAlchemy. Instead I'm planning to create 2 database handles in my webapp, one each for master and slave db servers. Then using a simple random value use either the master/slave db handle for "SELECT" operations.

但是，我不确定这是否是使用 SQLAlchemy 的正确方法.关于如何解决这个问题的任何建议/提示?

However, I'm not sure if this is the right way to go with using SQLAlchemy. Any suggestion/tips on how to pull this off?

推荐答案

我在我的博客 http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ .基本上，您可以增强会话，以便它在逐个查询的基础上从 master 或 slave 中进行选择.这种方法的一个潜在问题是，如果您有一个调用六个查询的事务，您最终可能会在一个请求中使用两个从属......但我们只是试图模仿 Django 的功能:)

I have an example of how to do this on my blog at http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ . Basically you can enhance the Session so that it chooses from master or slave on a query-by-query basis. One potential glitch with that approach is that if you have one transaction that calls six queries, you might end up using both slaves in one request....but there we're just trying to imitate Django's feature :)

一种稍微不那么神奇的方法，它也更明确地建立了我使用的使用范围是视图可调用对象上的装饰器(无论它们在 Flask 中如何调用)，如下所示:

A slightly less magic approach that also establishes the scope of usage more explicitly I've used is a decorator on view callables (whatever they're called in Flask), like this:

@with_slave
def my_view(...):
   # ...

with_slave 会做这样的事情，假设你有一个 Session 并设置了一些引擎:

with_slave would do something like this, assuming you have a Session and some engines set up:

master = create_engine("some DB")
slave = create_engine("some other DB")
Session = scoped_session(sessionmaker(bind=master))

def with_slave(fn):
    def go(*arg, **kw):
        s = Session(bind=slave)
        return fn(*arg, **kw)
    return go

这个想法是调用 Session(bind=slave) 调用注册表来获取当前线程的实际 Session 对象，如果它不存在则创建它 - 但是因为我们传递一个参数，scoped_session 会断言我们在这里创建的 Session 绝对是全新的.

The idea is that calling Session(bind=slave) invokes the registry to get at the actual Session object for the current thread, creating it if it doesn't exist - however since we're passing an argument, scoped_session will assert that the Session we're making here is definitely brand new.

您将其指向所有后续 SQL 的从属".然后，当请求结束时，您将确保您的 Flask 应用程序正在调用 Session.remove() 以清除该线程的注册表.当注册表下次在同一个线程上使用时，它将是一个新的 Session 绑定回master".

You point it at the "slave" for all subsequent SQL. Then, when the request is over, you'd ensure that your Flask app is calling Session.remove() to clear out the registry for that thread. When the registry is next used on the same thread, it will be a new Session bound back to the "master".

或者一个变体，你想只为那个调用使用slave"，这是更安全的"，因为它将任何现有的绑定恢复到会话:

Or a variant, you want to use the "slave" just for that call, this is "safer" in that it restores any existing bind back to the Session:

def with_slave(fn):
    def go(*arg, **kw):
        s = Session()
        oldbind = s.bind
        s.bind = slave
        try:
            return fn(*arg, **kw)
        finally:
            s.bind = oldbind
    return go

对于这些装饰器中的每一个，您都可以颠倒事物，将会话绑定到从属"，装饰器将其放在主"上以进行写操作.如果在这种情况下你想要一个随机的从设备，如果 Flask 有某种请求开始"事件，你可以在那个时候设置它.

For each of these decorators you can reverse things, have the Session be bound to a "slave" where the decorator puts it on "master" for write operations. If you wanted a random slave in that case, if Flask had some kind of "request begin" event you could set it up at that point.

这篇关于读从，读写主设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！