“MySQL Master-Slave Replication Made Simple: Ensuring Data Redundancy and High Availability”

4 min readJul 8, 2023

In this blog, we will delve into the world of MySQL Master-Slave Database Replication and explore how to configure a master-slave setup to achieve data mirroring and enhance the availability and reliability of your MySQL database. We will walk through the steps to set up and configure the master-slave replication, ensuring that your data is synchronized seamlessly and providing high availability for your application.

Let start with launching with 2 VM, signing your AWS account and launch 2 VM you can launch free tier machine also to avoid unnecessary billing

2. After launch the instance login or connect that instance as you see in my case I use “MySQL1”=”Master” and “MySQL2”=”Slave”.

3. Then Open SG(Security Group) for 3306 (for each other private ip) & ICMP IPV4 protocol to communicate with each other

4. For better understanding I use color code as follows:
Master = White Color
Slave = Black Color

Note: Step 6 to 10 are common on both server

5. As the result both machine are now communicating with each other if you want to check then use “ping Public IP” command

6. Then update your both machine

apt-get update

7. So as we doing Replication on “MySQL” so install it on both machines

apt-get install mysql-server -y

8. For verification MySQL install properly and running check MySQL service

systemctl status mysql

9. Now open “mysql.d cnf” file on both machines and do following changes.

vi /etc/mysql/mysql.conf.d/mysqld.cnf

===> change bind address to =0.0.0.0 <===

Slave Server

===> and uncomment a. server-id and b. log_bin <===

Master Server

and when you uncomment on slave give serverid as 2

Slave Server

10. After doing necessary changes save this file “:wq” and resart and check MySQL Service.

systemctl restart mysql
systemctl status mysql

11. Now go to “Master Server” and login MySQL

mysql -u root -p

12. After login use following command

SHOW MASTER STATUS \G
CREATE USER 'repl'@'Private Ip of anther instance(slave)' IDENTIFIED BY 'secret';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'Private Ip of anther instance(slave)';
SHOW MASTER STATUS \G

13. Now go to Slave server and login MySQL and use following Command and also make sure “SOURCE_LOG_FILE, SOURCE_LOG_POS, SOURCE_SSL” fill this value of master as you see last command i.e. “SHOW MASTER STATUS \G” copy that value

mysql -u root -p
CHANGE REPLICATION SOURCE TO SOURCE_HOST='Master instance private ip', SOURCE_LOG_FILE='file', SOURCE_LOG_POS=position, SOURCE_SSL=1; (enter these this as master output of first instance )                                                                                                                        2, SOURCE_SSL=1;
START REPLICA USER='repl' PASSWORD='secret';    
SHOW REPLICA STATUS \G ==>  #Replica_IO_State: Waiting for source to send event
SHOW DATABASES;