MySQL Replication allows data from one MySQL database server (the primary) to be replicated to one or more MySQL database servers (the replicas). This guide covers setting up a basic Primary/Replica replication environment in MySQL 8.0, including SSL/TLS configuration, user creation for replication, and replication initiation.
Although not a requirement for replication, configuring SSL/TLS is recommended to secure replication traffic, especially since replication user passwords are transmitted in plaintext by default.
Modify MySQL Configuration:
/etc/my.cnf.d/mysql-server.cnf and add the following under [mysqld]:[mysqld]
log-bin=mysql-bin
server-id=101
plugin-load=mysql_clone.so
systemctl restart mysqld
Create Replication and Clone Users:
mysql -u root -p
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
CREATE USER 'clone_user'@'%' IDENTIFIED BY 'password';
GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'%';
FLUSH PRIVILEGES;
Modify MySQL Configuration:
/etc/my.cnf.d/mysql-server.cnf and add the following under [mysqld]:[mysqld]
log-bin=mysql-bin
relay-log=node01-relay-bin
server-id=102
plugin-load=mysql_clone.so
read_only=1
report-host=node01.srv.world
systemctl restart mysqld
Create Clone User:
CREATE USER 'clone_user'@'%' IDENTIFIED BY 'password';
GRANT CLONE_ADMIN ON *.* TO 'clone_user'@'%';
FLUSH PRIVILEGES;
Clone Data from the Primary Host:
SET GLOBAL clone_valid_donor_list = 'primary_host_ip:3306';
CLONE INSTANCE FROM 'clone_user'@'primary_host_ip:3306' IDENTIFIED BY 'password';
Configure Replication:
CHANGE MASTER TO
MASTER_HOST='primary_host_ip',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_SSL=1,
MASTER_LOG_FILE='binlog_filename',
MASTER_LOG_POS=binlog_position;
START SLAVE;
Verify Replication Status:
SHOW SLAVE STATUS\G
Check for Slave_IO_Running: Yes and Slave_SQL_Running: Yes to ensure replication is active and running correctly.