How-to: MySQL Master/Slave Replication

Since MySQL 3.23 this very popular RDBMS has replication support. It’s very easy to setup and can be useful for various conditions including:
– Security
You can have replicated servers that you can take backups without messing around with the production ones.
– Performance
MySQL allows slave nodes to have read access on the replicated databases. This means, that you can load balance the requests so that the master will handle the write requests while the slave(s) will manage the reading.

In addition to these, MySQL provides a few different options for setting up a replicated environment. Basically, the master node is the one that has full access on the database and the slaves use to replicate its databases. That said, you can have the following architectures:
– Master to Slave
This is the most common architecture where the master’s data are replicated to a slave node where external services can only perform read operations.
– Master to Slaves
Similar to the previous one with the main difference of having multiple slave nodes replicating the master’s data.
– Master to Slave to Slave
Here, we have multi-tier architecture where the initial master’s slave node is the master node for a second layer slave and so on.
– Circular Replication
In this case, we still have a multi-tier architecture like the previous one but the last one serves as a master node for our first server.
– Master to master
Here each server is both a master and a slave, the first node acts as a master and slave node to the second one at the same time. This creates a high availability system since with even one working server you still have full access to your data.

How Does it Work?
First of all, MySQL has support for asynchronous (default) and semi-synchronous replications. In infrastructures requiring full synchronization you have to move to different solutions such as MySQL NDB Clustering. Now, the operation is straightforward. The master node maintains a binary log file of the changes performed on its database. On the other hand, the slave(s) is/are periodically reading that file and perform the replication using one of the following two replication types:
– Statement Based Replication (SBR) – (default)
The slave will execute the exact same SQL statements that the master did in its database(s)
– Row Based Replication (RBR)
Here the slave(s) will change/replicate only the changed rows
– Mixed Based Replication (MBR)
Using both of the above replication types.

How to Set it Up?
Here is a quick setup of a simple MySQL master/slave replication using CentOS 5.5 (2.6.18-194.32.1.el5 #1) on two 64-bit x86 systems. I will be calling them master-node and slave-node respectively.

Master Node Setup
First you have to install the MySQL packages…

[root@master-node ~]# yum install mysql-server mysql-client

Next, start the daemon using the default configuration file

[root@master-node ~]# /etc/init.d/mysqld start

And check that it is running

[root@master-node ~]# netstat -tnlp | grep mysqld
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      3744/mysqld  

I suggest setting a password to the MySQL root account like this:

[root@master-node ~]# mysqladmin -u root password password

Next, login to the MySQL shell and create a new user for replication as shown below.

[root@master-node ~]# mysql -u root -p
Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslaveuser'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

Always remember to flush privileges otherwise changes will not take effect. So, we now have a new user ‘myslaveuser’ for our replication server. The next step is to either import or as in my case create an example database.

mysql> CREATE DATABASE example_db;
Query OK, 1 row affected (0.00 sec)

mysql> USE example_db;
Database changed
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

As you can see I am using a reading lock in the newly created ‘example_db’ database. I’m doing this to safely take a backup of this using the next command.

[root@master-node ~]# mysqldump --all-databases --master-data > example_db.dmp

Now we jump to the configuration file located at ‘/etc/my.cnf’ and insert the following two lines after the ‘mysqld’ tag.

[mysqld]
log-bin=mysqlbin
binlog-do-db=example_db
server-id=1

The first one (log-bin) will enable the binary logging feature and use the provided name for it. The second one is used to name the database that the log will monitor. And the last one to identify this server with an ID during the MySQL communication between the two (or more) nodes. After doing this, restart the service…

[root@master-node ~]# /etc/init.d/mysqld restart

Now, login to your MySQL shell and unlock the locked database using

mysql> UNLOCK TABLES;

And execute the following command

mysql> SHOW MASTER STATUS;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| mysqlbin.000004 |       98 | example_db   |                  | 
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

That shows the binary log file name as well as what databases are affected by it and what is the master’s current position in that file. Now we are pretty much done with the master setup. We can move to the slave node now…

Slave Node Setup
After installing the same packages and starting the service with its default configuration file, you are copying the database to be replicated using the backup we took earlier from the master node.

shell> mysql < example_db.dmp

And you reconfigure the ‘/etc/my.cnf’ file to include the following information:

[mysqld]
log-bin
replicate-ignore-db=mysql
log-slave-updates
log-warnings

report-host=192.168.0.2     # Slave's IP or hostname
server-id=2
master-host=192.168.0.1    # Master's IP or hostname
master-user=myslaveuser    # Slave user created on master node
master-password=password   # Slave user's password on master node
master-connect-retry=60
master-port-3306           # Master's MySQL listening port
replicate-do-db=example_db # Which database to replicate from the master

Now restart the service and login to the slave’s MySQL shell and issue the following commands…

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin.000004', MASTER_LOG_POS=98;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

So, you basically done at this point. You can also use the next command to have a brief overview of what’s going on on the slave node…

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.1
                Master_User: myslaveuser
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqlbin.000004
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000005
              Relay_Log_Pos: 234
      Relay_Master_Log_File: mysqlbin.000004
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: example_db
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 234
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

As you can see there are numerous options that you can tweak from security related such as the SSL support to performance ones. If we jump back to the master node now and login to its MySQL shell we can see the connected slave like this:

mysql> SHOW SLAVE HOSTS;
+-----------+-------------+------+-------------------+-----------+
| Server_id | Host        | Port | Rpl_recovery_rank | Master_id |
+-----------+-------------+------+-------------------+-----------+
|         2 | 192.168.0.2 | 3306 |                 0 |         1 | 
+-----------+-------------+------+-------------------+-----------+
1 row in set (0.00 sec)

mysql> quit
Bye
[root@master-node ~]#

So, that was it. You can now use the master node as a common MySQL database server and see the replicated records on the slave.