Master - Slave Replication Setup

Consider 2 MySQL Servers for replication setup, one is a Master and the other is a Slave.

We are going to configure the Master that it should keep a log of every action performed on it. We are going to configure the Slave server that it should look at the log on the Master and whenever changes happens in log on the Master, it should do the same thing.

Master Configuration

First of all, we need to create a user on the Master. This user is going to be used by Slave to create a connection with the Master.

CREATE USER 'user_name'@'%' IDENTIFIED BY 'user_password';
GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'%';
FLUSH PRIVILEGES;

Change user_name and user_password according to your Username and Password.

Now my.inf (my.cnf in Linux) file should be edited. Include the following lines in [mysqld] section.

server-id = 1
log-bin = mysql-bin.log
binlog-do-db = your_database

The first line is used to assign an ID to this MySQL server.

The second line tells MySQL to start writing a log in the specified log file. In Linux this can be configured like log-bin = /home/mysql/logs/mysql-bin.log. If you are starting replication in a MySQL server in which replication has already been used, make sure this directory is empty of all replication logs.

The third line is used to configure the database for which we are going to write log. You should replace your_database with your database name.

Make sure skip-networking has not been enabled and restart the MySQL server(Master)

Slave Configuration

my.inf file should be edited in Slave also. Include the following lines in [mysqld] section.

server-id = 2
master-host = master_ip_address
master-connect-retry = 60

master-user = user_name
master-password = user_password
replicate-do-db = your_database

relay-log = slave-relay.log
relay-log-index = slave-relay-log.index

The first line is used to assign an ID to this MySQL server. This ID should be unique.

The second line is the I.P address of the Master server. Change this according to your Master system I.P.

The third line is used to set a retry limit in seconds.

The next two lines tell the username and password to the Slave, by using which it connect the Master.

Next line set the database it needs to replicate.

The last two lines used to assign relay-log and relay-log-index file names.

Make sure skip-networking has not been enabled and restart the MySQL server(Slave)

Copy Data to Slave

If data is constantly being added to the Master, we will have to prevent all database access on the Master so nothing can be added. This can be achieved by run the following statement in Master.

FLUSH TABLES WITH READ LOCK;

If no data is being added to the server, you can skip the above step.

We are going to take data backup of the Master by using mysqldump

mysqldump your_database -u root -p > D://Backup/backup.sql;

Change your_database and backup directory according to your setup. You wll now have a file called backup.sql in the given location.

If your database not exists in your Slave, create that by executing the following

CREATE DATABASE `your_database`;

Now we have to import backup into Slave MySQL server.

mysql -u root -p your_database  <D://Backup/backup.sql
--->Change `your_database` and backup directory according to your setup

Start Replication

To start replication, we need to find the log file name and log position in the Master. So, run the following in Master

SHOW MASTER STATUS;

This will give you an output like below

+---------------------+----------+-------------------------------+------------------+
| File                | Position | Binlog_Do_DB                  | Binlog_Ignore_DB |
+---------------------+----------+-------------------------------+------------------+
| mysql-bin.000001    | 130      | your_database                 |                  |
+---------------------+----------+-------------------------------+------------------+

Then run the following in Slave

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='master_ip_address', MASTER_USER='user_name', 
   MASTER_PASSWORD='user_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=130;
SLAVE START;

First we stop the Slave. Then we tell it exactly where to look in the Master log file. For MASTER_LOG_FILE name and MASTER_LOG_POS, use the values which we got by running SHOW MASTER STATUS command on the Master.

You should change the I.P of the Master in MASTER_HOST, and change the user and password accordingly.

The Slave will now be waiting. The status of the Slave can be viewed by run the following

SHOW SLAVE STATUS;

If you previously executed FLUSH TABLES WITH READ LOCK in Master, release the tables from lock by run the following

UNLOCK TABLES;

Now the Master keep a log for every action performed on it and the Slave server look at the log on the Master. Whenever changes happens in log on the Master, Slave replicate that.