Mysql: Replication setup between two mysql servers( Master - Slave ) setup.

Setting up replication between two mysql servers is easy. Not many steps involved.

below are the steps that need to be done to have replication working in Master-Slave configuration.

1) In order to setup the replication between the 2 servers. First we need to make sure everything is stopped on the source(Master) and the Target(Slave) side.

2) Bounce the Mysql on both Source and target.

3) Take the backup of the Source database( I used MEB method for the backup).

4) Restore the backup of the Source database in to Target database ( Used MEB backup).

5) we need to add Master configuration on the source side by editing /etc/my.cnf file

On Source(Master side):

server-id=121212   # if it is not present

sync_binlog=1  # enable "1" if replication is used for master

binlog_checksum = NONE

binlog_format = ROW

log-bin=/opt/mysql/log/hostname-bin.log

log-bin-index=/opt/mysql/log/hostname-bin.index

log_error=/opt/mysql/log/mysqld.log    --- if not present

expire_logs_days = 10

log_error_verbosity=2   # added it so that you can watch for errors and warnings. "1" just prints errors.

6) On source side, create replication user for the target with IP like below and grant replication grant.

mysql> create user 'TSTREPL_user'@'targetIP' identified by 'TSTREPL#1234';    --- run on master

mysql> grant replication slave on *.* to 'TSTREPL_user'@'targetIP';

mysql> Flush privileges;

 mysql> FLUSH TABLES WITH READ LOCK;

   show variables like 'sql_log_bin';

  set sql_log_bin=0;  --- to disable binary logging    we are doing this to disable replication for sometime till we setup slave with master backup and config etc.

   -- when ready to run command on step 5 on Slave-side

   mysql> unlock tables;

7) On Target side/ Slave side.

Run the below command to connect to Source side/Master.

CHANGE MASTER TO MASTER_HOST='sourceIP',MASTER_USER='TSTREPL_user',MASTER_PASSWORD='TSTREPL#1234',MASTER_LOG_FILE='hostname-bin.000001',MASTER_LOG_POS=150; 

8) On Source Side(Master side), reset the Master position and Log.

reset master

show master status \G;

(confirm bin is now reset to 000001)

 

9) On Target Side(Slave Side).

start slave;

show slave status \G;

  

10) Test replication by creating a table and insert rows on Master side(Source side) and check on slave side(target side).

On master..

use mysql;

create table TEST (id int);

    insert into TEST(1);

    select count(*) from TEST;  --- 1 row

11) On Target side (Slave), Validate the table got created and row got inserted.

use mysql;

select count(*) from TEST;  --- 1 row

end......