How to configure MySQL native replication for high availability

MySQL native replication keeps a second server continuously updated from the primary, reducing recovery time after an outage and enabling read scaling by routing read-only traffic to a replica.

Replication works by writing every transaction on the source (master) to the binary log, then streaming those events to the replica (slave) where separate I/O and SQL threads apply them.

Native replication is typically asynchronous, so a sudden failure can still lose the last transactions that were not yet applied on the replica, and automated failover requires separate tooling or runbooks. Commands use MySQL 8.0+ on Ubuntu or Debian, where configuration commonly lives in /etc/mysql/mysql.conf.d/mysqld.cnf and the systemd unit is mysql.service.

1. Decide on a private source (master) address reachable from the replica (slave) on TCP port 3306.

   Exposing mysqld on the network without a firewall can leak data and credentials; restrict access to the replica host and trusted admin networks.

2. Edit the source (master) MySQL configuration to enable the binary log with a unique server-id.

   [mysqld]
   server-id = 1
   bind-address = 192.0.2.40
   log_bin = mysql-bin
   binlog_format = ROW

   On Ubuntu and Debian packages, configuration commonly lives in /etc/mysql/mysql.conf.d/mysqld.cnf.

3. Restart the mysql service on the source server.

   $ sudo docker restart sg-mysql-primary
   sg-mysql-primary

4. Confirm the mysql service is active on the source server.

   $ docker ps --filter name=sg-mysql-primary --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}'
   NAMES              IMAGE       STATUS
   sg-mysql-primary   mysql:8.0   Up 7 minutes

5. Verify that log_bin is enabled on the source server.

   $ sudo mysql -e "SHOW VARIABLES LIKE 'log_bin';"
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | log_bin       | ON    |
   +---------------+-------+

6. Create a replication user on the source server restricted to the replica host.

   $ mysql -u root -p -e "CREATE USER 'repl'@'192.0.2.41' IDENTIFIED BY 'ReplPass123!'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.0.2.41'; FLUSH PRIVILEGES;"

   A host-specific account is safer than '%' for replication credentials.

7. Create a consistent snapshot on the source server with embedded binary log coordinates.

   $ mysqldump --single-transaction --routines --events --triggers --master-data=2 --all-databases --result-file=/root/sg-work/replication/mysql-replication.sql

   --single-transaction is intended for transactional engines like InnoDB.
   For a single application database, replace --all-databases with --databases appdb.

8. Confirm the snapshot file exists on the source server.

   $ ls -lh /root/sg-work/replication/mysql-replication.sql
   -rw-r--r-- 1 root root 3.7M Dec 25 01:41 /root/sg-work/replication/mysql-replication.sql

9. Copy the snapshot file to the replica server.

   $ docker cp sg-mysql-primary:/root/sg-work/replication/mysql-replication.sql /root/sg-work/replication/
   $ docker cp /root/sg-work/replication/mysql-replication.sql sg-mysql-replica:/root/sg-work/replication/mysql-replication.sql

10. Edit the replica (slave) MySQL configuration with a unique server-id plus relay log settings.

    [mysqld]
    server-id = 2
    relay_log = relay-bin
    relay_log_recovery = ON
    read_only = ON

    Enable super_read_only after the initial import to prevent privileged users from writing to the replica.

11. Restart the mysql service on the replica server.

    $ sudo docker restart sg-mysql-replica
    sg-mysql-replica

12. Confirm the mysql service is active on the replica server.

    $ docker ps --filter name=sg-mysql-replica --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}'
    NAMES              IMAGE       STATUS
    sg-mysql-replica   mysql:8.0   Up 8 minutes

13. Import the snapshot into the replica server.

    $ mysql -u root -p < /root/sg-work/replication/mysql-replication.sql

    Importing replaces existing objects in the dumped databases; run on a fresh instance or ensure replacement is acceptable.

14. Extract the source binary log file and position from the snapshot.

    $ grep -m 1 -E "^-- CHANGE MASTER TO " /root/sg-work/replication/mysql-replication.sql
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=727;

    Starting replication from the wrong file or position can cause missing rows or duplicate apply errors.

15. Configure replication source parameters on the replica server.

    $ mysql -u root -p -e "CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.0.2.40', SOURCE_USER='repl', SOURCE_PASSWORD='ReplPass123!', SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=727, GET_SOURCE_PUBLIC_KEY=1;"

    Older MySQL releases accept CHANGE MASTER TO syntax.

16. Start replication on the replica server.

    $ mysql -u root -p -e "START REPLICA;"

    Older MySQL releases accept START SLAVE syntax.

17. Verify that replication threads are running with no reported errors.

    $ mysql -u root -p -e "SHOW REPLICA STATUS\\G" | grep -E "Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source|Last_IO_Error|Last_SQL_Error"
    Replica_IO_Running: Yes
    Replica_SQL_Running: Yes
    Seconds_Behind_Source: 0
    Last_IO_Error:
    Last_SQL_Error:

18. Create a small test database with a test table on the source server.

    $ mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS ha_test; CREATE TABLE IF NOT EXISTS ha_test.t (id INT PRIMARY KEY AUTO_INCREMENT, note VARCHAR(64));"

19. Insert a test row on the source server.

    $ mysql -u root -p -e "INSERT INTO ha_test.t (note) VALUES ('replication-ok');"

20. Query the test table on the replica server.

    $ mysql -u root -p -e "SELECT * FROM ha_test.t;"
    id	note
    1	replication-ok