Setting Up Master-Slave Replication in MariaDB

 Step-by-Step Guide to Setting Up Master-Slave Replication in MariaDB

1. Prerequisites

Before you start, ensure the following:

• MariaDB is installed on both the master and slave servers.
• Both servers can communicate with each other (pingable via IP or hostname).
• The master and slave have unique server IDs.
• You have administrative access to both MariaDB servers.

2. Configure the Master Server

Step 2.1: Edit the MariaDB Configuration File

On the master server, edit the my.cnf  file to enable replication settings.

sudo nano /etc/my.cnf   # or /etc/mysql/my.cnf, depending on the OS

Add the following configuration under the [mysqld] section:

[mysqld]
server-id = 1  # Unique ID for the master (can be any number)
log-bin = /var/log/mysql/mariadb-bin  # Path to the binary log
binlog-format = mixed  # Binary log format (can be row, statement, or mixed)

Step 2.2: Restart MariaDB

Restart MariaDB to apply the changes:

sudo systemctl restart mariadb

Step 2.3: Create a Replication User

Create a user on the master server that the slave will use for replication. This user should have the REPLICATION SLAVE privilege:

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

'replica_user'@'%': % allows the user to connect from any host. You can restrict it to the slave's IP if preferred.

REPLICATION SLAVE: Grants the required privileges for replication.

Step 2.4: Obtain Master Log File Information

To get the binary log file and position, which will be needed for configuring the slave:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note down the File and Position values. These will be used later in the slave configuration.

Step 2.5: Unlock the Master Tables

After obtaining the binary log information, release the lock:

UNLOCK TABLES;

3. Configure the Slave Server

Step 3.1: Edit the Slave Configuration File

On the slave server, edit the my.cnf file:

sudo nano /etc/my.cnf

Add the following settings under the [mysqld] section:

[mysqld]
server-id = 2  # Unique ID for the slave
relay-log = /var/log/mysql/relay-bin  # Relay log location
log_bin = /var/log/mysql/mariadb-bin  # Optional: To make slave a potential future master

server-id: This must be unique. Since it's the slave, you can use a different ID than the master.

relay-log: The relay log is where the slave stores the incoming replication data from the master.

Step 3.2: Restart MariaDB

Restart MariaDB on the slave to apply the changes:

sudo systemctl restart mariadb

Step 3.3: Configure the Slave for Replication

Now, configure the slave with the master’s details and log file information obtained earlier:

CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='replica_user',
MASTER_PASSWORD='replica_password',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=120;

`MASTER_HOST`: The IP address or hostname of the master server. 

 `MASTER_LOG_POS`: These are the file and position noted from the master.”

Step 3.4: Start Slave Replication

Once the slave is configured, start the replication process:

START SLAVE;

Step 3.5: Verify Slave Status

To verify that the replication is working, run the following command on the slave:

SHOW SLAVE STATUS\G;

Look for the following fields:
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Seconds_Behind_Master: 0 (or a low number)

If both Slave_IO_Running and Slave_SQL_Running are Yes, the replication is set up and working correctly.

4. Monitor Replication

Step 4.1: Check Replication Health

On the slave, use the following command to check replication health:

SHOW SLAVE STATUS\G;

Monitor the Seconds_Behind_Master value. A consistent value of 0 or a low number indicates that replication is functioning properly.

Seconds_Behind_Master: If this is consistently high, it may indicate replication lag.

Last_Error: Any errors here will give you clues to troubleshoot issues with replication.

Step 4.2: Restart Slave if Necessary

If replication stops for any reason, you can restart the slave with the following commands:

STOP SLAVE;
START SLAVE;

5. Common Troubleshooting Tips

- **Check Permissions**: Ensure the replication user has REPLICATION SLAVE privileges.
- **Firewall Issues**: Ensure port 3306 is open between the master and slave.
- **Sync Issues**: If replication falls too far behind, consider re-syncing the slave.

 If the slave has fallen too far behind, you may need to reinitialize replication by re-syncing the database from the master (using mysqldump or similar).

Conclusion

Setting up master-slave replication in MariaDB is a straightforward process that provides many benefits, such as improved read scalability and high availability. By following these steps and monitoring the health of the replication, you can ensure a stable and reliable replication environment.