mysql_logo

MySQL replication is a critical feature for data redundancy, high availability, and scalability. Here's a concise overview:

📌 Types of Replication

  1. Asynchronous Replication

    • Master writes to binlog, slave fetches updates after some delay.
    • async_replication

  2. Semi-Synchronous Replication

    • Ensures at least one slave acknowledges the transaction before committing on the master.
    • semi_sync_replication

  3. Synchronous Replication

    • All slaves must acknowledge the transaction before it is considered committed.
    • sync_replication

🧰 Configuration Steps

  1. Enable Binary Logging

    • Modify my.cnf or my.ini: 
      [mysqld]  
log-bin=mysql-bin  
server-id=1

  2. Create Replication User

    • On the master: 
      CREATE USER 'repl'@'%' IDENTIFIED BY 'password';  
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

  3. Set Master Info

    • On the slave: 
      CHANGE MASTER TO  
MASTER_HOST='master_ip',  
MASTER_USER='repl',  
MASTER_PASSWORD='password',  
MASTER_LOG_FILE='mysql-bin.0001',  
MASTER_LOG_POS=1234;

  4. Start Replication

    • Execute: 
      START SLAVE;

  5. Verify Status

    • Check: 
      SHOW SLAVE STATUS\G
    • Ensure Slave_IO_Running and Slave_SQL_Running are both Yes.

📚 Further Reading

For detailed setup instructions, visit our MySQL Setup Guide.

mysql_replication_flow
*Note: Always ensure network security and proper authentication when configuring replication.*