How to Setup Replicate MySQL on RHEL 7

How to Setup Replicate MySQL on RHEL 7 - Replicating MySQL databases is a crucial aspect of ensuring data availability, reliability, and scalability in modern database management. 

The process of MySQL replication involves creating and maintaining duplicate copies of a database to enhance data redundancy and fault tolerance. This technique enables organizations to distribute workloads, enhance system performance, and create robust backup mechanisms. 

By replicating MySQL databases, businesses can achieve high availability, reduce the risk of data loss, and improve overall system resilience. 

Whether for disaster recovery, load balancing, or ensuring data consistency across distributed environments, MySQL replication stands as a fundamental strategy for optimizing database performance and safeguarding critical information.

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 root@192.168.79.138:/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