I have been running a web app on 1 server since 2005 and we have seen a good bit of traffic. We are currently ranked 8200 according to quantcast.com with just over 200,000 visitors a month. Traffic is picking up and I expect it to almost double in Q4.  NewRelic shows all green right now with about 250ms response time which is fine.  But I am worried that if traffic doubles, the database may become a little stressed.  So I have decided to be pro-active and go ahead and add another server to the database layer.  Right now, I have Nginx in front of mongrel cluster (4 instances) and MySQL as the database.  All of this is running on the same server.  So my new setup will be:

Server 1:  Nginx > Mongrel Cluster > MySQL master

Server 2: MySQL slave

NOTE:  RailsEnvy has a really good video series on scaling your database (and many other great videos on other subjects), so you may want to check that out just to get an overview of the process.

In order to get this working, Rails will need to be able to perform writes only on master and reads on both master and slave.  But, lets talk about setting up the replication first.  Here is how you set it up:

1. Set binary logging on the master server.  Edit /etc/my.conf

2. Set the server ids on master and slave in /etc/my.conf

3. Grant the slave permission to pull the data from the master (replace XX.XX.XX.XX with your slave’s IP address)

Then you will need to restart your mysql server on the master server

on ubuntu you would do:

service mysql restart

4. Perform a read-lock on the master

5. Record the file and position on the master.  It should give you output like this.  Record the file and position to use later

6. Export data on master using mysql_dump

> mysqldump --all-databases --lock-all-tables >dump.db

7. Unlock the tables on master
mysql> UNLOCK TABLES;

8. FTP the tar file of the mysql snapshot to your slave box

9. Stop MySQL on the slave
/etc/init.d/mysqld stop

10. You need to start slave using --skip-slave option.  I changed the init.d script to do this.  Probably an easier way to do it, but whatever..  It was line 61 in /etc/init.d/mysqld.  Just make a backup of that file, so you can replace it after you start mysql
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure.
/usr/bin/mysqld_safe   --skip-slave --datadir="$datadir" --socket="$socketfile" \
--log-error="$errlogfile" --pid-file="$mypidfile" \
>/dev/null 2>&1 &

11. Start mysql on slave.  Then change /etc/init.d/mysql back to the original (without --skip-slave option)
> /etc/init.d/mysqld start

12. Import the data into the slave
> mysql < dbdump.db

13. Configure master on the slave box using the values for your host,user, and password.  Fill in the last 2 with the values you recorded from step 5
mysql> CHANGE MASTER TO
->     MASTER_HOST='your_host',
->     MASTER_USER='replication',
->     MASTER_PASSWORD='user_password',
->     MASTER_LOG_FILE='mysql-bin.000001',
->     MASTER_LOG_POS=1467771;

14. Start the slave replication on the slave server
mysql> START SLAVE;

Now your slave should be getting updates from the master.  Go ahead and change some stuff on the master and see if the slave replicates.  You can also view the status of the slave by using this mysql command:
mysql> SHOW SLAVE STATUS;
CONFIGURING RAILS TO WORK WITH MASTER/SLAVE

The next step in the process is to get rails to communicate with the master/slave setup.  The easiest way to do this is to use the machoism plugin.  You can get it at github.  There are a few different branches of this, but I am using the one from technoweenie (I had problems getting the mislav version to work)

http://github.com/technoweenie/masochism/tree/master

It is easy to install.  Just go to your rails app root directory and issue the following command
> script/plugin install http://github.com/technoweenie/masochism.git

Next you need to configure your database.yml to for masochism.  One interesting thing I found out that I was not aware of is that you can set the login as a variable, so you can reuse it later in the config file.  Here is my config
login: &login
adapter: mysql
database: yourapp_production
username: yourusername
password: yourpassword# default configuration (slave)
production:
<<: *login
host: XX.XX.XX.XX

# setup for masochism (master)
master_database:
<<: *login
host: XX.XX.XX.XX

Then add the following lines to your environment.rb file

# in environment.rb
config.after_initialize do
  if Rails.env.production?
    ActiveReload::ConnectionProxy::setup!
  end
end

Restart your app server and you should be good to go.
I thought I was until I figured out that there was a conflict between masochism and another plugin I use called use_db.  Masochism seems to be overriding the database config for use_db, so it is restricting me to only the master/slave databases.  I will post when I figure out a solution to this.

Tags: master slave, mysql replication, ruby on rails