How To Set Up MySQL Master-Master Replication

MySQL Master-Master Replication configuration setup

=> Setup requirement :-

We will have two mysql Servers, named db1 and db2 to setup the master to master Replication.Both servers have two IP addresses (one public, one private).We will configure the replication to be done over the private IP interface so that we don’t incur any bandwidth charges.

Note: Commands listed below are to be run as a privileged (root, sudo group) user.

Step 1: Installing Mysql

First we need to install MySQL on both the DB Servers. As always, prior to installing any packages, we need to make sure that our package list is up to date and our locale/language settings are configured properly.

Update the package Database          

# yum update

Now, you can run the following commands to install MySQL

# yum install mysql-server mysql-client libmysqlclient15-dev

=> Configuring replication:-
Once the mysql-server package has been installed successfully, we can start configuring each of the MySQL nodes in order to enable replication between them.
We need to create the database that will be replicated as well as the replication username and password to be used with it. You can use the commands outlined below to set them up.

First on DB1, login to the mysql console (using mysql root password setup during MySQL installation).

# mysql -u root -p

       Enter password:

       mysql>

Now let’s create the replication user, which will be used to synchronize the changes.
mysql> grant replication slave on *.* to slaveuser@'[private IP of DB2]' identified by '[password]';
          mysql> flush privileges;
          mysql> exit

Do the same for DB2
mysql> grant replication slave on *.* to slaveuser@'[private IP of DB1]’ identified by ‘[password]’;
mysql> flush privileges;
mysql> exit

Now in DB1, edit /etc/mysql/my.cnf and insert/update or uncomment following entries

bind-address = [Ip address of DB1]

server-id = 1

log-bin = /var/log/mysql/var/bin.log

log-slave-updates

log-bin-index = /var/log/mysql/log-bin.index

log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log

relay-log-info-file = /var/log/mysql/relay-log.info

relay-log-index = /var/log/mysql/relay-log.index

auto_increment_increment = 10

auto_increment_offset = 1

master-host = [private IP address of DB2]

master-user = [replication username]

master-password = [replication password]

replicate-do-db = <database name to be replicated>




=> Repeat the steps on the DB2 server:

bind-address = [Ip address of DB2]

server-id = 2

log-bin = /var/log/mysql/bin.log

log-slave-updates

log-bin-index = /var/log/mysql/log-bin.index

log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log

relay-log-info-file = /var/log/mysql/relay-log.info

relay-log-index = /var/log/mysql/relay-log.index

auto_increment_increment = 10

auto_increment_offset = 2

master-host = [private IP address of DB1]

master-user = [replication username]

master-password = [replication user password]

replicate-do-db = [database name to be replicated]



Now, restart both databases. If the service restart on either server fails, then please check the /var/log/mysql/error.log file for any errors. Update the configuration and check for any typos, etc.



=> Testing the scenarios:-

To test the Replication setup,we can create the database specified in the configuration file.



mysql> create database [your-db-name];

             mysql> use [your-db-name]

             mysql> create table foo (id int not null, username varchar(30) not null);

             mysql> insert into foo values (1, 'bar');

An additional test is to stop the MySQL service on DB2, making database changes on the DB1 server and then restarting the MySQL service on DB2. The DB2 MySQL service should sync up all the new changes automatically.

You should also consider changing the default binary log rotation values (expire_logs_days and max_binlog_size ) in the /etc/mysql/my.cnf file, as by default all the binary logs will be kept for 10 days. If you have high transaction count on your database application then it can cause significant hard disk space usage in logs. So, we recommend changing those values to match your server backup policies. For example, if you have daily backups setup of your MySQL node then it makes no sense to keep 10 days worth of binary logs.