MySQL and MariaDB Replication

Asynchronous source/replica replication using GTIDs (global transaction IDs), how to watch lag, and how to fail over. MySQL 8.4 and MariaDB use different GTID implementations and slightly different commands, so each has its own section. Basics are in MySQL and MariaDB Cheat-Sheet.

Documentation: MySQL 8.4 Replication · CHANGE REPLICATION SOURCE TO · MariaDB Replication · MariaDB CHANGE MASTER TO · MariaDB GTID

Verification: CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION=1 and GET_SOURCE_PUBLIC_KEY (MySQL 8.4), MariaDB MASTER_USE_GTID=slave_pos and that SHOW SLAVE STATUS and SHOW REPLICA STATUS are interchangeable in MariaDB were checked against the official docs. Not verified against the docs: the status field names, the MariaDB version that added the REPLICA aliases, the config examples and the failover procedures (they follow the documentation as known). No command was executed. Terminology changed: MySQL 8.4 uses SOURCE/REPLICA (the old CHANGE MASTER TO and START SLAVE were removed in 8.4), MariaDB still accepts MASTER/SLAVE.

See also: MySQL · MariaDB Cheat-Sheet · PostgreSQL Backup and Replication · Docker · UFW (uncomplicated firewall) · Linux

Concepts #

Replication is asynchronous: the source does not wait for the replica, so a failover can lose the last transactions. Semi-synchronous plugins and Galera (MariaDB) or Group Replication / InnoDB Cluster (MySQL) exist for stronger guarantees. Replication is not a backup: a DROP TABLE replicates. See also the MySQL backup commands.

MySQL 8.4 (GTID) #

1. Configure the Source #

/etc/mysql/mysql.conf.d/replication.cnf (or my.cnf):

[mysqld]
server-id                = 1
log_bin                  = mysql-bin
binlog_format            = ROW
gtid_mode                = ON
enforce_gtid_consistency = ON
binlog_expire_logs_seconds = 604800
bind-address             = 0.0.0.0

Restart, then create a replication user:

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY '<PASSWORD>';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';

(REPLICATION SLAVE is still the privilege name.) Open port 3306 only to the replica (UFW (uncomplicated firewall)).

2. Configure the Replica #

[mysqld]
server-id                = 2
log_bin                  = mysql-bin
gtid_mode                = ON
enforce_gtid_consistency = ON
read_only                = ON
super_read_only          = ON
relay_log                = relay-bin
log_replica_updates      = ON

Restart.

3. Seed the Replica With Data #

For an empty source, skip this. Otherwise take a consistent dump with GTIDs from the source and load it on the replica:

# on the source
mysqldump -u root -p --all-databases --single-transaction --routines --triggers \
  --source-data=2 --set-gtid-purged=ON > seed.sql
# on the replica
mysql -u root -p < seed.sql

--set-gtid-purged=ON writes SET @@GLOBAL.GTID_PURGED=... into the dump so the replica starts from the right place. Clone plugin (CLONE INSTANCE FROM) or Percona XtraBackup are faster for large databases. On the replica run RESET BINARY LOGS AND GTIDS; first if it is not new.

4. Point the Replica at the Source #

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '<SOURCE-HOST>',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = '<PASSWORD>',
  SOURCE_AUTO_POSITION = 1,
  GET_SOURCE_PUBLIC_KEY = 1;
START REPLICA;

GET_SOURCE_PUBLIC_KEY=1 lets the caching_sha2_password login work without TLS. Prefer SOURCE_SSL=1 on untrusted networks. The replica threads must be stopped (STOP REPLICA;) when you run CHANGE REPLICATION SOURCE TO.

5. Check Status #

SHOW REPLICA STATUS\G

MariaDB (GTID) #

MariaDB GTIDs look like domain-server_id-sequence (for example 0-1-1234) and are tracked per domain. They work differently from MySQL GTIDs, so SOURCE_AUTO_POSITION does not exist: you use MASTER_USE_GTID.

1. Source #

/etc/mysql/mariadb.conf.d/60-replication.cnf:

[mysqld]
server_id         = 1
log_bin           = /var/log/mysql/mariadb-bin
binlog_format     = ROW
log_slave_updates = ON
expire_logs_days  = 7
bind-address      = 0.0.0.0

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY '<PASSWORD>';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';

(On MariaDB 10.5.1+ the privilege is also named REPLICATION REPLICA, and REPLICATION SLAVE still works.)

2. Replica #

[mysqld]
server_id         = 2
log_bin           = /var/log/mysql/mariadb-bin
read_only         = ON
relay_log         = relay-bin
log_slave_updates = ON

3. Seed #

# on the source
mariadb-dump --all-databases --single-transaction --routines --triggers --master-data=2 --gtid > seed.sql
# on the replica
mariadb < seed.sql

The dump comment holds the GTID, or on the replica SELECT @@gtid_slave_pos;. Set it before starting: SET GLOBAL gtid_slave_pos = '0-1-1234';.

4. Connect #

CHANGE MASTER TO
  MASTER_HOST='<SOURCE-HOST>',
  MASTER_USER='repl',
  MASTER_PASSWORD='<PASSWORD>',
  MASTER_USE_GTID=slave_pos;
START SLAVE;

MASTER_USE_GTID values: slave_pos (the replica’s own position, normal), current_pos (use after promotion, so the replica’s own writes are accounted for), no (classic file and position).

5. Check Status #

SHOW REPLICA STATUS\G      -- same output as SHOW SLAVE STATUS

Also: SELECT @@gtid_current_pos, @@gtid_slave_pos, @@gtid_binlog_pos;. SHOW ALL SLAVES STATUS lists multi-source setups.

Monitoring Lag #

Typical causes of growing lag: single-threaded apply of heavy transactions (MySQL: replica_parallel_workers, MariaDB: slave_parallel_threads and slave_parallel_mode), long-running ALTER or batch jobs, slow disk on the replica, tables without primary keys (row events scan the whole table), network.

Common Errors #

Failover and Switchover #

Planned switchover (no data loss):

1. Stop writes on the source: SET GLOBAL read_only=ON; SET GLOBAL super_read_only=ON; (MySQL). FLUSH TABLES WITH READ LOCK; also works. Stop application writes first.
2. Wait until the replica has caught up: lag 0, and the GTID sets match.
3. On the replica: STOP REPLICA; (STOP SLAVE), then RESET REPLICA ALL; (RESET SLAVE ALL), and turn read-only off: SET GLOBAL super_read_only=OFF; SET GLOBAL read_only=OFF;.
4. Repoint applications to the new source (DNS, proxy, config).
5. Turn the old source into a replica: CHANGE REPLICATION SOURCE TO ... pointing at the new source (MySQL SOURCE_AUTO_POSITION=1; MariaDB MASTER_USE_GTID=current_pos), START REPLICA.

Unplanned failover (source is dead):

1. Choose the most up-to-date replica (compare Retrieved_Gtid_Set / Executed_Gtid_Set, or MariaDB Gtid_IO_Pos).
2. Let it apply its relay log: wait until Replica_SQL_Running_State says it has read all relay log.
3. Promote as above and repoint the other replicas and the application.
4. When the old source returns, do not put it back as is. It may hold transactions the new source lacks. Compare GTIDs and re-seed it as a replica.

Expect to lose recent transactions with asynchronous replication.

Automation: Orchestrator (MySQL and MariaDB topology manager), MaxScale (MariaDB proxy with automatic failover), ProxySQL (query routing), MySQL Router and InnoDB Cluster, Galera Cluster (MariaDB, multi-primary, synchronous-style certification). For a homelab, a documented manual switchover plus backups is often enough.

Docker Example #

services:
  mysql-source:
    image: mysql:8.4
    command: >
      --server-id=1 --log-bin=mysql-bin --gtid-mode=ON --enforce-gtid-consistency=ON
    environment:
      MYSQL_ROOT_PASSWORD: <PASSWORD>
    volumes: [source-data:/var/lib/mysql]
  mysql-replica:
    image: mysql:8.4
    command: >
      --server-id=2 --log-bin=mysql-bin --gtid-mode=ON --enforce-gtid-consistency=ON
      --read-only=ON --super-read-only=ON --log-replica-updates=ON
    environment:
      MYSQL_ROOT_PASSWORD: <PASSWORD>
    volumes: [replica-data:/var/lib/mysql]
volumes:
  source-data:
  replica-data:

Then run the CREATE USER on the source and the CHANGE REPLICATION SOURCE TO on the replica with SOURCE_HOST='mysql-source'. See Docker-Compose and Docker.

Useful Commands #