How to configure MySQL or MariaDB replication

MySQL and MariaDB replication keeps a secondary server synchronized with a primary dataset, enabling fast failover, read scaling, and safer maintenance windows. A healthy replica reduces recovery time after hardware failure and provides a place to run heavy read queries without impacting the primary workload.

Replication relies on the source server’s binary log (binlog) to record committed changes, while the replica reads those events and replays them locally to reach the same state. The replica starts applying events at a specific binlog file and position (or a GTID set), so the initial data on the replica must match the source at the same point in time.

Asynchronous replication is common, so lag is possible during bursts of writes, and incorrect binlog coordinates can cause missing transactions or duplicate data. Classic file/position replication provides broad compatibility across versions, requiring a consistent snapshot plus the matching binlog position for the replica start point.

1. Edit the MySQL or MariaDB server configuration file on the source host.

   $ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

2. Add replication settings under the [mysqld] section on the source host.

   [mysqld]
   server_id = 1
   log_bin = mysql-bin
   binlog_format = ROW

   If the source binds only to localhost via bind-address, set bind-address to a private interface IP reachable from the replica, and allow TCP port 3306 from the replica host in any firewall.

   Consider setting binlog_expire_logs_seconds (MySQL) or expire_logs_days (MariaDB) to prevent binlogs from filling disk.

3. Restart the database service on the source host.

   $ sudo systemctl restart mysql

   Use mariadb instead of mysql on systems where the service name is mariadb.

4. Verify that the source host is writing a binary log.

   $ mysql -u root -p -e "SHOW MASTER STATUS\G"
   Enter password:
   *************************** 1. row ***************************
                File: mysql-bin.000001
            Position: 157
        Binlog_Do_DB:
    Binlog_Ignore_DB:
   Executed_Gtid_Set:

5. Create a dedicated replication user on the source host.

   $ mysql -u root -p -e "CREATE USER 'repl'@'10.0.0.20' IDENTIFIED BY 'REPLACE_WITH_STRONG_PASSWORD';"
   Enter password:

   Prefer a replica-specific host (IP or DNS) instead of '%' to limit where the replication credentials can be used.

6. Grant replication privileges to the replication user on the source host.

   $ mysql -u root -p -e "GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.0.0.20';"
   Enter password:

7. Verify the replication user grants on the source host.

   $ mysql -u root -p -e "SHOW GRANTS FOR 'repl'@'10.0.0.20';"
   Enter password:
   Grants for repl@10.0.0.20
   GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.0.0.20'

8. Create a consistent snapshot dump on the source host.

   $ mysqldump -u root -p --all-databases --single-transaction --routines --triggers --events --master-data=2 > /tmp/mysql-replication.sql
   Enter password:

   The --single-transaction option provides a consistent snapshot for InnoDB tables without locking writes.

   Large datasets can take significant time to dump, and disk space must be sufficient for /tmp/mysql-replication.sql.

9. Copy the snapshot dump to the replica host.

   $ scp /tmp/mysql-replication.sql user@10.0.0.20:/tmp/
   mysql-replication.sql                                           100%   42MB  32.1MB/s   00:01

10. Edit the MySQL or MariaDB server configuration file on the replica host.

    $ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

11. Add replica settings under the [mysqld] section on the replica host.

    [mysqld]
    server_id = 2
    read_only = 1
    relay_log = mysql-relay-bin
    skip_slave_start = 1

    Keep the replica server_id unique across the replication topology.

12. Restart the database service on the replica host.

    $ sudo systemctl restart mysql

13. Import the snapshot dump into the replica host.

    $ mysql -u root -p < /tmp/mysql-replication.sql
    Enter password:

    Importing into a non-empty replica can overwrite or conflict with existing data, so align datasets before starting replication.

14. Extract the binlog file name with position from the dump header on the replica host.

    $ grep -m1 -E "^-- CHANGE MASTER TO MASTER_LOG_FILE" /tmp/mysql-replication.sql
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=157;

15. Stop any replication threads on the replica host.

    $ mysql -u root -p -e "STOP SLAVE;"
    Enter password:

    A message such as The slave is not running is common on first-time setup.

16. Configure the replication connection on the replica host.

    $ mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='10.0.0.10', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='REPLACE_WITH_STRONG_PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=157;"
    Enter password:

    Wrong MASTER_LOG_FILE or MASTER_LOG_POS values can break consistency, causing missing rows or duplicate-key errors.

17. Start replication on the replica host.

    $ mysql -u root -p -e "START SLAVE;"
    Enter password:

    MySQL 8.0 also accepts START REPLICA plus SHOW REPLICA STATUS\G as synonyms.

18. Verify Slave_IO_Running plus Slave_SQL_Running both show Yes on the replica host.

    $ mysql -u root -p -e "SHOW SLAVE STATUS\G"
    Enter password:
    *************************** 1. row ***************************
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
            Seconds_Behind_Master: 0
                   Last_IO_Error:
                  Last_SQL_Error:
    ##### snipped #####

19. Create a test database on the source host.

    $ mysql -u root -p -e "CREATE DATABASE repl_test;"
    Enter password:

20. Create a test table on the source host.

    $ mysql -u root -p -e "CREATE TABLE repl_test.t (id INT PRIMARY KEY) ENGINE=InnoDB;"
    Enter password:

21. Insert a test row on the source host.

    $ mysql -u root -p -e "INSERT INTO repl_test.t (id) VALUES (1);"
    Enter password:

22. Query the test row on the replica host.

    $ mysql -u root -p -e "SELECT * FROM repl_test.t;"
    Enter password:
    id
    1