Mysql Replication Adapter

So you want to use multiple slave databases with Rails? How does this syntax strike you:

A few months ago, there was quite a bit of noise in the Rails community about Rails’ inability to easily make use of multiple databases. Dr Nic quickly responded with a solution. Everyone rejoiced in the wonder of the community.

However, the solution lacks the syntactic sugar that Rails does so well. It also has the load balancing logic above the models in user code, rather than below them, where it seems to belong.

Enter Mysql Replication Adapter.

This library contains a Rails database adapter that helps your Rails app talk to a single write master and multiple read-only slaves in a very elegant way. The configuration is also more environment-oriented than connection-oriented, so it hides the concept of multiple connections from the programmer altogether. All you have to do is say that you want a #find, #find_by_sql, or aggregate function spread over slave databases and the adapter does it for you.

Similar to Dr Nic’s approach, you still have to explicitly say when you want your queries to be sent to a slave database. We opted for this approach over a silent automatic balancing option because it reduces your ability to accidently really bork something up by sending it to a database that is just slightly behind in replication.

Here at Rapleaf, we’re using this adapter in a lot of our projects, because it allows us to leverage the additional read bandwidth of our two slave databases.

Installation & Use
(This all assumes that you have Mysql master-slave replication set up in advance.)

First, install it via the gem:

Next, pop open environment.rb and add:

to the top before the initialize loop.

(Note: It *has* to go before the initializer loop, or it will load too late.)

Next, open up database.yml and configure it like so:

development:
adapter: mysql_replication
database: yourdb
username: root
password:
host: localhost
port: 3306
slaves:
- host: slave1
port: 3306
username: readonlyuser
password:
- host: slave2
port: 3306
username: readonlyuser
password:

Finally, to actually have Rails use one of the slave databases for a given find, use syntax like this:

That’s all there is to it. If you’ve specified slave databases, when you make the above call, a random slave will be chosen and the query executed against that database. If you try and do a write operation (like find_or_create_by_*), the adapter will throw an error, so don’t worry, you won’t be able to corrupt your database.

One nice feature is that if you’re logging queries, whether because you’re in development mode or otherwise, the log will tell you which slave database the query got sent to. This could be helpful for debugging purposes.

There are a few other ways to make use of slave databases, but I’ll leave that for another post. You can also check out the readme in the gem directory for more details if you’re interested.