How to configure master master replication on mysqld


AllCloud Blog: Cloud Insights and Innovation

Server names: master1, master2
Set up slave account for master2 on master1, enter to mysql on master 1

mysql -u root -p mysql
mysql> GRANT REPLICATION SLAVE ON *.* TO

‘replication’@master2

 identified be 'slave';
mysql> FLUSH TABLES WITH READ LOCK;

shutdown mysql on master1

service mysqld stop

make a tar of datadir on master1

cd /data
tar cvf  /tmp/mysqld_snapshot.tar .

configure my.cnf file on master1

vi /etc/my.cnf

add the following lines in [mysqld] section:

log-binbinlog-do-db=<database name>
binlog-ignore-db=mysql
binlog-ignore-db=test
server-id=1
  1. start mysql on master1 “service mysqld start”
  2. stop mysql on master2 “service mysqld stop”
  3. copy the tar file from master1 to master2 “scp master:/tmp/mysqld_snapshot.tar /tmp”
  4. open the tar file on the datadir of master2
    • cd /data
    • tar xvf /tmp/mysqld_snapshot.tar
    • make sure mysql user rights for the new databases
  5. configure my.cnf file on master2
    • vi /etc/my.cnf
    • add the flowing lines in [mysqld] statement:server-id=2master-host=master1master-user=replicationmaster-password=slavemaster-port=3306
  6. start mysqld on master2 “service mysqld start”
  7. start slave on master2
    • enter mysql “mysql -u root -p mysql”
    • mysql> start slave;
    • mysql> show slave statusG;                 # check status of slave
  8. to chaeck the status on master enter the mysql and run “mysql> show master status;”
  9. for now we enabled master-slave replication to make it master-master we need to enable slave-master replication
  10. configure my.cnf on master2
    • vi /etc/my.cnf
    • add the following lines in [mysqld] section:log-binbinlog-do-db=database_name
  11. create replication account for master1 on master2
    • enter mysql “mysql -u root -p mysql
    • mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication’@master1 identified by ‘slave2’;
  12. configure my.cnf on master1
    • vi /etc/my.cnf
    • add the following lines in [mysqld] section:# information for becoming slavemaster-host=master2master-user=replicationmaster-password=slave2master-port=3306
  13. stop and start mysqld on master1 and master2
    • service mysqld stop
    • service mysqld start
  14. configure master1 as slave
    • enter mysql “mysql -u root -p mysql”
    • mysql> start slave;
  15. check status
    • on each server enter mysql and  issue the following commands:mysql> show master status;mysql> show slave statusG;

Lahav Savir

Founder, EVP and Chief Architect, Cloud Platforms

Read more posts by Lahav Savir