Master-Slave Replication MYSQL(8.0.21)

MySQL Master-Slave Replication is a database configuration where one MySQL server (Master) shares its data changes with another server (Slave). The Slave server mirrors the Master’s data, enhancing data availability and providing backup. It’s useful for load distribution, failover, and data redundancy in applications with high availability requirements. Configuration involves setting server IDs, log files, and permissions, and replication can be tested by monitoring data consistency between the Master and Slave servers.

STEP1: MySQL service on both the Master and Slave servers.

# Check MySQL service status on Master
systemctl status mysqld
ps -ef | grep -i mysql
pidof mysqld
netstat -ntlp | grep 3306

# Get the public IP of the server
curl icanhazip.com

If you haven’t already done so, make sure to open port 3306 on the Master server to allow communication.

MASTER SERVER CONFIGURATION

STEP2: On the Master server, set a custom command prompt for clarity and open the MySQL configuration file.

vi /etc/my.cnf

STEP3: Inside the MySQL configuration file

server-id=1
log_bin=mysql-bin

Save the changes and exit the editor with Esc, :wq!.

STEP4: Now, restart the MySQL service and log in as the root user

systemctl restart mysqld
mysql -uroot -pAdmin@123

STEP5: Create a new user for replication and grant the necessary privileges

CREATE USER 'replica'@'18.191.186.27' IDENTIFIED with mysql_native_password BY 'Test@123';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'18.191.186.27';
flush privileges;
show master status\G

Note the file and position values; you’ll need them for the Slave configuration.

SLAVE SERVER CONFIGURATION

STEP6: On the Slave server, set a custom command prompt and edit the MySQL configuration

vi /etc/my.cnf

STEP7: Inside the MySQL configuration file

server-id=2
log_bin=mysql-bin

Save the changes and exit the editor with Esc, :wq!.

STEP8: Now, restart the MySQL service and log in as the root user

systemctl restart mysqld
mysql -uroot -pAdmin@123

STEP9: Configure the Slave to replicate from the Master

CHANGE MASTER TO MASTER_HOST = '18.219.224.177',
MASTER_USER = 'replica',
MASTER_PASSWORD = 'Test@123',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 857;
start slave;

show slave status\G
show databases;

Verifying Replication

STEP10: To ensure that replication is working correctly on both servers, you can execute the following commands.

MASTER

mysql -uroot -pAdmin@123
show databases;
create database replication;
show databases;

SLAVE

mysql -uroot -pAdmin@123
show databases;

This will verify that the databases on the Master server are being replicated to the Slave server successfully