Replication is a significant feature of MySQL and MariaDB, enabling the distribution of data across different servers. Through replication, data from one MySQL or MariaDB database server (the primary or “master” server) can be duplicated onto one or multiple secondary (or “slave”) servers.

This process ensures data availability, boosts performance for high-availability setups, and provides an essential backup strategy. It's especially beneficial in scenarios where you want to distribute the read load across multiple servers or maintain an off-site backup in real-time.

Configuring replication for MySQL or MariaDB entails setting up the primary server to maintain a binary log of changes and preparing the secondary server to read from that log. The secondary then applies these changes to keep its data in sync with the primary.

Steps to configure MySQL or MariaDB replication:

  1. Primary Server Configuration.
  2. Edit the MySQL or MariaDB configuration file.
    $ sudo nano /etc/mysql/my.cnf # On Ubuntu/Debian

    Configuration file paths might differ based on the Linux distribution. It could also be /etc/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf.

  3. In the [mysqld] section, append the following lines:
    log_bin = /var/log/mysql/mysql-bin.log
    server_id = 1
  4. Restart the MySQL or MariaDB service.
    $ sudo systemctl restart mysql
  5. Setting Up a Replication User.
  6. Log into MySQL/MariaDB on the primary server.
    $ mysql -u root -p
  7. Create a user for replication.
    CREATE USER 'replication'@'%' IDENTIFIED BY 'password';
  8. Grant the replication privileges.
    GRANT REPLICATION SLAVE ON . TO 'replication'@'%';
  9. Exit the MySQL/MariaDB shell.
  10. Secondary Server Configuration.
  11. Edit the MySQL or MariaDB configuration on the secondary server.
    $ sudo nano /etc/mysql/my.cnf # On Ubuntu/Debian
  12. In the [mysqld] section, set a unique server_id (different from the primary).
    server_id = 2
  13. Restart the MySQL or MariaDB service.
    $ sudo systemctl restart mysql
  14. Start Replication.
  15. On the secondary server, log into MySQL/MariaDB.
    $ mysql -u root -p
  16. Connect to the primary server as the replication user.
    CHANGE MASTER TO MASTER_HOST='primary_server_ip', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;

    Update 'primary_server_ip' and 'password' with appropriate values. MASTER_LOG_FILE and MASTER_LOG_POS values can be fetched from the primary server's bin-log.

  17. Start the slave process.
  18. Exit the MySQL/MariaDB shell.
Discuss the article:

Comment anonymously. Login not required.