MySQL replication is a critical feature for data redundancy, scalability, and high availability. Below is a step-by-step setup guide for configuring master-slave replication:

1. Prerequisites 📋

  • Ensure both master and slave servers are running MySQL 5.6 or later.
  • Verify network connectivity between the servers.
  • Confirm the same version of MySQL is installed on both nodes.

2. Configuration Steps ⚙️

Master Server Setup

  1. Edit MySQL Configuration
    Open my.cnf or my.ini and add: 

    server-id=1
log-bin=mysql-bin
binlog-format=ROW
    MySQL Server

  2. Create Replication User
    Run the following SQL command on the master: 

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

  3. Lock Tables for Backup
    Execute: 

    FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

    Note the File and Position values for later use.

Slave Server Setup

  1. Edit MySQL Configuration
    Add to my.cnf or my.ini: 

    server-id=2
    Replication Configuration

  2. Start Replication
    Use the master's File and Position to configure: 

    CHANGE MASTER TO
MASTER_HOST='master_host_ip',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1234;
START SLAVE;

  3. Verify Replication Status
    Check with: 

    SHOW SLAVE STATUS\G

    Ensure Slave_IO_Running and Slave_SQL_Running are both Yes.

3. Common Issues ⚠️

  • Connection Errors: Check firewall rules and MySQL user permissions.
  • Data Mismatch: Ensure the slave starts replication after a full backup.
  • Authentication Failures: Confirm the password and user privileges.

4. Further Reading 📚

5. Visual Overview 📈

Data Transmission
*Replication process flow between master and slave nodes.*

For production environments, consider using tools like Percona XtraBackup for automated backups. Always test configurations in a staging setup before applying to live systems.