MySQL Replication Tutorial 🎯

Welcome to our comprehensive guide on MySQL Replication! This tutorial is designed to help you understand and implement MySQL replication from scratch, making it suitable for both beginners and intermediate learners. 📝

What is MySQL Replication? 💡

MySQL replication is a method of copying data from a master database server to one or more slave database servers. It's a powerful feature that enables you to maintain multiple identical copies of the database, ensuring data availability, improving performance, and providing disaster recovery options.

Why Use MySQL Replication? 📝

Data Availability: If your master server goes down, your slave servers can take over, ensuring minimal downtime.
Load Balancing: By distributing read operations across multiple slaves, you can reduce the load on the master server.
Disaster Recovery: In case of a disaster, you can quickly restore your database from a slave server.

Prerequisites 📝

A working MySQL Server (Master & Slave)
Basic understanding of SQL and MySQL commands

Setting up MySQL Replication 💡

Master Server Configuration 📝

Open your MySQL configuration file (usually my.cnf or my.ini).

bash

sudo nano /etc/mysql/my.cnf

Add the following lines at the end of the file:

bash

server-id = 1
log_bin = mysql-bin

Restart your MySQL server.

bash

sudo service mysql restart

Slave Server Configuration 📝

Open your MySQL configuration file on the slave server.

bash

sudo nano /etc/mysql/my.cnf

Add the following lines at the end of the file:

bash

server-id = 2
replicate-donald-db

Replace donald-db with the name of the database you want to replicate.

Restart your MySQL server on the slave server.

bash

sudo service mysql restart

Connecting Slave to Master 💡

On the slave server, execute the following command:

sql

CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='replication_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=4;

Replace master_ip with the IP address of the master server, replication_user and replication_password with the credentials for the replication user.

Start the slave:

sql

START SLAVE;

Verifying Replication 💡

To check if replication is working, execute the following command on the slave server:

sql

SHOW SLAVE STATUS\G;

If the output shows that the slave is running and seconds_behind_master is 0, then replication is working correctly.