MySQL :How to Configure Mysql master slave replication in MYSQL database

MySQL :How to Configure MySQL Master-Slave Replication on RHEL
*********************************************************************

MySQL replication procedure enabled you to automatically copy data from one MYSQL database server to one or more MYSQL servers.

MySQL supports a number of replication topologies with Master/Slave topology being one of the most well-known topologies in which

one database server acts as the master, while one or more servers act as slaves. By default, the replication is asynchronous where

the master sends events that describe database modifications to its binary log and slaves request the events when they are ready.

This sort of replication topology is good for deploying of read replicas for read scaling, live databases backup

for disaster recovery and for analytics jobs.

MySQL 8.0 supports different methods of replication. The traditional method is based on replicating events from the master's binary log,

and requires the log files and positions in them to be synchronized between master and slave.

Prerequisites
*************

Setup
*******
ROLE HOST OS MYSQL SERVER VERSION

Master 192.168.1.51(linux2) OEL 6.5 8.0.19
Slave 192.168.1.61(linux3) OEL 6.5 8.0.19

vaidate value of

show variables like '%uuid%';

Step 1: Configure the Master Server.
*************************************

-Let's configure the master MySQL server and perform below changes.

-enable MySQL server to listen on the private IP.
-Set a unique server ID.
-Enable the binary logging.

#

bind-address = 192.168.1.51
server-id = 1
log_bin = mysql-bin

-Once done, restart the MySQL service for changes to take effect

# service mysqld restart

-create a new replication user who can login master server from slave server.

mysql -u root -p

SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000022
Position: 155
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.01 sec)

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.1.61';

***************************************************
Like master server perform below changes on slave server.

Set the MySQL server to listen on the private IP
Set a unique server ID
Enable the binary logging

bind-address = 192.168.1.61
server-id = 2
log_bin = mysql-bin

# service mysqld restart

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

mysql -uroot -p

STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='192.168.1.51',
MASTER_USER='replica',
MASTER_LOG_FILE='mysql-bin.000023',
MASTER_LOG_POS=686;

start slave;
show slave status\G;

show variables like '%uuid%';

testing
*******

Create database testreplica;
use testreplica;
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`),
UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

[massoudrahimzadeh]

Hi there, thank you for the nice tutorial. I have a question, who (which role) in an organised company is responsible for configuring a replication or server side tasks in general?
What skills are needed?

[oah]

fantastic video, but what format is the bin-log using? is it row based replication or SBR or is it mixed?
and also this works only for brand new databases, right? because if we have old data on the master, i guess it is not being copied to the server.

[dahuzihaidao]

Nice video! Just one question, if I have Mysql-Master deployed on Server A using Docker, what should change to implement a Slave MySQL on Server B? Many thanks! :)

[akky]

how to do maintenance of master-slave replication, like for Master DB we need to reboot but no downtime so, in this case can we do one thing like slave server become master and vice versa so we can reboot the server and then again change it back to previous

[muhammadazeem]

Thanks, but I have a question. did we can reverse this action synchronization as slave to master. Let me explain. I am using a database under a website and its replication at different server. Now I point my domain to other server that holding slave database will it work fine as like master database. If yes, Now changes will in the slave database and its replication can be handle in the master. two way replication at the same time.

[mohammadhussain]

hey how do you make the master and slave instances and have different IP addresses, linux3, linux2?

[mauromezzina]

Amazing tutorial, thank you very much!

[Private-GtngxNMBKvYzXyPq]

Good demonstration and tutorial. Thank you.

[akshayarya]

on the slave status, i'm getting Last_IO_error : "The slave IO thread stops because master and slave have equal mysql server ids", However i've used different server ids in both the my.cnf files. Can you please help

[veladisravankumar]

Thank You, It's working fine to me. My doubt is, we pointed one binlog file name to slave db. But after few days running mysql, new binlog files will generate ? then how it will work. Out pointed binlog will be old one. Please give your suggestions..

[SilvermanWKWK]

I have problem with connection slave dont wanna connect to master. can you help me?
I have error code MY-0002003

[sarmadmalik]

Hi
Can u please tell master slave is applicable on two different version of mysql
Master mysql verion 5.7.31
Slave mysql version 8.0.22

[carloscoliver]

Hi My friend... Excellent vídeo! But I have a task to make the DB replica in the same physical server, to be used for testing purposes, and not deal with production DB. Would it be possible with this method? What would be the changes I have to make?

[matthewbrown]

Does this method work with multiple machines? I want to write to a database on one computer and have that same update show up on another computer

[AB-mimm]

Thank you for excellent videos and for .txt

[JahulHasan]

Hi
Can we do Master-Slave Configuration on the same server?

[aakashroy]

Please tell the steps to create master slave replication where slaves will be 2.
Please put the steps. its a request

[nickzouein]

Can you do us this tutorial with Phpmyadmin please? we are using windows as host.

[muhammadazeem]

Can we make database replication on the share hosting?