This article describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (say we can call it as slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync.

This is not a backup method or policy because an accidentally issued DELETE or ALTER command will also be carried out on the slave; but replication can help protect against hardware failures though.

2. Configure The Master:

We can configure the master server for replication using the mysql configuration file /etc/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we will comment these lines (if already existing in my.cnf)

 #skip-networking
 #bind-address            = 127.0.0.1

Now we have to configure the MySQL server for which database it should write logs (these logs are used by the slave to see what has changed on the master) and we have to specify that this MySQL server is the master. We want to replicate the database db_test, so we put the following lines into /etc/my.cnf

log-bin
binlog-do-db=db_test
server-id=1

Then restart MySQL:

 # /etc/rc.d/init.d/mysql restart

Then we need to log into the MySQL database as root and create a user with replication privileges:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'u1@%' IDENTIFIED BY 'p1';
mysql> FLUSH PRIVILEGES;

Next (still on the MySQL shell) do this:

mysql> USE db_test;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

The sample master status will be as follows:

mysql> show master status;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| rs2-bin.000063 |       79 | db_test      |                  |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

We need this information later on for the slave configuration Then leave the MySQL shell:

mysql> quit;

3.Configure The Slave:

We can configure the slave server for replication using the file /etc/my.cnf. We have to tell MySQL on the slave that it is the slave, that the master is H1, and that the master database to watch is db_test. Therefore we add the following lines to /etc/my.cnf

server-id=2
master-host=H1
master-user=u1
master-password=p1
master-connect-retry=100
replicate-do-db=db_test

The master-user and master-password are those to which we set when we granted REPLICATION SLAVE permission on the master. The server-id must be a unique number, different to the master or any other slaves in the system. The master-connect-retry is time in seconds for the slave to attempt to reconnect if the master goes down. 60 seconds is default.

Then restart MySQL:

# /etc/rc.d/init.d/mysql restart

4.Data Transfer:

There are two possibilities to get the existing tables and data from db_test from the master to the slave. The first one is to make a database dump, the second one is to use the LOAD DATA FROM MASTER; command on the slave.

(a)For Mysqldump method:

On master server, please take the dump of the database using

# mysqldump  db_test>db_test.sql
Then scp the file db_test.sql to slave server.
# Scp db_test.sql root@slave_serverip:/root
root@slaveip's password:

Restore the db_test.sql file at slave server

# mysqldump db_test < db_test.sql

(b)To LOAD DATA FROM MASTER:

Please perform the following at Master end:

# mysql -u root -p
Enter password:
mysql> UNLOCK TABLES;
mysql> quit;

Please perform the following at Slave end:

# mysql -u root -p
Enter password:
mysql> LOAD DATA FROM MASTER;
mysql> quit;

5. Replication in action:

Once the mysql master and slave configuration completed, we can check the replication action using the command ‘SHOW SLAVE STATUS’.

We can check if the slave is running correctly by looking at the Slave_IO_Running and Slave_SQL_Running. The most important field is the Last_Error field.

Mysql> SHOW SLAVE STATUSG

This will give the result as follows:
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: H1
                Master_User: u1
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: rs2-bin.000063
        Read_Master_Log_Pos: 346
             Relay_Log_File: vrh-relay-bin.000001
              Relay_Log_Pos: 312
      Relay_Master_Log_File: rs2-bin.000063
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: db_test
        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: 346
            Relay_Log_Space: 312
            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)

If you need to force the slave to begin at a certain point, usually when the master has been running with an active binary log, you can do so as follows ( refer the master status value). The following starts with the 63rd binary log, as position 79.

mysql> CHANGE MASTER TO MASTER_HOST='H1',
MASTER_USER='u1',MASTER_PASSWORD='p1', MASTER_LOG_FILE='rs2-bin.000063', MASTER_LOG_POS=79;

MASTER_HOST is the IP address or hostname of the master (in this example it is H1).
MASTER_USER is the user we granted replication privileges on the master.
MASTER_PASSWORD is the password of MASTER_USER on the master.
MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

The SLAVE START and SLAVE STOP commands are used to manually stop and start the slave. The slave will also always stop if it comes across an error while replicating.

mysql> SLAVE STOP;
mysql> SLAVE START;

On active databases, the binary logs tend to grow quite quickly. We can use RESET MASTER to clear them. The RESET MASTER command tells the master to flush all its binary logs and start fresh. But while replication in action, we will have to execute the following.

mysql> PURGE MASTER LOGS TO 'rs2-bin.000063';

6. Options:

By default, the slave will replicate everything, but you can change this behavior with the following options in the slave configuration file (my.cnf).

replicate-do-db=db_name (replicate this database)
replicate-ignore-db=db_name (don't replicate this database)
replicate-do-table=db_name.table_name (replicate this table)
replicate-ignore-table=db_name.table_name (don't replicate this table)

7. Testing:

At master end if you are modifying the database db_test, it will reflect on slave server in real time using replication.

Example: On the master server if we are inserting the values as follows:-

mysql> use db_test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| staffactive       |
+-------------------+
1 row in set (0.00 sec)

mysql> desc staffactive;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) |      |     |         |       |
| date     | varchar(20) |      |     |         |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO staffactive (username,date) VALUES ('savi','20070305');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO staffactive (username,date) VALUES ('testuser', 'testdate');
Query OK, 1 row affected (0.00 sec)

mysql> select * from staffactive;
+----------+----------+
| username | date     |
+----------+----------+
| savi     | 20070305 |
| testuser | testdate |
+----------+----------+
2 rows in set (0.00 sec)

mysql> q

Bye

This will reflect on the slave server in real time as follows:-

mysql> use db_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from staffactive;
+----------+----------+
| username | date     |
+----------+----------+
| savi     | 20070305 |
| testuser | testdate |
+----------+----------+
2 rows in set (0.00 sec)

mysql> q

Bye

About the author:
Savitha Nair works as Sr. Software Engineer Grade 11 in Bobcares.com. Savitha has worked in Bobcares for 3 years and is a passionate advocate of linux server administration.