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 :
- Server 1 [Master] : 192.168.79.130
- 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 |
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes & Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Post a Comment for "How to Setup Replicate MySQL on RHEL 7"
Post a Comment