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

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 – Slave 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/

You need to install mysql server on both master and slave machine.

Login to Master Server.

 

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 remotely access your server

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

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.

mysql > show master status;

master-status


Login to Slave Server. 

 

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

Step 7. Restart Mysql Server.

# service mysql restart

Step 8. Login to Mysql Server.

# mysql -u root -p

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

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


Login to Master Server.

 

Step 10. Login to Mysql Server

# mysql -u root -p

Step 11. For testing a Replication we need to create a new database, it will automatically replicate on Slave Server.

mysql > create database demo;


Login to Slave Server.

 

Step 12. Login to Mysql Server

# mysql -u root -p

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

mysql > show databases;