MySQL replication is a process in which a set of data from a single mysql server is being copied live on another mysql servers. Most popular setup of mysql replication is what they call Master-Slave Setup. This setup will just copy the data from the master server and replicate it to the slave servers. What we are going to do now is better than Master-Slave setup because on our case, we will be treating both servers as master and slave at the same time. This means both servers will copy and replicate data from each other, hence Master-Master Replication. Okay, let’s begin.
First we need two servers.
Server-1 - 192.168.100 Server-2 - 192.168.101
Login on both servers using ssh and install mysql-server and mysql-client.
Installing these may vary depending on what type of distro you have. I’m assuming you’re using linux 😉
After installing mysql-server on both servers, edit your my.cnf file which is located under /etc if you’re using linux. Add this code under [mysqld] section
Server-1
server-id=1 log-bin=mysql-bin
Server-2
server-id=2 log-bin=mysql-bin
Then, restart your mysql-server.
service mysqld restart
Now, login to both Mysql Server as root.
mysql -u root -pPassword
Next, let’s create a replicator user on both servers and grant them permission to replicate the data.
Server-1
mysql> grant replication slave on *.* to replicator@"192.168.101" identified by "[your_password]";
Server-2
mysql> grant replication slave on *.* to replicator@"192.168.100" identified by "[your_password]";
Next, Check both servers master status by using this command:
mysql> show master status;
This command will show something like this.
+------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
Take note of the File and Position value from both servers because we are using it later.
After that, make sure no one will write / insert on your database because it will change the position value. So to do that, issue this command on both servers.
flush tables with read lock;
This will lock your entire database and will prevent any create, insert or update query on all of your tables.
Next is input the master info to the slaves. We will use the File and Position value that we gather earlier. Then, start the slave.
Server-1
mysql> stop slave; mysql> CHANGE MASTER TO master_host="192.168.1.101", master_user="replicator", master_password="[your_password]", master_log_file="[FILE_VALUE]", master_log_pos=[POSITION_VALUE]; mysql> start slave;
Server-2
mysql> stop slave; mysql> CHANGE MASTER TO master_host="192.168.1.100", master_user="replicator", master_password="[your_password]", master_log_file="[FILE_VALUE]", master_log_pos=[POSITION_VALUE]; mysql> start slave;
Now unlock tables on both servers
mysql> unlock tables;
And we’re done, your Master-Master Replication setup should be working now.
To verify if your setup is working, create a database on Server-1 and check if it did replicate on Server-2 and vice-versa.
If you encounter any errors, please comment it below so we can try to debug it together 🙂