How to set up master slave replication in MySql

We use below IP address for our set up in this example.

123.34.56.79- Master Database

123.23.34.67- Slave Database

Setup

sudo apt-get install mysql-server mysql-client

1. Configure Master Database

sudo vi /etc/mysql/my.cnf

We need to make few changes in this file.

bind-address            = 127.0.0.1
to
bind-address            = 123.34.56.79

After that we need to change in another line.

server-id               = 1

Now change in log_bin line.

log_bin                 = /var/log/mysql/mysql-bin.log

Finally se the database which we want to replicate.

binlog_do_db            = newdb

Save and exit configuration file.

Restart MySql service.

sudo service mysql restart

Next, we need to grant privileges to the slave. The command should be

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

Now,

USE newdb;

Lock the database to prevent new changes.

FLUSH TABLES WITH READ LOCK;

and then,

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      137 | newdab       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

From this position slave db will start replicating. Keep this number with you will use it later.

Keep current MySql console as it is so it will remian locked as it is. So now open another terminal window and start dump of master db by using below command.

mysqldump -u root -p --opt newdb > newdb.sql

after that come to oringinal window and type below command

Unlock tables;

Quit;

Now let us configure slave database.

2. Configure the Slave DB

Login into MySql shell in slave server and create new database.

CREATE DATABASE newdb;

Exit;

Import database which we have already exported from master database.

mysql -u root -p newdb < /path/to/newdb.sql

Now need to change in configuration file.

sudo vi /etc/mysql/my.cnf

First of all we change the server-id value which we have set 1 for master so for slave it is something different. We need to ensure that the value of server-id must be unique.

server-id               = 2

Following three crieteria we need to feel properly.

relay-log               = /var/log/mysql/mysql-relay-bin.log

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = newdb

Now need to refresh MySql for getting effect of change of configuration.

sudo service mysql restart

Next step is to enable MySql replication from console.

CHANGE MASTER TO MASTER_HOST='123.34.56.79',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  187;

That's it !! you have configured a master and slave server.

Now Activating the slave server.

START SLAVE;

For check the details of the slave replication by below command.

SHOW SLAVE STATUSG

If you find any issue with connection you can use below command to skip it.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; 

Done.