Skip to content
DBAExperts
MySQL High availability

How to set up replication in MySQL for high availability

Replication keeps one or more replicas that copy the changes from a primary server. It provides fault tolerance, scaled-out reads, and backups without impacting production. In MySQL 8.0 the recommended approach is GTID-based replication for its robustness during failover.

  1. 1

    Prepare the primary server

    Enable the binlog, GTID, and a unique server_id. These parameters go in my.cnf and require a restart.

    [mysqld]
server_id = 1
log_bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
  2. 2

    Create the replication user on the primary

    The replica connects with this user. It only needs the REPLICATION SLAVE permission.

    CREATE USER 'repl'@'10.0.%.%' IDENTIFIED BY 'clave_repl_fuerte';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%.%';
  3. 3

    Configure the replica and start it

    With GTID you do not need to note binlog positions: SOURCE_AUTO_POSITION handles it. First you must seed the replica with a consistent copy of the primary.

    CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.1',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'clave_repl_fuerte',
  SOURCE_AUTO_POSITION = 1;
START REPLICA;
  4. 4

    Verify the replica status

    Both threads should say Yes and Seconds_Behind_Source should be 0 or close to it. Any error shows up in Last_Error.

    SHOW REPLICA STATUS\G

// common mistake

Replication is NOT a backup: if you delete data by mistake on the primary, that DELETE is replicated to the replica instantly. Also, asynchronous replication can lose the last few transactions if the primary crashes; for zero loss you need semisynchronous replication or a group cluster.

// when it's worth an expert

Designing real high availability (automatic failover, no data loss, controlled split-brain) has a lot of fine details. At dba.mx we implement topologies with tested failover and monitoring, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.