MySQL :How to Configure Mysql master slave replication in MYSQL database
ฝัง
- เผยแพร่เมื่อ 12 ก.ย. 2024
- 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.
#
cp /etc/my.cnf /etc/my.cnf_bkp_5may2020
vi /etc/my.cnf
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.
[root@linux2 ~]#
mysql -u root -p
Mysql@1234
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)
CREATE USER 'replica'@'192.168.1.61' IDENTIFIED WITH mysql_native_password BY 'Password@123';
GRANT REPLICATION SLAVE ON . TO 'replica'@'192.168.1.61';
Select host, user from mysql.user;
[root@linux3 ~]# mysql -u replica -h 192.168.1.51 -p
Step 2 : [root@linux3 ~]#CONFIGURE THE SLAVE SERVER
***************************************************
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
slave:/etc/my.cnf
cp /etc/my.cnf /etc/my.cnf_orig
vi /etc/my.cnf
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
Mysql@1234
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='192.168.1.51',
MASTER_USER='replica',
MASTER_PASSWORD='Password@123',
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;
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
Been wondering that as well
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.
you explained well. thank you so much
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! :)
Clarified my confusions, thank you
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?
Perfect video . Thanks for sharing
Thanks for finding it useful.
Good demonstration and tutorial. Thank you.
Great video. Big Thanks!
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.
I have not tried it yet .
Thank you for excellent videos and for .txt
hey how do you make the master and slave instances and have different IP addresses, linux3, linux2?
great video, thanks. make more
Thank you for finding it useful.
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..
Did we any issue in normal way of functioning ?
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
Where are 2 hosts ? Cloud?
Local?
@@EMultiSkillsDatabaseServices i'm performing this on cloud
I have problem with connection slave dont wanna connect to master. can you help me?
I have error code MY-0002003
thanks for sharing
Thanks for watching!
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?
Yes it's possible . I will try to make a video on that once the time allows
@@EMultiSkillsDatabaseServices thank You very very much for the fast reply, My dear friend. In the meantime, Could You enlight me with any link for that subject, for me to read or follow,? Because I think the assignment has to be done in few days. Sorry, again for me to bother you
Did you try creating it ? Did you face an issue
@@EMultiSkillsDatabaseServices I need the same can u provide any doc r video related to that..
Hi, What is your role? I am asking because trying to figure it out who is responsible for the server side tasks and what skills is needed
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
Provided the configuration is correct, it works
thanks a lot
Happy to help
Hi
Can we do Master-Slave Configuration on the same server?
Why would you like to do that ?
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
Officially MySQL supports replication from one release series to the next higher release series.
You can replicate from a source running MySQL 5.7.* to a replica running MySQL 8.0.*
You might see some issues due to some functionality working in lower version and obsolete in higher version.
Please tell the steps to create master slave replication where slaves will be 2.
Please put the steps. its a request
Are these 2 slaves on 2 different hosts?
@@EMultiSkillsDatabaseServices correct. 2 slaves on different servers. And master is on different server. The scenario is 1 master 2 slaves
Can you do us this tutorial with Phpmyadmin please? we are using windows as host.
Sure that makes sense
Can we make database replication on the share hosting?
yes, that's possible and good for testing purpose as well.
@@EMultiSkillsDatabaseServices But Want to develop a desktop application that can make database replication real time as well as data like files, directory images, docs etc that may be a specific directory or a complete project.
will you to work on it. I already searching experienced person who can assist or can develop it.
I post project on fiverr and upwork still not found a special one that can do this. still interviewing from last two days.
can i get notes of replication
Sure , is not it in the description of the video as text ?
Amazing tutorial, thank you very much!