Database Replication
Why replication?
Rails Machine servers have the benefit of nightly snapshots in case of data corruption or hardware failure. But what about the time between snapshots? If you want the ability to recover all of the data from your database- right up to the second before catastrophe stuck- then you’ll need to set up database replication. The simplest type of replication to configure is master-slave replication. Should anything happens to the master server, you can promote the slave server to be the new master or dump the slave database and import it into a new server. Setting up database replication can seem like a daunting task. In this article, we’re going to offer a detailed, mostly-painless description of configuring your servers for replication.
Getting the tools
Apart from MySQL itself, the only tool we’ll be using is XtraBackup from Percona. Visit the Xtra Backup downloads page to find the latest version. If you cannot find precompiled binaries, download the source package and compile by following the build instructions. If you are using 32bit CentOS, you can download binary files for version 0.7 here. These are known to work for CentOS 4, but may work on new versions as well. You will need to have Xtra Backup installed on both servers.
Replication overview
To help you keep the overall process in mind as we walk through the steps, here is a high-level overview of what we’ll be doing. For our example, we’ll be assuming that there is a database server in use called db1 with IP address 10.0.0.20. This will be the master in our replication scheme. We want to configure a new slave server called db2 with IP address 10.0.0.21.
- Verify the MySQL configuration on db1
- Obtain a consistent, ‘clean’ snapshot of the data on db1
- Obtain metadata about the snapshot- the log file and position
- Allow access to data on db1 to db2
- Import the data on to db2
- Give db2 the connection details
Replicate!
Check settings
Open your MySQL configuration on db1. In the [mysqld] section, make sure you have a server-id and binary log options set.
server-id=1 log-bin=db1-bin log-bin-index=db1-bin
The names of the binary log files are not important, but it’s a good idea to include the hostname. When you’re configuring MySQL on db2, you would use a server-id of 2.
If you make any changes to the database configuration, be sure to restart MySQL:
sudo service mysqld restart # the service is called 'mysql' on debian/ubuntu
Snapshot the database
Important: This backup process performs a ‘hot’ backup. This mean that, most likely, your application will be able to continue using the database with no side effects. However, the backup tool does need to identify the log file and position, and for this it will very briefly read-lock the database tables at the end of the process. If you are concerned that this may impact your application, you should wait for a period of low traffic or seek outside help.
On db1, run the following commands.
mkdir /tmp/db1 cd /tmp/db1 innobackupex --user=root --password=<pass> .
This may take a while to complete. When it does complete, you should see a confirmation that the process ended successfully that looks something like this:
innobackupex: Backup created in directory '/tmp/db1/2009-06-09_15-00-09' innobackupex: MySQL binlog position: filename 'db1-bin.000001', position 3456 090602 15:21:11 innobackupex: innobackup completed OK!
Note the binlog filename (db1-bin.000001) and position (3456). You will need these later to tell db2 where to restart the replication process.
Grant access to db2
Now we need to log in to MySQL on db1 and grant replication privileges to a user from db2. Run the following commands on db1, setting your own username and password:
# mysql -uroot -p<password> mysql> grant replication slave on *.* to 'repl'@'10.0.0.21' identified by 'sekrit_password'; mysql> exit;
Import data snapshot on db2
There are any number of ways to get the data copied from db1 to db2 but for this example, we’ll use scp.
On db1:
scp -r /tmp/db1 user@10.0.0.21:
This will copy the db1 backup to the home directory of your user on db2.
Now on db2, make sure the database is stopped, move aside any existing MySQL data, and replace it with the export from db1. You also need to apply the logs that Xtra Backup created.
sudo su - # become root service mysqld stop # the service is called 'mysql' on debian/ubuntu mv /var/lib/mysql /var/lib/mysql.old mv /home/user/db1 /var/lib/mysql chown -R mysql.mysql /var/lib/mysql cd /var/lib/mysql innobackupex --apply-log . service mysqld start
Check your MySQL log files to verify that the database has started normally.
Connect to db1 from db2
The only task remaining is to tell db2 how to connect to db1 and replicate changes to the database as they happen. Run the following on db2, replacing the filename and position with the values you noted from the db1 snapshot:
mysql -uroot -p<password> mysql> change master to master_host = '10.0.0.20', master_user = 'repl', master_password = 'sekrit_password', master_log_file = 'db1-bin.000001', master_log_pos = 3456; mysql> start slave; mysql> show slave status\G; # the \G changes the format for better readability
The very last line of the slave status report should show the seconds_behind_master. On subsequent runs, this number should decrease.
Troubleshooting: If the seconds_behind_master is NULL- and remains that way after running ‘show slave status\G;’ a few times- then you may have some debugging to do. Look up higher in the report and there should be details. Make sure you used the correct replication username, password, log file and position. Also, make sure there’s not a firewall blocking your connection.
Success!
Once seconds_behind_master is 0, your slave database is fully caught up. Congratulations! You have increased your resiliency against disaster and given everyone involved a little peace of mind. One last bit of advice: this is a read-only slave, you shouldn’t be doing any writes on db2. If you do, you’ll likely break replication and there may not be an easy way to get it started without going through all of this again.