Monday, 2 December 2019

MYSQL PROD to DR replicaiton(master - slave) setup for high avaliablity

Below is the step for DR replication setup for mysql.

Step1:Edit mysql config file
vi /etc/my.cnf
    [mysqld]
    server-id = 1
    log-bin=/var/lib/mysql/master-bin
    expire_logs_days = 10

Step2:Restart mysql    
# /etc/init.d/mysqld restart

Step3: Setup replication in PROD
mysql  -uroot -p******
    show variables like 'log_bin';
     CREATE USER 'repl_slave'@'%' IDENTIFIED BY '*****';
     GRANT REPLICATION SLAVE ON *.* TO 'repl_slave'@'%';
     FLUSH PRIVILEGES;
    SHOW MASTER STATUS;
     FLUSH TABLES WITH READ LOCK;

Step4: Backup:
#mysqldump -uroot -p****** --ignore-table=mysql.event --all-databases --master-data >mysql_dbs_oevent.dump

Step5:copy the dump to DR
scp mysql_dbs_oevent.dump DRIP@/hom

Step6: Edit the DR /etc/my.cfg
    server-id = 2
    read_only
    relay-log=/relay-bin
    expire_logs_days = 10

Step7: Restart mysql    
# /etc/init.d/mysqld restart

Step8: Setup replication in DR

CHANGE MASTER TO MASTER_HOST = 'PRODREPLICATIONIP/Hostname'
   , MASTER_USER = 'repl_slave'
   , MASTER_PASSWORD = '******'
   , MASTER_PORT = 3306;
  
  
Step8: Import the data
mysql -uroot -p******** < mysql_dbs.dump

Step9: Check status in DR
mysql  -uroot -p******
show slave status\G

At DR to stop and start replication
show slave status\G
stop slave;   
start slave;   
show slave status\G;

No comments: