MySQL Replication Guide – Step by Step

---

Setting Up MySQL Replication: A Step-by-Step Guide

MySQL replication is a powerful feature that allows you to maintain multiple copies of your database across different servers, ensuring high availability, load balancing, and redundancy. In this guide, we’ll walk you through the process of setting up MySQL replication from a master server to one or more slave servers.

Prerequisites

Before we start, ensure you have the following:

1. MySQL installed on both master and slave servers.
2. Root access to both servers.

Step 1: Configure the Master Server

Edit the MySQL Configuration File

Note: The following path may be different based on OS or your local settings.

Open your MySQL configuration file (my.cnf, 50-server.cnf on Linux or my.ini on Windows) and add the following line:

   server-id = 1
   log-bin = /var/log/mysql-bin.log
   binlog-do-db = your_database_name

• server-id: A unique identifier for the server. Each server in the replication setup must have a different ID.
• log-bin: Enables binary logging, which is essential for replication.
• binlog-do-db: Specifies the database to replicate. Remove this line if you want to replicate all databases.

Restart MySQL

Apply the configuration changes by restarting MySQL:

   sudo systemctl restart mysql

Create a Replication User

Create a user specifically for replication. Login into your MySql.

   MYSQL -u root -P

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

Lock the Tables and Get the Binary Log Coordinates

Lock the tables and retrieve the current binary log coordinates:

   FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS;

Note down the File and Position values from the SHOW MASTER STATUS output. You’ll need these for the slave configuration.

Export the Database

Export the database from the master server:

   mysqldump -u root -p --databases [your_database_name] > database_dump.sql

Unlock the Tables

Unlock the tables after the export is complete:

   UNLOCK TABLES;

Step 2: Configure the Slave Server

Import the Database Dump

Copy the database dump file to the slave server and import it:

   mysql -u root -p [your_database_name] < database_dump.sql

Edit the MySQL Configuration File on the Slave

Open the MySQL configuration file on the slave server and add the following settings:

   [mysqld]
   server-id = 2
   log-bin = /var/log/mysql-bin.log
   relay-log = /var/log/mysql/mysql-relay-bin.log

• server-id: A unique identifier for the slave server.
• relay-log: Specifies the base name and location of the relay log files.

Restart MySQL on the Slave

Apply the changes by restarting MySQL:

   sudo systemctl restart mysql

Configure the Slave to Replicate from the Master

Set up the slave to start replicating from the master. Login into MySQL Database Server and run the following command:

   CHANGE MASTER TO
       MASTER_HOST='master_host_ip',
       MASTER_USER='replica_user',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='mysql-bin.000001',
       MASTER_LOG_POS=position;
Note: Use the value from the master status for MASTER_LOG_FILE and MASTER_LOG_POS.

Start the Slave Process

Start the replication process on the slave server:

   START SLAVE;

Verify the Slave Status

Check the replication status to ensure everything is working correctly:

   SHOW SLAVE STATUS\G;

Ensure Slave_IO_Running and Slave_SQL_Running are both Yes. If there are any errors, troubleshoot and resolve them as necessary.

Conclusion

Setting up MySQL replication involves configuring the master server to log changes and the slave server to apply those changes. This guide covered the essential steps to get you started with MySQL replication:

• Configure the master server for binary logging.
• Create a replication user.
• Export and import the database.
• Configure and start replication on the slave server.

MySQL replication can be further customized and scaled according to your needs, providing a robust solution for high availability and disaster recovery. For more advanced configurations, such as multi-source replication or semi-synchronous replication, refer to the official MySQL documentation.