Mysql replication – Master to Master
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.