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
[mysqld]
# add this line anywhere in the [mysqld] section
log-bin=mysql-bin
2. Set the server ids on master and slave in /etc/my.conf
#MASTER
[mysqld]
server-id=1
#SLAVE
[mysqld]
server-id=2
3. Grant the slave permission to pull the data from the master (replace XX.XX.XX.XX with your slave’s IP address)
mysql>grant replication slave on *.* to ‘replication’@XX.XX.XX.XX identified by ’slavepass’;
4. Perform a read-lock on the master
mysql> FLUSH TABLES WITH READ LOCK;
5. Record the file and position on the master. It should give you output like this. Record the file and position to use later
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 1467771 | | |
+——————+———-+————–+——————+
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.
And I can’t use MySQL master/slave because the data is not production worthy until the script fully completes. Instead of using Master/Slave, I push the binary files from database server 1 to database server 2 when the script completes. So each of the databases will be turned off at some point. Therefore, I need to use HAProxy to forward the requests to the working server when the other one is turned off. And when the product script is not running and binary files are not being pushed, I want both of them running in the cluster. So here is application flow: