MySQL Master-Slave Replication


This article is about setting up MySQL Master-Slave database replication between two Cloud Servers. Master-Slave data replication allows for replicated data to be copied to multiple computers for backup and analysis by multiple parties.  Needed changes identified by a group member must to be submitted to the designated "master" of the node. This differs from Master-Master replication in which data can be updated by any authorized contributor of the group.

The operating system we are going to use for this example is CentOS 5.5, built from a Rackspace Cloud Server base image.

Contents

Setup Outline:

  • For the purpose of this exercise, we will have two Cloud Servers, named db01 and db02. Cloud Servers have two IP addresses (one public, one private). We will be configuring replication over the private IP interface so that we do not incur any bandwidth charges. For the duration of the exercise db01 is considered master MySQL server (running in read-write mode), and db02 is considered the slave (running in read-only mode).
  • If you already have a MySQL database running on the master node, a dump and restore into the slave would be required into the slave node before configuring replication between them. We will be using the mysqldump command to dump a database into a file, transferring it and restoring it to the slave. Once the necessary configuration has been carried out, replication will be in effect from that point onwards.

Creating the Cloud Servers:

  • First, you will need to create two Linux Cloud Servers, using the Centos 5.5 base image. Log into your Rackspace Cloud Control Panel, locate the Hosting tab, and click the “Add Server” button and select the Centos 5.5 base image. Choose the RAM configuration appropriate for your database requirements and name the servers accordingly so that you can easily identify them during the setup. The commands outlined in the following sections need to be executed by a privileged (root, sudo group) user. Any strings/values specified in brackets should be replaced with data specific to your setup.

Installing MySQL:

You need to install the mysql-server package on both CentOS Cloud Servers.

  • Before installing MySQL, confirm that the package database is up to date by running the following command:
 
#yum update
  • Now install MySQL and enable it to run at boot automatically:
 
 #yum install mysql-server
#chkconfig mysqld on
  • Start the mysqld service:
 
#service mysqld start
  • Once your mysqld service has been started, set your mysql server root password with following commands:
 
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h web01 password 'new-password'
  • Alternatively, you can run the secure installation script packaged with the MySQL installation:
 
# /usr/bin/mysql_secure_installation
Enter current password for root (enter for none):
...
Set root password? [Y/n] Y
...
Remove anonymous users? [Y/n] Y
...
Disallow root login remotely? [Y/n] Y
...
Remove test database and access to it? [Y/n] Y
...
Reload privilege tables now? [Y/n] Y
  • Finally, we need to permit connections on port 3306 (mysqld default port) and save the updated firewall configuration.
  • Add a TCP port 3306 rule with an insert at the last line number in the RH-Firewall-1-INPUT chain (in this case, line 10):
 
# iptables -I RH-Firewall-1-INPUT 10 -p tcp --dport 3306 -j ACCEPT

Save the firewall configuration:

 
# service iptables save
  • We are now ready to make the relevant configuration changes to enable replication.

Configuring replication:

  • A MySQL user is required on the master server (db01) to be used for replication. Run the following commands to set up the MySQL user, taking care to update the entries in brackets with strings/values you wish you use with your setup.
 
# mysql -u root -p
mysql> grant replication slave on *.* TO [replication_username]@’[private IP of db02]’ identified by '[some password]';
mysql> flush privileges;
mysql> quit
  • Edit the /etc/my.cnf file and add the following entries:
 
bind-address = 0.0.0.0
server-id = 1
log-bin = mysql-bin
binlog-ignore-db = “mysql”
  • Once you have finished updating the /etc/my.cnf file, we can restart the MySQL service.
 
#service mysqld restart
  • Before we can start replication, we need to make sure the data on each server (master and slave) is the same, so we need to dump the data from the master (db01) server and add it to the slave (db02) server.
  • First, we need to make sure that nothing can write to the master database while we obtain a database dump. We will also note the filename and position of the binary log as we will need these values to complete the replication configuration on db02.
 
# mysql -u root –p
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
 
+------------------+--------------------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+--------------------------+------------------+
| mysql-bin.000010 |        10 |              | mysql            |
+------------------+--------------------------+------------------+
1 row in set (0.00 sec)
  • Perform a database dump using mysqldump as follows (list all the databases barring mysql and information_schema):
 
# mysqldump -u root -p --databases [database-1] [database-2] ...  > /root/db_dump.sql
  • Once the dump has completed, we can lift the read lock from the master (db01):
 
# mysql -u root –p
mysql> UNLOCK TABLES;
  • Copy the database dump file to the slave server so it can be restored. You can use the scp command to accomplish this:
 
scp /root/db_dump.sql [private-IP-of-db02]:/root/
  • Let’s move to db02 now:

Edit the /etc/mysql/my.cnf file and add the following entries:

 
bind-address = 0.0.0.0
server-id = 2
master-host =  [private-IP-of-db01]
master-user = [replication-username]
master-password = [replication-password]
master-connect-retry = 60
  • Import the db_dump.sql file we copied over earlier and restart the MySQL service.
 
# mysql –u root –p < /root/db_dump.sql
# service mysqld restart
  • Time to complete the slave replication steps:
 
# mysql -u root –p
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='[private-IP-of-db01]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master-status]',
MASTER_LOG_POS=[log-position-listed–on-master-status];
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
  • The Slave_IO_State field should show "Waiting for master to send event". If it shows "Connecting to Master" please check your MySQL log file. By default it is /var/log/mysqld.log but it may be configured differently on your system. As always /etc/my.cnf will define the location of your log file.

Testing scenarios:

  • For the purpose of testing our replication setup, we can create a new database (testing) and associated table (users) on db01, inserting data to confirm that the changes are mirrored on db02. Here is an example:
 
# mysql -u root -p
mysql> create database testing;
mysql> use testing
mysql> create table users(id int not null auto_increment, primary key(id), username varchar(30) not null);
mysql> insert into users (username) values ('foo');
mysql> insert into users (username) values ('bar');
mysql> exit
  • The changes should be visible on db02 immediately.


Was this content helpful?




© 2011-2013 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License


See license specifics and DISCLAIMER