MySQL Master-Slave Replication: Ensuring Data Security and Redundancy

MySQL replication is a powerful feature that allows you to automatically copy data from one MySQL database server to one or more MySQL servers. One of the most common replication topologies is the Master/Slave topology, where one database server acts as the master, and one or more servers act as slaves. This topology is ideal for scenarios like deploying read replicas for read scaling, live database backup for disaster recovery, and running analytics jobs.

In this guide, we’ll walk through the step-by-step setup of MySQL Master-Slave replication.

Prerequisites:

Before we begin, make sure you have the following prerequisites:

Access to two servers: one for the master and one for the slave.
MySQL installed on both servers.
Basic knowledge of MySQL configuration.

Setup:

Roles and Hosts:

Master: 192.168.1.51 (Linux2), [OEL 6.5, MySQL Server Version 8.0.19]
Slave: 192.168.1.61 (Linux3), [OEL 6.5, MySQL Server Version 8.0.19]

Step 1: Configure the Master Server:

Edit MySQL Configuration:

cp /etc/my.cnf /etc/my.cnf_bkp_5may2020
vi /etc/my.cnf

Add the following lines:

bind-address = "YOUR SLAVE HOST"
server-id = 1
log_bin = mysql-bin

2. Restart MySQL Service:

service mysqld restart

3. Create Replication User:

mysql -u root -p

SHOW MASTER STATUS\G

CREATE USER 'YOUR USER'@'YOUR HOSTNAME' IDENTIFIED WITH mysql_native_password BY 'YOUR PASSWORD;
GRANT REPLICATION SLAVE ON *.* TO 'YOUR USER'@'YOUR HOSTNAME';

Step 2: Configure the Slave Server:

Edit MySQL Configuration:

cp /etc/my.cnf /etc/my.cnf_org
vi /etc/my.cnf

Add the following lines:

bind-address = "YOUR MASTER HOST"
server-id = 2
log_bin = mysql-bin

2. Restart MySQL Service:

service mysqld restart

Step 3: Configure Slave Parameters:

Connect to MySQL:

mysql -uroot -p

2. Configure Slave:

STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='YOUR MASTER HOST',
MASTER_USER='YOUR USER FROM MASTER DB',
MASTER_PASSWORD='YOUR PASSWORD FROM MASTER DB',
MASTER_LOG_FILE='mysql-bin.000023',
MASTER_LOG_POS=686;

3. Start Slave:

start slave;
show slave status\G;

Testing:

Create a database and table on the master server, and verify if the data is replicated to the slave server.

Following these steps, you can successfully set up MySQL Master-Slave replication for your environment, allowing for efficient data distribution and redundancy.

Feel free to customize the configurations and parameters based on your requirements and environment. Happy replicating!