How to Setup Mysql Replication on Ubuntu 16.04 (Master-Master)

mysql-logo

MySQL is one of the most popular open source relational database management system (RDBMS). When your traffic grows then you need to increase the instances of your mysql server and have to replicate the data on multiple servers. MYSQL provide 2 ways to replicate data, master – master and master – slave.

  • Master – Master
    In this mode data is written to both the servers, so whenever one server gets the write request it will sync data to other server. This mode will be best suited when your project have more write requests and you want best redundancy. You can send write requests to any server.
  • Master – Slave
    In this mode only the master server sync data with other servers, so you will have only 1 server which takes the write requests and other servers will be used for read only purpose. This mode will be best suited for projects which have more read requests than write ones and can be used for data backups.

In this tutorial we will learn how to replicate data using Master – Master configuration.

Perquisites: MySql Server

Note: – If you want to see the Installation of Mysql Server, click on below Link:

https://www.cloudjojo.com/how-to-install-mysql-server-in-amazon-ec2-instance/

Step 1. Edit & Modify the Configuration file of MySql  Server.

# vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 127.0.0.1     #comment this line if you want to access mysql remotly

Add below lines at the end of the file.

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1

Step 2. Restart Mysql Server.

# service mysql restart

Step 3. Login to Mysql Server.

# mysql -u root -p

Step 4.  Create a new user for Replication & specify the Password to that user.

mysql > create user 'replica'@'%' identified by 'password';
mysql > GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
mysql > FLUSH PRIVILEGES;

Step 5. Execute below command to view the File & Position of Master Server 1.

mysql > show master status;

master-status-pic


Login to Master Server 2. 

 

Step 6. Edit & Modify the Configuration file of MySql Server.

vim /etc/mysql/mysql.conf.d/mysqld.cnf
# bind-address = 127.0.0.1

Add below lines at the end of the file.

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2

Step 7. Restart Mysql Server.

# service mysql restart

Step 8. Login to Mysql Server.

# mysql -u root -p

Step 9.  Create a new user for Replication & Specify the Password to that user.

mysql > create user 'replica'@'%' identified by 'password';
mysql > GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
mysql > FLUSH PRIVILEGES;

Step 10. Specify the following details as given below & make sure to Replace the following settings with your settings.

MASTER_HOST     :   IP Address of Master server 1
MASTER_USER     :   Replication User of Master server 1 that we had created in previous steps.
MASTER_PASSWORD :   Replication User Password of Master server 1 that we had created in previous steps.
MASTER_LOG_FILE :   Your Value of Master Log File of Master server 1.
MASTER_LOG_POS  :   Your Value of Master Log Position of Master server 1.
mysql > stop slave; 
mysql > CHANGE MASTER TO MASTER_HOST = '107.22.99.68', MASTER_USER = 'replica', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 154; 
mysql > start slave;

Step 11. Execute below command to view the File & Position of Master Server 2.

mysql > show master status;

slave-status-pic


Login to Master Server 1.

 

Step 12. Specify the following details as given below & make sure to Replace the following settings with your settings.

MASTER_HOST     : IP Address of Master server 2.
MASTER_USER     : Replication User of Master server 2 that we had created in previous steps.
MASTER_PASSWORD : Replication User Password of Master server 2 that we had created in previous steps.
MASTER_LOG_FILE : Your Value of Master Log File of Master server 2.
MASTER_LOG_POS  : Your Value of Master Log Position of Master server 2.
mysql > stop slave;
mysql > CHANGE MASTER TO MASTER_HOST = '52.53.224.71', MASTER_USER = 'replica', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000003', MASTER_LOG_POS = 154;
mysql > start slave;

Step 13. For testing a Replication we need to create a new database, it will automatically replicate on Master Server 2.

mysql > create database demo;


Login to Master Server 2.


Step 14.
 Login to Mysql Server

# mysql -u root -p

Step 15. View your Replicated Database by using below command.

mysql > show databases;