How to configure MySQL or MariaDB replication

Replication in MySQL or MariaDB is a mechanism that allows one or more servers to synchronize data in real-time, ensuring high availability, data redundancy, and load balancing. It uses a primary-replica architecture where the primary server sends updates to one or more replicas, which then apply the changes to maintain the same data as the primary. The primary server processes write queries, while replicas handle read queries, thus distributing the workload across multiple servers.

Setting up replication involves configuring the primary and replica servers, enabling the binary log on the primary server, and establishing a connection between the servers. This guide will walk you through the process of creating MySQL or MariaDB replication step-by-step. Note that the steps provided assume that you already have a running MySQL or MariaDB installation and necessary privileges to perform the tasks.

1. Configure the primary server by editing the my.cnf or my.ini file, adding the following lines.

   server-id=1
   log-bin=mysql-bin
   binlog-format=ROW

2. Restart the primary server to apply the changes.
3. Create a dedicated replication user on the primary server by running the following SQL query.

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

4. Obtain the primary server's binary log position by executing the following command on the primary server.

   SHOW MASTER STATUS;

5. Note the File and Position values from the output for later use.
6. Configure the replica server by editing the my.cnf or my.ini file, adding the following lines.

   server-id=2
   relay-log=relay-log
   read-only=1

7. Restart the replica server to apply the changes.
8. Import a recent database dump from the primary server into the replica server.
9. Configure the replica server to connect to the primary server by running the following SQL query on the replica server, replacing the placeholders with the appropriate values.

   CHANGE MASTER TO
       MASTER_HOST='primary_server_ip',
       MASTER_USER='replica_user',
       MASTER_PASSWORD='your_password',
       MASTER_LOG_FILE='File',
       MASTER_LOG_POS=Position;

10. Start the replication process on the replica server by executing the following command.

    START SLAVE;

11. Verify that the replication is working correctly by running the following command on the replica server.

    SHOW SLAVE STATUS\G

12. Ensure that the Slave_IO_Running and Slave_SQL_Running values are both set to Yes, indicating successful replication setup.