MySQL Replication (M-W)

The key benefits of master-slave replication are:

• Resiliency. Slaves act as hot backups, ready to become the new master in case of a primary server failure. This setup minimizes downtime and data loss.

• Scalability. Read queries can be distributed across slaves, offloading the master and facilitating the handling of increased loads.

• Disaster recovery. Slaves safeguard data against hardware failures or catastrophic events, ensuring quick recovery.

---

MySQL master-slave replication is a process that involves copying data from a MySQL "master" server to one or more "slave" servers. This allows for data redundancy, scalability, and high availability, where the slave servers maintain a real-time copy of the master’s data.

Here's an overview of how MySQL master-slave replication works and how to set it up:

Steps for Setting Up MySQL Master-Slave Replication:

1. Prepare the Master Server:

• Ensure binary logging is enabled on the master server.

• Assign a unique server ID.

• Create a replication user with the necessary privileges.

Configuration on the Master (my.cnf or my.ini):

Edit the my.cnf or my.ini file on the master server:

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = your_database  # Optional, specify databases to replicate

• server-id: A unique ID for each server in the replication setup (must be different for each server).

• log-bin: Enables binary logging, which is essential for replication.

• binlog-do-db: (Optional) Specifies which databases to replicate. This is useful if you only want to replicate specific databases.

Restart MySQL:

sudo systemctl restart mysql

2. Create a Replication User on the Master:

Login to the MySQL master server:

mysql -u root -p

Then, create a user that the slave will use to connect to the master:

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

3. Get the Master’s Binary Log Position:

You need to capture the current position of the binary log to start replication from that point. Run the following command on the master server:

SHOW MASTER STATUS;

This will return output like this:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 12345    | my_database  |                  |
+------------------+----------+--------------+------------------+

• File: The binary log file name.

• Position: The position in the binary log.

Take note of the File and Position values, as these will be needed on the slave server.

4. Prepare the Slave Server:

• Ensure that the slave server has a unique server-id.

• Copy the database or data from the master server to the slave.

Configuration on the Slave (my.cnf or my.ini):

Edit the my.cnf or my.ini file on the slave server:

[mysqld]
server-id = 2  # Unique ID different from master

Restart MySQL:

sudo systemctl restart mysql

5. Set Up Replication on the Slave:

Login to the MySQL slave server:

mysql -u root -p

Set the master connection information and start replication:

CHANGE MASTER TO
    MASTER_HOST = 'master_ip',
    MASTER_USER = 'replica_user',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',  -- File from master
    MASTER_LOG_POS = 12345,  -- Position from master
    
START SLAVE;

• MASTER_HOST: IP address of the master server.

• MASTER_USER: The replication user created on the master.

• MASTER_PASSWORD: The password for the replication user.

• MASTER_LOG_FILE: The binary log file from the master.

• MASTER_LOG_POS: The binary log position from the master.

6. Verify Replication:

You can check the replication status on the slave server with the following command:

SHOW SLAVE STATUS\G