How to Setup MySQL Master-Slave Replication on Ubuntu 20.04 LTS

MySQL master – slave replication is a technique that permits information base records to be recreated or duplicated across at least one hubs in an organization. This arrangement gives repetition and adaptation to internal failure to such an extent that in case of a disappointment in the Master hub, the data sets can in any case be recuperated in the Slave hub. This gives clients genuine feelings of serenity that all won’t be lost in any possibility as a copy of the data sets can in any case be recovered from an alternate worker.

 

In this post, We will configure MariaDB master and slave replication on ubuntu linux.

 

MariaDB node details are given below:-

 

MariaDB master node IP – 192.168.0.10

MariaDB slave node IP    – 192.168.0.11

 

On Master Node command execution.

 

Step 1: Update the System 

 

You need to update the current ubuntu repository by using the given command. 

 

sudo apt-get update 

 

Step 2: Install MariaDB Package

 

To install  MariaDB packages you need to execute the following command.

 

sudo apt-get install mysql-server mysql-client -y

 

Step 3: Bind IP Address 

 

When the packages installed then we need to bind the master node ip address with MariaDB database server using mysql configuration file, Use the following command for the same.

 

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

 

bind-address      = 127.0.0.1

Change IP address with your machine ip address, In my case i am using 192.168.0.10

bind-address      = 192.168.0.10

 

now find out the bind parameter and add the master node ip address.

 

Step 4: Configure Server ID 

 

You need to set or update the server id on same config file mysqld.cnf find out the parameter and set server id =1 

server-id     = 1

 

Step 5: Configure Logs 

 

You need to also configure the MariaDB logs on the same config file, copy and paste the given parameter.

 

relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index

 

Save and exit from the nano editor.

 

Step 6: Restart the MariaDB Service 

 

To get new changes you need to restart the mysql service by using  the given command and after that we need to check the mysql running or not.

 

sudo systemctl restart mysql && sudo systemctl status mysql

 

Step 7 : Create Replication User in MariaDB 

 

You need to take login as a root user and after that we need to create a replication user in our MySQL database server, It will use in MariaDB slave server in order to sync data from MariaDB master node.

 

To get login in msql shell

 

sudo mysql -u root -p

To create a replication user, You can change the username and password for security purpose.

CREATE USER 'replication_user'@'192.168.0.11' IDENTIFIED BY 'type_password_here';

To grant replication permission

 

GRANT REPLICATION SLAVE ON *.* TO 'replication_user '@'192.168.0.11';   

FLUSH PRIVILEGES;

 

Step 8: Get Replication Details

 

Now you need to execute the given command to get the file and position to configure MySQL slave node.

 

SHOW MASTER STATUSG ;

 

Here our MariaDB master node installation and configuration process is completed.

 

On MariaDB Slave Node

 

Step 1: Update the System 

 

You need to update the current ubuntu repository by using the given command. 

 

sudo apt-get update 

 

Step 2: Install MariaDB Package

 

To install  MariaDB packages you need to execute the following command.

 

sudo apt-get install mysql-server mysql-client -y

 

Step 3: Bind IP Address 

 

When the packages installed then we need to bind the master node ip address with MariaDB database server using mysql configuration file, Use the following command for the same.

 

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

 

bind-address      = 127.0.0.1

Change IP address with your machine ip address, In my case i am using 192.168.0.10

bind-address      = 192.168.0.11

now find out the bind parameter and add the master node ip address.

 

Step 4: Configure Server ID 

 

You need to set or update the server id on same config file mysqld.cnf find out the parameter and set server id l =2

 

server-id     =2

 

Step 5: Configure logs 

 

You need to also configure the MariaDB logs on the same config file, copy and paste the given parameter.

 

relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index

 

Save and exit from the nano editor.

 

Step 6: Restart the MariaDB Service 

 

To get new changes you need to restart the mysql service by using  the given command and after that we need to check the mysql running or not.

 

sudo systemctl restart mysql && sudo systemctl status mysql

 

Step 7: Slave Node Configuration

 

You need to take a myql shell access as a root user and execute the following query to configure replication with master node.

 

To get access on mysql shell.

 

sudo mysql -u root -p

 

To create a replication user. 

 

STOP SLAVE;

You need to change or replace the below values with your own’s credentials.

1. MariaDB Master IP Address
2. Replication username
3. Password
4. Bin log
5. Position

CHANGE MASTER TO MASTER_HOST ='192.168.0.10', MASTER_USER ='type_user_here', MASTER_PASSWORD ='type_password_here', MASTER_LOG_FILE = 'type_bin_log_here', MASTER_LOG_POS = type_position_here;

To get start the MariaDB replication within Master and slave node you need to execute the below query.

 

START SLAVE;

 

Step 8: Verify the Master-Slave Replication

 

You need to check replication is working or not to archive this you need to create sample database on master node and after that we need to check in slave node same database its starting replication or node, Execute the given MySQL query.

Execute on MariaDB master node only (Note: Do not use the write command on slave server)

 

sudo mysql -u root -p

CREATE DATABASE replication_database;

On MariaDB slave node to verify the database replicated.

sudo mysql -u root -p

 

SHOW DATABASES;