MySQL DB Replication On CentOS & RHEL

Prerequisites:

Ensure both master and slave servers have the same version of MySQL installed.

Allocate disk space on both servers at least three times the size of the database.

Ensure that the same configuration files (my.cnf) are present on both the master and slave servers.

On the Master Server:

• Locate and Edit my.cnf:

sudo vi /etc/my.cnf

• Add the below in my.cnf:

server-id = 106

log_bin = /var/lib/mysql/mysql-bin.log

expire_logs_days = 7

log_slave_updates

binlog_format = MIXED

binlog_row_image = MINIMAL

• Restart MySQL Service:

sudo systemctl restart mysqld

• Verify Binary Logging:

mysql -u username -ppassword

show variables like '%log_bin%';

show variables like '%binlog%';

show variables like '%expire%';

On the Slave Server:

• Install Required Packages:

yum install screen

• Install MyDumper:

release=$(curl -Ls -o /dev/null -w %{url_effective} https://github.com/mydumper/mydumper/releases/latest | cut -d'/' -f8)

yum install https://github.com/mydumper/mydumper/releases/download/${release}/mydumper-${release:1}.el7.x86_64.rpm

yum install https://github.com/mydumper/mydumper/releases/download/${release}/mydumper-${release:1}.el8.x86_64.rpm

• Create Backup:

mkdir backup

cd backup/

screen -S backup

mydumper -h 172.16.104.54 -u admin -a -o /root/backup -c -t 8 --build-empty-files --events --routines --triggers -v 3

• Edit Slave's my.cnf:

sudo nano /etc/my.cnf

• Add the below in my.cnf:

server-id = 49

• Restart MySQL Service:

sudo systemctl restart mysqld

• Restore Master DB on Slave:

myloader -u username -a -o -t 8 -v 3 -d /root/backup

• Create Replication User on Master:

mysql -u username -ppassword

create user 'repl'@'SLAVE_DB_IP' IDENTIFIED WITH mysql_native_password BY 'PASS_FOR_repl_USER';

grant replication slave on . to 'repl'@'SLAVE_DB_IP';

• Configure Replication on Slave:

mysql -u username -ppassword

change master to master_host='MASTER_DB_IP', master_user='repl', master_password='PASS_OF_repl_USER', master_log_file='mysql-bin.000095', master_log_pos=785209519;

Note: You will get master_log_file & master_log_pos from the metadata file which will be present in the backup directory of slave server.

start slave;

• Verify Replication Status:

show slave status\G;

• Ensure Replication is Running:

Check Slave_IO_Running and Slave_SQL_Running should be YES.

Seconds_Behind_Master should ideally be 0.