How to set up Database Replication in MySQL/MariaDB March 7, 2025

This guide explains how to set up database replication in MySQL/MariaDB. Database replication in MySQL/MariaDB is a process that lets data from one database (Master) be replicated to another database (Slave). This improves performance, scalability, and reliability.

Prerequisites:

• MySQL/MariaDB is installed on both the master and slave servers.
• Root access to both servers.
• Firewall rules configured to permit replication traffic.
• The same database structure on both servers.

Step 1: Configure the Master server

1. Edit MySQL Configuration File:
   1. Open the MySQL/MariaDB configuration file on the master server:

      sudo nano /etc/mysql/my.cnf

      Or for MariaDB:

      sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

   2. Add or modify the following lines:

      [mysqld]
      bind-address = 0.0.0.0
      server-id = 1
      log_bin = /var/log/mysql/mysql-bin.log
      binlog_do_db = your_database_name

   3. Save and exit.
2.  Restart MySQL Service:

   sudo systemctl restart mysql

3. Create a Replication User:
   1. Log in to MySQL:

      mysql -u root -p

   2. Run the following SQL commands:

      CREATE USER 'replica'@'%' IDENTIFIED BY 'your_password';
      GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
      FLUSH PRIVILEGES;
      SHOW MASTER STATUS;

   3. Take note of the File and Position values from the output.

Step 2: Configure the Slave Server

1. Edit MySQL Configuration File:
   1. Open the MySQL/MariaDB configuration file on the slave server:

      sudo nano /etc/mysql/my.cnf

      Or for MariaDB:

      sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

   2. Modify the following settings:

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

   3. Save and exit.
2. Restart MySQL Service:

   sudo systemctl restart MySQL

3. Set Up Replication on the Slave:
   1.  Log in to MySQL:

      mysql -u root -p

   2. Run the following SQL command, replacing MASTER_LOG_FILE and MASTER_LOG_POS with the values from the master:

      CHANGE MASTER TO
      MASTER_HOST='master_server_ip',
      MASTER_USER='replica',
      MASTER_PASSWORD='your_password',
      MASTER_LOG_FILE='mysql-bin.000001',
      MASTER_LOG_POS=154;
      START SLAVE;
      SHOW SLAVE STATUS\G;

Step 3: Verify Replication:

1. On the slave server, check the replication status:

   SHOW SLAVE STATUS\G;

2. Ensure Slave_IO_Running and Slave_SQL_Running are both Yes.

We have successfully set up MySQL/MariaDB replication, which guarantees data redundancy and improves database accessibility. One should frequently monitor the replication status and logs to avoid any sync issues.

For further queries, seek assistance from our support staff at your earliest.

Need to import a MySQL/MariaDB database? Check out our step-by-step guide How to import a MySQL/MariaDB database