MySQL 8.0 Master-Slave Replication Setup

Replication enables data from one MySQL database server (the master) to be copied to one or more other database servers (the slaves). This tutorial guides you through setting up binary log replication.

1. Configure the Master

Edit the my.cnf file on the Master server to enable binary logging and set a unique server ID.

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydb

Restart the MySQL service to apply changes.

2. Create Replication User

Log in to the Master and create a user that the slave will use to connect.

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;

3. Obtain Master Coordinates

Lock the tables to get a consistent snapshot position.

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

4. Configure the Slave

On the Slave server, edit my.cnf and set a unique ID (different from Master).

[mysqld]
server-id = 2

5. Start Replication

On the Slave, configure the master connection details.

CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='replica_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

START SLAVE;
SHOW SLAVE STATUS\G;

Summary

If Slave_IO_Running and Slave_SQL_Running are both "Yes", your replication is successfully established!