[email protected]

Master1 server configuration:

we are going to setup master to master replication, for instance we are using master1 and master2 servers.

open my.cnf location in etc/

and add this lines

[mysqld]
server-id=1
log-bin=mysql-bin

now restart mysql server

and dump the databases which one you want to replicate and copy to slave server

to dump all databases use

shell> mysqldump --all-databases > dump.sql

to dump only specified databases use

shell> mysqldump --databases db1 db2 db3 > dump.sql

once done we now create a user and grant permissions to replicate the db.

connect to mysql

and grant the access using.

mysql> grant replication slave on *.* to 'master2username'@'180.xxx.xxx.xxx' identified by 'master2password';

once its done, to see the master status type

mysql> show master status;


+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      120 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

you will see the output like this save this file and position id for next steps.

now flush the tables and set the lock to read;

mysql > flush tables with read lock;

[incase if you want to unlock use this command ] mysql > unlock tables;

mysql > change master to master_host = '170.xxx.xxx.xxx',    #this is your master2 host ip
master_user='master2username',
master_password='master2_password',  
master_log_file='mysql-bin.000002',   
master_log_pos=606; # this is the position we get when we did master2 status

 

Master2 configuration:

open my.cnf located in /etc/

and add this line

[mysqld] 
server-id=2
log-bin=mysql-bin

save this file and restart your mysql server

and grant the master user to access master1 db

mysql> grant replication slave on *.* to 'master1username'@'180.xxx.xxx.xxx' identified by 'master1password';

once its done, to see the master status type

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |      606 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

and restart mysql server, once its restarted login to mysql and change the master using this query.

mysql > change master to
master_host = '170.xxx.xxx.xxx',    #this is your master1 host ip
master_user='master1username',   
master_password='master1_password', 
master_log_file='mysql-bin.000002',  # this the file we get when we did master status 
master_log_pos=120; # this is the position we get when we did master status

now all set, dump the databases we copied in master1 to master2, using mysqldump

shell> mysqldump test.sql < testdb

mysql> show processlist;

mysql> show slave status\G;

to see status of the db replication.