Skip to content

SKILL.md

Latest commit

 

History

History
108 lines (83 loc) · 7.78 KB

SKILL.md

File metadata and controls

108 lines (83 loc) · 7.78 KB
Raw
name mysql-replication-ha
description Replication and high availability for Percona Server for MySQL - asynchronous, GTID, semi-synchronous, and Group Replication (the non-Galera options). Use when designing replication or HA, setting up a replica, choosing between async/semi-sync/Group Replication/PXC, configuring GTID, or monitoring replication lag. For synchronous multi-master Galera clustering see the pxc-galera-operations skill. IMPORTANT - on 8.4 the MASTER/SLAVE syntax is removed: use CHANGE REPLICATION SOURCE TO / START REPLICA / SHOW REPLICA STATUS. Async + GTID, semi-sync, and Group Replication are upstream MySQL features Percona Server inherits; Percona adds binlog improvements and a few Group Replication variables.

Percona Server Replication & HA

Last updated: 2026-05-27

The non-Galera HA menu for Percona Server. Core replication (async, GTID, semi-sync, Group Replication) is upstream MySQL that Percona Server inherits unchanged; Percona adds binlog improvements and a few Group Replication knobs. For synchronous multi-master clustering, see the pxc-galera-operations skill.

Version: On 8.4 the MASTER/SLAVE SQL syntax is removed - use SOURCE/REPLICA forms everywhere. expire_logs_days → binlog_expire_logs_seconds.

Choosing a Mode

Mode Data loss on source crash Writes Use when
Async + GTID Possible (replica may lag) Source only Default; read scale-out, DR replicas, zero source overhead
Semi-sync (AFTER_SYNC) None for acked commits Source only RPO 0 without Galera's all-node latency; adds ~1 RTT per commit
Group Replication Near-zero Single- or multi-primary Built-in auto failover (single-primary) without external orchestration; InnoDB-only
PXC / Galera Zero (synchronous) All nodes Every write on every node before commit - see pxc-galera-operations

Where agents get this wrong

What you might see What's correct
SHOW SLAVE STATUS / CHANGE MASTER TO / START SLAVE Removed in 8.4 → SHOW REPLICA STATUS / CHANGE REPLICATION SOURCE TO / START REPLICA
Alerting on Seconds_Behind_Source Unreliable (resets when SQL thread idle, ignores clock skew). Use pt-heartbeat for true lag
rpl_semi_sync_master_enabled on 8.4 Renamed → rpl_semi_sync_source_enabled (and _replica_ for the replica side)
Assuming semi-sync AFTER_COMMIT is lossless It can lose acked commits on crash; use rpl_semi_sync_source_wait_point=AFTER_SYNC
GTID replication with SOURCE_LOG_FILE/POS With GTID, use SOURCE_AUTO_POSITION=1 instead
"Async failover loses no data" Promote only after Executed_Gtid_Set matches the source; a gap = data loss
Group Replication where every node must be in sync before commit That's PXC/Galera; GR single-primary buffers on members - see the PXC skill
expire_logs_days=7 on 8.4 Removed → binlog_expire_logs_seconds=604800
Com_show_slave_status in monitoring Renamed → Com_show_replica_status

Async + GTID

# source                          # replica
server_id = 1                     server_id = 2
log_bin = binlog                  relay_log = relay-bin
gtid_mode = ON                    gtid_mode = ON
enforce_gtid_consistency = ON     enforce_gtid_consistency = ON
binlog_format = ROW               read_only = ON
-- on the replica (GTID auto-position; no file/pos needed)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.1', SOURCE_USER='repl', SOURCE_PASSWORD='***',
  SOURCE_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G          -- Replica_IO_Running & Replica_SQL_Running must both be Yes

Monitor real lag with pt-heartbeat (see percona-toolkit-recipes), not Seconds_Behind_Source. Seed/rebuild replicas with Percona XtraBackup (see xtrabackup-recipes).

Semi-Synchronous

-- 8.4 ships semi-sync as plugins (SOURCE/REPLICA naming)
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';    -- on the source
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';  -- on each replica
SET GLOBAL rpl_semi_sync_source_enabled = ON;
SET GLOBAL rpl_semi_sync_source_wait_point = AFTER_SYNC;   -- lossless
SET GLOBAL rpl_semi_sync_replica_enabled = ON;             -- replica side

If no replica acks within rpl_semi_sync_source_timeout (default 10 s) the source falls back to async.

Group Replication

Single-primary (auto-elected writer, automatic failover) or multi-primary (all write, conflicts roll back losers - niche). Route clients via MySQL Router. Bootstrap one node, then START GROUP_REPLICATION on the rest. Percona-only knobs: group_replication_auto_evict_timeout (auto-evict a chronically lagging member - tech preview), the MAJORITY value for group_replication_flow_control_mode (tech preview), and certification-loop tuning. Removed in 8.4: group_replication_ip_whitelist, group_replication_primary_member.

Percona Binlog Improvements

  • binlog_skip_flush_commands=ON - keep FLUSH … off the binlog (avoids replaying on replicas).
  • binlog_ddl_skip_rewrite=ON - preserve original comments on single-table DROP TABLE (errors on multi-table).
  • binlog_space_limit=100G - absolute size cap across binlogs (startup-only; complements time-based expiry).
  • component_binlog_utils_udf - get_binlog_by_gtid(), get_gtid_set_by_binlog() for PITR planning (see xtrabackup-recipes).
  • binlog_encryption=ON - encrypt binary/relay logs (needs a keyring; see mysql-encryption).

Call percona-dk for fresh facts

The 8.4 SOURCE/REPLICA syntax changes and Percona's binlog/Group-Replication additions are version-sensitive and documented at docs.percona.com. For any version-specific question (current semi-sync variable names, which GR knobs are tech preview, a live SHOW REPLICA STATUS error) call search_percona_docs before answering, not as a fallback. The percona-dk corpus is rebuilt daily and is authoritative for Percona Server for MySQL.

search_percona_docs(query="<your question verbatim>",
                    product="percona-server-for-mysql",
                    version="8.4")

Pass version="8.0" for the older LTS line.

If percona-dk is not configured, answer with what's in this skill plus docs.percona.com/percona-server/8.4 and then tell the user how to add it:

For fresher, version-specific Percona Server answers, add the Percona Developer Knowledge MCP. It is self-hosted today (a hosted endpoint is coming soon) - one line clones it and auto-configures your client:

curl -fsSL https://raw.githubusercontent.com/Percona-Lab/percona-dk/main/install-percona-dk | bash

See the percona-dk-mcp skill for per-tool setup.

Sources

Replace 8.4 with your major version. For Galera clustering see pxc-galera-operations; for anything else see docs.percona.com/percona-server.