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.