How to configure MySQL or MariaDB replication

Replication is a powerful feature in MySQL and MariaDB that allows data to be copied from one server (the master) to another (the slave). This feature ensures data availability and redundancy, providing a safety net in case of failure and improving system performance by distributing read operations across multiple servers. Replication is a key component in high-availability and load-balanced setups.

By logging all changes on the master server and having the slave server apply these changes, you can keep databases synchronized. This setup not only improves the read capacity of your infrastructure but also acts as a real-time backup solution. As a result, replication reduces downtime and protects against data loss.

Setting up replication involves configuring both the master and slave servers. The master server needs to maintain a binary log of all changes, while the slave must be able to read and apply these logs to stay in sync. Additionally, a replication user with specific privileges must be created to handle the communication between the servers.

1. Edit the configuration file on the master server.

   $ sudo nano /etc/mysql/my.cnf # On Ubuntu/Debian

   Common paths include /etc/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf depending on your distribution.

2. Add the following lines to the [mysqld] section.

   log_bin = /var/log/mysql/mysql-bin.log
   server_id = 1

3. Restart the MySQL or MariaDB service.

   $ sudo systemctl restart mysql

4. Log in to MySQL or MariaDB on the master server.

   $ mysql -u root -p

5. Create a replication user.

   CREATE USER 'replication'@'%' IDENTIFIED BY 'password';

6. Grant replication privileges to the new user.

   GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

7. Exit the MySQL or MariaDB shell.

   exit;

8. Edit the configuration file on the slave server.

   $ sudo nano /etc/mysql/my.cnf # On Ubuntu/Debian

9. Set a unique server ID for the slave in the [mysqld] section.

     server_id = 2

   Ensure the slave's server ID is different from the master server.

10. Restart the MySQL or MariaDB service.

    $ sudo systemctl restart mysql

11. Log in to MySQL or MariaDB on the slave server.

    $ mysql -u root -p

12. Configure the slave to connect to the master.

      CHANGE MASTER TO MASTER_HOST='primary_server_ip', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;

    Replace 'primary_server_ip' with the master server's IP. Retrieve MASTER_LOG_FILE and MASTER_LOG_POS from the master’s binary log.

13. Start the replication process on the slave server.

    START SLAVE;

14. Verify the replication status.

    SHOW SLAVE STATUS\G;

    Ensure that Slave_IO_Running and Slave_SQL_Running are both 'Yes' to confirm replication is working.

15. Exit the MySQL or MariaDB shell.

    exit;