Saturday, September 23, 2017

MySQL Replication in CentOS

Requirement
1.Two host which will communicable
2.Same version of MySQL on both Machine
3.Same DB and users were been on both Machine

Here
Master - 192.168.0.75
Slave - 192.168.0.76

#Master
#vi /etc/my.cnf
[mysqld]
server-id=1
log-bin=master-bin.log

save that file

#Slave
#vi /etc/my.cnf
[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%

save that file
#service mysqld restart   //execute both machine

#Master
#mysql -u root -p
mysql> show master status \G;
*************************** 1. row ***************************
            File: master-bin.000001
        Position: 3640
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>unlock tables;      =====>execute both machine

mysql>grant replication slave on *.* to 'repuser'@'%' identified by 'slavepwd';
mysql>flush privileges;

*.* ==> all db
'%' ==> allow from all IP

#Slave
mysql>change master to
->master_host ='192.168.0.75',
->master_port=3306,
->master_user='repuser',
->master_password='slavepwd',
->master_log_file='master-bin.000001',
->master_log_pos=3640;

mysql>start slave;
mysql>show slave status \G;

///if any error happen then disabled iptables from both machine
///now update any tables from master it will be updated automatically from slave

No comments:

Post a Comment