How to Setup Replicate MySQL on RHEL 7 Keep Your Web Server Alive

Preparation

Okay, on this article i want share how to setup replication mysql in RHEL 7, i hope you are enjoy with my explain. here, i have 2 server to maked 1 Server for Master and 1 More to be Slave :

1. Server 1 [Master] : 192.168.79.130
2. Server 2 [Slave] : 192.168.79.138

Configure Master Server

For the first step you must edit /etc/my.cnf for this master worked

# cp /etc/my.cnf /etc/my.cnf.ori // for backup
# vi /etc/my.cnf

 [mysqld]

........

........

# replication

server-id = 1

log-bin=mysql-bin

Save & Exit

my.cnf be master

for apply that configured you must restart mysqld

# systemctl restart mysqld

You must have one or more databases to replicate, and this time i want create three database in master server.

# mysql -u root -p
> show databases;
> create database bangkit;
> create database ade;
> create database saputra;

create databases

After create database you must create user for this fitur (replicate) and just allow access from ip slave server

> create user 'repuser'@'192.168.79.138' identified by '123!@#QwE';
> grant replication slave on *.* to 'repuser'@'192.168.79.138';
> flush privileges

Backup database and copy that database to slave server with scp or other

# mysqldump -u root -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --routines --opt --all-databases > all_databases.sql
# scp all_databases.sql [email protected]:/home/kitsake

backup databases and transfer to slave server with scp

Configure Slave Server

Like a first step on configure master server, in this moment you must edit /etc/my.cnf on slave server, but here im exclude database no needed. 

# cp /etc/my.cnf /etc/my.cnf.ori // for backup
# vi /etc/my.cnf

[mysqld]

........

........

server-id=2

relay-log-index=slave-relay-bin.index

relay-log=slave-relay-bin

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=information_schema.%

replicate-wild-ignore-table=performance_schema.%

replicate-wild-ignore-table=sys.%

read-only =1

Save & Exit

my.cnf be slave

for apply that configured you must restart mysqld

# systemctl restart mysqld

Restore the database

# mysql -u root -p < all_databases.sql

it's time for a collaboration between the master server and the slave server

In Master Server [192.168.79.133] you must see information bin log

# mysql -u root -p
> show master status;

and put to your notepad that information is showing will needed to setup in slave server 

Next step is insert the binlog from Master Server to this Slave Server [192.168.79.138]

# mysql -u root -p
> stop slave;
> CHANGE MASTER TO MASTER_HOST='192.168.79.133', MASTER_USER='repuser', MASTER_PASSWORD='123!@#QwE', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
> start slave;

show binlog and start slave

And finally this step to replicate done and see that replication worked or no type this command at Slave Server

> show slave status \G;

show slave status

on this picture any three point :

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes & Slave_SQL_Running: Yes
Seconds_Behind_Master: 0