How to Setup MySQL Replication (Master-Slave)

 

GETTING STARTED WITH LINUX SERVER

Step 1: Download Ubuntu Server from

Step 2: Create 2 VMs and install Ubuntu Server operating System.

Step 3: Download MobaXterm software on your local computer

Ubuntu Server: https://ubuntu.com/#download

MobaXterm: https://mobaxterm.mobatek.net/

INSTALL MYSQL SERVER ON UBUNTU SERVER 1

Open the terminal and write the below script one by one

sudo apt update

sudo apt upgrade

sudo apt install mysql-server

sudo systemctl start mysql.service;

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'TypeYourPassword';

mysql -u root -p

INSTALL MYSQL SERVER ON UBUNTU SERVER 2

Open the terminal and write the below script one by one

sudo apt update

sudo apt upgrade

sudo apt install mysql-server

sudo systemctl start mysql.service;

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'TypeYourPassword';

mysql -u root -p

Getting Ready Master Server | Server 1 | 10.0.0.1

First, identify the IP address of your server. If you’re in MySQL command line, write

exit

Then install net-tools. To do that write

sudo apt install net-tools

Then write

ifconfig

You will get your IP address.

  Run the following script to see MySQL configuration file

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

delete hash (#) before following settings and put your server IP address instead of 127.0.0.1.

bind-address = 10.0.0.1

server-id = 1

 log_bin = mysql-bin

Then press Ctrl + X after that Y and last, Enter

After that reboot your MySQL server by writing

service mysqld restart;

Run the following script to see MySQL Master status

mysql -u root –p

SHOW MASTER STATUS\G

Create a new login for the replication on master server.

CREATE USER 'replica'@'10.0.0.2' IDENTIFIED WITH  mysql_native_password BY 'PutAnyPassword';

GRANT REPLICATION SLAVE ON . TO 'replica'@'10.0.0.2';
Select host, user from mysql.user;

mysql -u replica -h 10.0.0.1 -p

Getting Ready Slave Server | Server 2 | 10.0.0.2

Same way 

-Enable MySQL server with server IP. -Set a unique server ID. -Enable the binary logging.

Run the following script on Master server

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

delete # before following settings and put your server IP address instead of 127.0.0.1. bind-address = 10.0.0.2 server-id = 1 log_bin = mysql-bin Once done, restart the MySQL service for changes to take effect

service mysqld restart

Step 3: Configure the parameters for slave server to connect to the master server. ***********************************************************************************

mysql -u root -p

STOP SLAVE;

Change MASTER_LOG_FILE name and MASTER_LOG_POS which we already copied from master server. In this scenario MASTER_LOG_FILE='mysql-bin.000022' and MASTER_LOG_POS =155

CHANGE MASTER TO
MASTER_HOST='10.0.0.1',
MASTER_USER='replica',
MASTER_PASSWORD='PutYourReplicaUserPassword',
MASTER_LOG_FILE='mysql-bin.000022',
MASTER_LOG_POS=155;

start slave;

show slave status\G;

Create a demo database on the Master server (10.0.0.1)

CREATE DATABASE Demo_Replication; 

USE Demo_Replication; 

CREATE TABLE Demo_table (Name VARCHAR(20)Address VARCHAR(30));

Check the demo database on will be available on the Slave server (10.0.0.2)

#Login your Slave Server
mysql -u root -p
SHOW DATABASES;