mariadb-replication-and-ha

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

MariaDB Replication and High Availability

MariaDB复制与高可用性

Last updated: 2026-06-24

MariaDB offers three tiers of replication depending on your consistency and availability requirements:

Approach	Consistency	Failover	Best for
Standard async replication	Eventual	Manual or tool-assisted	Read scaling, backups, low-latency writes
Semi-synchronous replication	Eventual (same as async)	Manual or tool-assisted	Ensuring a replica received each commit before the client is acknowledged — bounds failover loss (lossless only with `AFTER_SYNC` )
Galera Cluster	Synchronous (multi-primary)	Automatic	Zero-data-loss HA, multi-datacenter writes

Requires: GTID replication, semi-synchronous replication, and parallel replication (including
optimistic
mode) are all built in and have been available since well before any currently-supported release — assume they are present on the 11.8 LTS baseline. Current LTS is 11.8 (GA May 2025).
Default context: Assume MariaDB 11.8 LTS unless the user states another version. Features marked 12.x or 13.0 may be suggested when relevant (including as upgrade options), but always state the minimum version — do not present them as available on 11.8.

最后更新时间：2026-06-24

根据一致性和可用性需求，MariaDB提供三个层级的复制方案：

方案	一致性	故障转移	适用场景
标准异步复制	最终一致性	手动或工具辅助	读扩展、备份、低延迟写入
半同步复制	最终一致性（与异步相同）	手动或工具辅助	确保副本在客户端收到提交确认前已接收每个事务——限制故障转移时的数据丢失（仅使用 `AFTER_SYNC` 时实现无丢失）
Galera Cluster	同步（多主）	自动	零数据丢失高可用、多数据中心写入

要求： GTID复制、半同步复制和并行复制（包括
optimistic
模式）均已内置，且在所有当前支持的版本之前就已可用——默认基于11.8 LTS版本。当前LTS版本为11.8（2025年5月正式发布）。
默认环境：除非用户指定其他版本，否则默认使用MariaDB 11.8 LTS。标记为12.x或13.0的特性可在相关场景下推荐（包括作为升级选项），但需明确说明最低版本——不得将其表述为11.8版本可用。

What LLMs Get Wrong

大语言模型（LLMs）常见错误

What you might see	What's correct
`CHANGE REPLICATION SOURCE TO` , `SOURCE_*` connection options	MariaDB has no `CHANGE REPLICATION SOURCE TO` — use `CHANGE MASTER TO` with `MASTER_*` options. Since 10.5.1, `START REPLICA` / `SHOW REPLICA STATUS` are canonical; `START SLAVE` / `SHOW SLAVE STATUS` are legacy aliases
`replica_parallel_type` with values `DATABASE` or `LOGICAL_CLOCK`	MariaDB uses `slave_parallel_mode` ( `optimistic` / `conservative` / `aggressive` / `minimal` / `none` ) — a different implementation; MySQL's mode settings do not port over. Pool size: `slave_parallel_threads`
MySQL GTID format or `gtid_mode=ON` syntax	MariaDB GTID uses a different format ( `domain-server-seq` ) and different commands — MySQL and MariaDB GTIDs are incompatible
MySQL Group Replication or InnoDB Cluster — `START GROUP_REPLICATION` , `group_replication_*` variables, MySQL Shell `dba.createCluster()`	MariaDB has no Group Replication and no InnoDB Cluster — Group Replication is incompatible with MariaDB. The synchronous multi-primary equivalent is Galera Cluster (built in)
"Install the Galera plugin"	There is no Galera plugin to load — wsrep support is built into the MariaDB server. But a cluster still requires the separate Galera wsrep provider library ( `galera-4` / `libgalera_smm.so` , set via `wsrep_provider` ); on 12.3+ you must install it yourself (see the Galera Cluster section)
Assuming sequential `AUTO_INCREMENT` in Galera	Galera produces gaps in auto-increment sequences across nodes by design — never rely on sequential values
`LOCK TABLES` or `GET_LOCK()` in a Galera environment	Not supported in Galera — use transactions instead
Treating a replica as a backup	Replication is not a backup — a `DROP TABLE` on the primary replicates immediately to all replicas
Tables without primary keys in a Galera cluster	All tables in Galera must have a primary key — `DELETE` fails on keyless tables

常见错误表述	正确内容
`CHANGE REPLICATION SOURCE TO` 、 `SOURCE_*` 连接选项	MariaDB不支持 `CHANGE REPLICATION SOURCE TO` ——请使用 `CHANGE MASTER TO` 搭配 `MASTER_*` 选项。自10.5.1版本起， `START REPLICA` / `SHOW REPLICA STATUS` 为标准语法； `START SLAVE` / `SHOW SLAVE STATUS` 为旧版别名
`replica_parallel_type` 取值为 `DATABASE` 或 `LOGICAL_CLOCK`	MariaDB使用 `slave_parallel_mode` （取值为 `optimistic` / `conservative` / `aggressive` / `minimal` / `none` ）——这是独立实现；MySQL的模式设置无法直接迁移。线程池大小由 `slave_parallel_threads` 控制
MySQL GTID格式或 `gtid_mode=ON` 语法	MariaDB GTID使用不同格式（ `domain-server-seq` ）和命令——MySQL与MariaDB的GTID不兼容
MySQL Group Replication或InnoDB Cluster —— `START GROUP_REPLICATION` 、 `group_replication_*` 变量、MySQL Shell的 `dba.createCluster()`	MariaDB不支持Group Replication和InnoDB Cluster——Group Replication与MariaDB不兼容。对应的同步多主方案是Galera Cluster（已内置）
"安装Galera插件"	无需加载Galera插件——MariaDB服务器已内置wsrep支持。但集群仍需单独的Galera wsrep提供程序库（ `galera-4` / `libgalera_smm.so` ，通过 `wsrep_provider` 配置）；12.3+版本需自行安装该库（详见Galera Cluster章节）
假设Galera中 `AUTO_INCREMENT` 是连续的	Galera设计上会在节点间产生自增序列间隙——切勿依赖连续的自增值
在Galera环境中使用 `LOCK TABLES` 或 `GET_LOCK()`	Galera不支持此类操作——请使用事务替代
将副本当作备份使用	复制不等于备份——主库上的 `DROP TABLE` 会立即同步到所有副本
Galera集群中无主键的表	Galera中所有表必须有主键——无主键表执行 `DELETE` 会失败

Standard Async Replication

标准异步复制

The foundation: one primary, one or more replicas. The primary writes to the binary log; replicas apply changes asynchronously.

GTID-based replication is the default since MariaDB 10.10 (MDEV-19801) and remains so on 10.11 LTS, 11.4 LTS, and 11.8 LTS. On a fresh replica start, a

RESET SLAVE

, or a

CHANGE MASTER TO

that omits

MASTER_USE_GTID

, the replica defaults to

slave_pos

instead of legacy file/position. If you have configs that rely on the old behavior, set

MASTER_USE_GTID=no

explicitly.

sql

-- On replica (10.10+ — MASTER_USE_GTID is optional, slave_pos is the default):
CHANGE MASTER TO
  MASTER_HOST='primary.host',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_USE_GTID = slave_pos;

START SLAVE;

Promoting a replica to primary — historically

MASTER_USE_GTID=current_pos

was used to include locally-written GTIDs. current_pos
is deprecated since 10.10 (MDEV-20122). Use

MASTER_DEMOTE_TO_SLAVE=1

instead: it converts the old primary's

gtid_binlog_pos

into

gtid_slave_pos

so the demoted server can attach to the new primary cleanly without race conditions.

sql

-- On the former primary, being demoted to a replica (10.10+):
CHANGE MASTER TO
  MASTER_HOST='new_primary.host',
  ...,
  MASTER_DEMOTE_TO_SLAVE=1;
START SLAVE;

Since MariaDB 13.0,

CHANGE MASTER

also resets

Master_Server_Id

SHOW SLAVES STATUS

. On older versions this field could carry stale values across primary changes — check it explicitly when reconfiguring replication on pre-13.0 servers.

基础架构：一个主库，一个或多个副本。主库写入二进制日志；副本异步应用变更。

自MariaDB 10.10版本起，基于GTID的复制为默认方式（MDEV-19801），并在10.11 LTS、11.4 LTS和11.8 LTS版本中保持默认。在副本首次启动、执行

RESET SLAVE

或未指定

MASTER_USE_GTID

的

CHANGE MASTER TO

操作时，副本默认使用

slave_pos

而非旧版的文件/位置方式。若依赖旧版行为，请显式设置

MASTER_USE_GTID=no

。

sql

-- 在副本上（10.10+版本——MASTER_USE_GTID为可选，slave_pos为默认）：
CHANGE MASTER TO
  MASTER_HOST='primary.host',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_USE_GTID = slave_pos;

START SLAVE;

将副本提升为主库——历史上使用

MASTER_USE_GTID=current_pos

来包含本地写入的GTID。current_pos
自10.10版本起已废弃（MDEV-20122）。请改用

MASTER_DEMOTE_TO_SLAVE=1

：它会将旧主库的

gtid_binlog_pos

转换为

gtid_slave_pos

，使降级后的服务器能无竞争地干净连接到新主库。

sql

-- 在即将降级为副本的旧主库上（10.10+版本）：
CHANGE MASTER TO
  MASTER_HOST='new_primary.host',
  ...,
  MASTER_DEMOTE_TO_SLAVE=1;
START SLAVE;

自MariaDB 13.0版本起，

CHANGE MASTER

还会重置

SHOW SLAVES STATUS

中的

Master_Server_Id

字段。在旧版本中，该字段在主库切换后可能保留过期值——在13.0之前的服务器上重新配置复制时，请显式检查该字段。

MariaDB GTID Format

MariaDB GTID格式

MariaDB GTIDs have three components:

domain_id-server_id-sequence

(e.g.,

0-1-247

This is different from MySQL's

server_uuid:sequence

format. They are not compatible — a MariaDB primary cannot replicate to a MySQL replica using GTIDs, and vice versa.

Domain IDs (

gtid_domain_id

) identify independent replication streams. The rule is about concurrency of writes, not server count — a common and damaging mistake is to give every server its own domain ID:

Single active primary, including simple failover: leave
```
gtid_domain_id = 0
```
on all servers. In an
```
A → B
```
pair where
```
B
```
is later promoted (so it becomes
```
B → A
```
),
```
A
```
and
```
B
```
share the same domain ID — do not give them different ones.
Multiple primaries updated concurrently (multi-source, or multi-primary within one topology): give each concurrently-updated primary its own distinct
```
gtid_domain_id
```
, so each stream stays independently ordered and automatic GTID replica-switchover works correctly.

sql

-- Multi-source / multi-primary ONLY — each concurrently-written primary gets its own domain:
SET GLOBAL gtid_domain_id = 1;   -- on primary A
SET GLOBAL gtid_domain_id = 2;   -- on primary B

Assigning a distinct domain ID per server otherwise complicates the GTID position and loses the single ordered binlog stream. See Global Transaction ID.

Enable
gtid_strict_mode
alongside domain ID configuration — it catches out-of-order or mixed-domain GTID mistakes before they corrupt a replica's position:

ini

undefined

MariaDB GTID包含三个组件：

domain_id-server_id-sequence

（例如：

0-1-247

）。

这与MySQL的

server_uuid:sequence

格式不同。两者不兼容——MariaDB主库无法通过GTID复制到MySQL副本，反之亦然。

Domain ID（

gtid_domain_id

）用于标识独立的复制流。规则与写入并发度相关，而非服务器数量——常见的错误是为每个服务器分配独立的domain ID：

单活跃主库（包括简单故障转移）： 所有服务器保持
```
gtid_domain_id = 0
```
。在
```
A → B
```
的配对中，若B后续被提升为主库（变为
```
B → A
```
），A和B共享同一个domain ID——切勿分配不同的ID。
多主库并发写入（多源或同一拓扑内的多主）：为每个并发写入的主库分配唯一的
```
gtid_domain_id
```
，确保每个流保持独立顺序，且GTID副本切换能自动正确执行。

sql

-- 仅适用于多源/多主场景——每个并发写入的主库分配独立domain：
SET GLOBAL gtid_domain_id = 1;   -- 在主库A上
SET GLOBAL gtid_domain_id = 2;   -- 在主库B上

否则，为每个服务器分配独立domain ID会使GTID位置复杂化，并丢失单一有序的二进制日志流。详见全局事务ID。

**启用

gtid_strict_mode

**搭配domain ID配置——它会在副本位置被破坏前捕获乱序或混合domain的GTID错误：

ini

undefined

my.cnf on all servers:

所有服务器的my.cnf配置：

gtid_strict_mode = ON


With strict mode ON, a replica stops with an error on a GTID ordering violation rather than silently applying out-of-order transactions. Without it, a domain ID misconfiguration can go undetected until the only fix is a full resync.

In multi-source replication, replication commands and variables (`START SLAVE`, `SHOW SLAVE STATUS`, …) act on the connection named by `default_master_connection`. It was session-only — you had to `SET SESSION default_master_connection='name'` in each session before issuing commands for that source. Since MariaDB 13.0 ([MDEV-9247](https://mariadb.com/docs/release-notes/community-server/13.0/mariadb-13.0-changes-and-improvements)) it can also be set **globally**, making a chosen named connection the default for all sessions.

gtid_strict_mode = ON


启用严格模式后，副本在遇到GTID顺序违规时会报错停止，而非静默应用乱序事务。若未启用，domain ID配置错误可能直到需要完全重新同步时才被发现。

在多源复制中，复制命令和变量（`START SLAVE`、`SHOW SLAVE STATUS`等）作用于`default_master_connection`指定的连接。该变量曾仅支持会话级——在每个会话中执行`SET SESSION default_master_connection='name'`后，才能针对该源执行命令。自MariaDB 13.0版本起（[MDEV-9247](https://mariadb.com/docs/release-notes/community-server/13.0/mariadb-13.0-changes-and-improvements)），它也支持**全局设置**，使选定的命名连接成为所有会话的默认连接。

Parallel Replication

并行复制

By default, replicas apply events serially —

slave_parallel_threads

defaults to

, meaning parallel replication is off out of the box regardless of the mode setting. To enable it:

ini

undefined

默认情况下，副本串行应用事件——

slave_parallel_threads

默认值为

，意味着无论模式如何，并行复制默认关闭。如需启用：

ini

undefined

my.cnf on replica:

副本的my.cnf配置：

slave_parallel_threads = 4 # must be > 0 to enable parallel apply slave_parallel_mode = optimistic # default since 10.5.1 — tries parallel, retries on conflict


`optimistic` mode applies transactions in parallel and retries on conflict. Use `conservative` for stricter workloads where conflict retries are unacceptable.

> **Different from MySQL:** MariaDB's `slave_parallel_mode` (`optimistic`, `conservative`, etc.) is its own implementation — not equivalent to MySQL's `replica_parallel_type` (`DATABASE` / `LOGICAL_CLOCK`). Copy-pasting a MySQL parallel-replication mode config will not work. Pool size is `slave_parallel_threads` (alias `slave_parallel_workers`).

Since MariaDB 12.1, parallel replication also works when **asynchronously replicating between two Galera clusters** (MDEV-20065) — useful for cross-datacenter or DR setups where one Galera cluster is an async replica of another.

slave_parallel_threads = 4 -- 必须大于0才能启用并行应用 slave_parallel_mode = optimistic -- 自10.5.1版本起为默认——尝试并行应用，冲突时重试


`optimistic`模式并行应用事务，冲突时重试。对于冲突重试不可接受的严格工作负载，请使用`conservative`模式。

> **与MySQL的区别：** MariaDB的`slave_parallel_mode`（`optimistic`、`conservative`等）是独立实现——与MySQL的`replica_parallel_type`（`DATABASE` / `LOGICAL_CLOCK`）不等价。直接复制MySQL的并行复制模式配置无法生效。线程池大小由`slave_parallel_threads`（别名`slave_parallel_workers`）控制。

自MariaDB 12.1版本起，并行复制也支持**两个Galera集群间的异步复制**（MDEV-20065）——适用于跨数据中心或灾难恢复场景，其中一个Galera集群作为另一个的异步副本。

Replication Improvements in 10.7–10.11 LTS

10.7–10.11 LTS版本的复制改进

Two-phase
ALTER TABLE
replication (10.8+, MDEV-11675,
```
binlog_alter_two_phase
```
) — opt-in: when enabled, a large
```
ALTER TABLE
```
can start on the replica while the primary is still executing it, rather than only after, which can reduce replication lag during schema changes. Off by default. Treat as advanced/experimental — validate thoroughly before relying on it in production rather than enabling it by default.
GTID-aware
mariadb-binlog
(10.8+, MDEV-4989) —
```
--start-position
```
and
```
--stop-position
```
accept GTID lists, so point-in-time replay tools can target GTIDs directly without needing binlog file/offset pairs. (Separately,
```
--gtid-strict-mode
```
— on by default — is only a safeguard: it checks that GTID sequence numbers are monotonic per domain and aborts on out-of-order events; it does not enable GTID targeting.) See mariadb-binlog.
slave_max_statement_time
(10.10+, MDEV-27161) — caps how long a single statement may run on the replica SQL thread. If a replicated statement exceeds it, the SQL thread stops with an error (error 3024) so a slow or runaway query surfaces instead of lag growing unnoticed. It does not skip the statement and continue — replication halts until you investigate and restart it.
mariadb-binlog --do-domain-ids
/
--ignore-domain-ids
/
--ignore-server-ids
(10.9+, MDEV-20119) — domain/server filtering when extracting binlog events.
Multi-source replication CHANNEL syntax (10.7+, MDEV-26307) — MySQL-style
```
FOR CHANNEL 'name'
```
clauses now work in
```
CHANGE MASTER TO
```
,
```
START SLAVE
```
, etc.

两阶段
ALTER TABLE
复制（10.8+版本，MDEV-11675，
```
binlog_alter_two_phase
```
）——可选启用：启用后，大型
```
ALTER TABLE
```
操作可在主库仍执行时就在副本上启动，而非仅在主库完成后，从而减少 schema 变更期间的复制延迟。默认关闭。视为高级/实验性功能——在生产环境依赖前需彻底验证，而非默认启用。
支持GTID的
mariadb-binlog
（10.8+版本，MDEV-4989）——
```
--start-position
```
和
```
--stop-position
```
接受GTID列表，因此时间点恢复工具可直接定位GTID，无需依赖二进制日志文件/偏移量对。（另外，
```
--gtid-strict-mode
```
默认启用——仅作为安全防护：检查每个domain的GTID序列号是否单调递增，遇到乱序事件时终止；它不启用GTID定位功能。）详见mariadb-binlog。
slave_max_statement_time
（10.10+版本，MDEV-27161）——限制副本SQL线程中单条语句的运行时长。若复制的语句超过该时长，SQL线程报错停止（错误码3024），使慢查询或失控查询及时暴露，而非让延迟持续增长。它不会跳过语句继续执行——复制会暂停，直到你排查并重启。
mariadb-binlog --do-domain-ids
/
--ignore-domain-ids
/
--ignore-server-ids
（10.9+版本，MDEV-20119）——提取二进制日志事件时按domain/server过滤。
多源复制CHANNEL语法（10.7+版本，MDEV-26307）——MySQL风格的
```
FOR CHANNEL 'name'
```
子句现在可用于
```
CHANGE MASTER TO
```
、
```
START SLAVE
```
等命令。

Replication Improvements in 11.4 LTS

11.4 LTS版本的复制改进

Global limit on binary log disk space (11.4+, MDEV-31404) —
```
max_binlog_total_size
```
(alias
```
binlog_space_limit
```
, default
```
0
```
= no limit) triggers binlog purging when the total size of all binlogs exceeds the threshold. Combine with
```
--slave-connections-needed-for-purge
```
(default
```
1
```
) so purging won't run if a configured replica is disconnected. New status variable
```
binlog_disk_use
```
reports current disk usage.
GTID index for the binary log (11.4+, MDEV-4991) — a new GTID-to-position index lets reconnecting replicas seek straight to their start position without scanning whole binlog files. Controlled by
```
binlog_gtid_index
```
(default
```
ON
```
),
```
binlog_gtid_index_page_size
```
, and
```
binlog_gtid_index_span_min
```
. Status variables
```
binlog_gtid_index_hit
```
/
```
binlog_gtid_index_miss
```
let you confirm it's being used.
SQL_BEFORE_GTIDS
/
SQL_AFTER_GTIDS
for
START SLAVE UNTIL
(11.4+, MDEV-27247) — finer-grained stopping for staged failover or PITR replay.
Detailed replication-lag fields (11.4+, MDEV-29639) —
```
SHOW REPLICA STATUS
```
adds
```
Master_last_event_time
```
,
```
Slave_last_event_time
```
,
```
Master_Slave_time_diff
```
for clearer lag interpretation than
```
Seconds_Behind_Master
```
alone (the 11.6 update built on this — see below).

二进制日志磁盘空间全局限制（11.4+版本，MDEV-31404）——
```
max_binlog_total_size
```
（别名
```
binlog_space_limit
```
，默认
```
0
```
表示无限制）在所有二进制日志总大小超过阈值时触发清理。搭配
```
--slave-connections-needed-for-purge
```
（默认
```
1
```
），确保在已配置的副本断开连接时不执行清理。新增状态变量
```
binlog_disk_use
```
报告当前磁盘使用情况。
二进制日志的GTID索引（11.4+版本，MDEV-4991）——新增的GTID到位置索引使重新连接的副本可直接跳转到起始位置，无需扫描整个二进制日志文件。由
```
binlog_gtid_index
```
（默认
```
ON
```
）、
```
binlog_gtid_index_page_size
```
和
```
binlog_gtid_index_span_min
```
控制。状态变量
```
binlog_gtid_index_hit
```
/
```
binlog_gtid_index_miss
```
可确认是否在使用该索引。
START SLAVE UNTIL
的
SQL_BEFORE_GTIDS
/
SQL_AFTER_GTIDS
（11.4+版本，MDEV-27247）——为分阶段故障转移或时间点恢复（PITR）提供更精细的停止控制。
详细的复制延迟字段（11.4+版本，MDEV-29639）——
```
SHOW REPLICA STATUS
```
新增
```
Master_last_event_time
```
、
```
Slave_last_event_time
```
、
```
Master_Slave_time_diff
```
字段，比仅使用
```
Seconds_Behind_Master
```
能更清晰地解读延迟（11.6版本的更新在此基础上进一步优化——见下文）。

Binlog Performance Improvements in 11.7

11.7版本的二进制日志性能改进

Large-transaction commit no longer freezes other transactions (11.7+, MDEV-32014) — previously, committing a very large transaction while
```
log_bin
```
was on would stall all other transactions until the binlog write completed. This bottleneck is gone.
Async rollback of prepared transactions during binlog crash recovery (11.7+, MDEV-33853) — faster startup after a crash with many prepared transactions.
slave_abort_blocking_timeout
(11.7+, MDEV-34857) — kill long-running queries on a replica when they block replication progress past a threshold. Useful on read replicas that occasionally run long analytical queries.

大事务提交不再冻结其他事务（11.7+版本，MDEV-32014）——此前，启用
```
log_bin
```
时提交超大事务会使所有其他事务停滞，直到二进制日志写入完成。该瓶颈已消除。
二进制日志崩溃恢复期间异步回滚预准备事务（11.7+版本，MDEV-33853）——在存在大量预准备事务的情况下，崩溃后的启动速度更快。
slave_abort_blocking_timeout
（11.7+版本，MDEV-34857）——当副本上的长查询阻碍复制进度超过阈值时，终止该查询。适用于偶尔运行长分析查询的只读副本。

Monitoring Replication Lag

监控复制延迟

sql

SHOW SLAVE STATUS\G
-- Key fields:
-- Seconds_Behind_Master: estimated lag in seconds
-- Last_SQL_Error: last error stopping the SQL thread
-- Relay_Log_Pos vs Read_Master_Log_Pos: how far behind the relay log is

Alert when

Seconds_Behind_Master > 5

for latency-sensitive applications. A value of

NULL

means replication is not running. Note:

Seconds_Behind_Master

can be misleading on idle primaries — use heartbeat tools (e.g.,

pt-heartbeat

) for accurate measurement.

Since MariaDB 11.6 (MDEV-33856), the definition of

Seconds_Behind_Master

was refined and three new columns were added to

SHOW ALL REPLICAS STATUS

plus a new Information Schema

SLAVE_STATUS

table, providing more nuanced lag visibility (e.g., separate measurements for IO vs SQL thread lag).

sql

SHOW SLAVE STATUS\G
-- 关键字段：
-- Seconds_Behind_Master：估计延迟（秒）
-- Last_SQL_Error：导致SQL线程停止的最后一个错误
-- Relay_Log_Pos 与 Read_Master_Log_Pos：中继日志落后的程度

对延迟敏感的应用，当

Seconds_Behind_Master > 5

时触发告警。值为

NULL

表示复制未运行。注意：在主库空闲时，

Seconds_Behind_Master

可能有误导性——使用心跳工具（如

pt-heartbeat

）进行准确测量。

自MariaDB 11.6版本起（MDEV-33856），

Seconds_Behind_Master

的定义已优化，

SHOW ALL REPLICAS STATUS

新增三个列，并新增Information Schema的

SLAVE_STATUS

表，提供更细致的延迟可见性（例如，IO线程与SQL线程延迟的单独测量值）。

Semi-Synchronous Replication

半同步复制

The primary writes and fsyncs each transaction to its own binary log first — making it durable locally — and only then waits for at least one replica to acknowledge that it has received the transaction before reporting the commit complete to the client. The

rpl_semi_sync_master_wait_point

setting controls when that wait happens: with

AFTER_SYNC

the primary waits before the changes become visible, so failover to an acknowledged replica is lossless; with

AFTER_COMMIT

(the MariaDB default) the transaction is already committed and visible before the wait, so a crash in that window can still lose it on failover. See Semisynchronous Replication.

sql

-- Enable on primary:
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- Enable on replica:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

If no replica acknowledges within

rpl_semi_sync_master_timeout

(default 10 seconds), the primary falls back to async. Built-in since MariaDB 10.3 — no plugin needed.

Use when: you want at least one replica to have received each transaction before the client's commit returns — e.g. to bound failover data loss (use

AFTER_SYNC

). Note that semi-sync only delays commit completion as seen by the client; it does not add durability to the primary's own copy, and a transaction lost before any replica receives it is gone regardless of semi-sync.

主库先将每个事务写入并同步到自身的二进制日志——确保本地持久化——然后等待至少一个副本确认已接收该事务，再向客户端报告提交完成。

rpl_semi_sync_master_wait_point

设置控制等待时机：使用

AFTER_SYNC

时，主库在变更可见前等待，因此故障转移到已确认的副本时无数据丢失；使用

AFTER_COMMIT

（MariaDB默认）时，事务在等待前已提交并可见，因此该窗口内的崩溃仍可能在故障转移时丢失数据。详见半同步复制。

sql

-- 在主库上启用：
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- 在副本上启用：
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

若在

rpl_semi_sync_master_timeout

（默认10秒）内无副本确认，主库会回退到异步模式。自MariaDB 10.3版本起内置——无需插件。

适用场景：希望客户端收到提交返回前，至少有一个副本已接收每个事务——例如限制故障转移时的数据丢失（使用

AFTER_SYNC

）。注意：半同步仅延迟客户端看到的提交完成时间；它不会增强主库自身副本的持久性，且在任何副本接收前丢失的事务，无论半同步如何配置都会丢失。

Galera Cluster

Multi-primary synchronous replication — all nodes accept reads and writes, changes are certified across the cluster before committing. No single point of failure. Built into MariaDB.

Packaging change (12.3+): The Galera library is no longer included as a server-package dependency or in the MariaDB repositories by default (MDEV-38744). On 12.3+ you must install
galera-4
(or your distro's equivalent) separately when setting up a Galera node. The MariaDB server still understands Galera natively — only the library distribution changed.

多主同步复制——所有节点接受读写，变更在提交前会在集群内进行认证。无单点故障。已内置到MariaDB。

打包变更（12.3+版本）： Galera库不再默认作为服务器包依赖或包含在MariaDB仓库中（MDEV-38744）。在12.3+版本中搭建Galera节点时，需单独安装
galera-4
（或发行版对应的包）。MariaDB服务器仍原生支持Galera——仅库的分发方式变更。

Developer Constraints

开发约束

These will break in Galera if you're not aware of them:

All tables must have a primary key:

sql

-- ✗ DELETE fails in Galera on keyless tables:
CREATE TABLE logs (message TEXT);

-- ✅ Always define a PK:
CREATE TABLE logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, message TEXT);

AUTO_INCREMENT values have gaps — Galera uses

auto_increment_increment

and

auto_increment_offset

per node to avoid conflicts, resulting in non-sequential IDs. Never rely on sequential auto-increment in Galera.

LOCK TABLES, GET_LOCK(), and
FLUSH TABLES {table list} WITH READ LOCK
are not supported — use transactions. Note: global

FLUSH TABLES WITH READ LOCK

(no table list) IS supported:

sql

-- ✗ Not supported in Galera:
LOCK TABLES orders WRITE;
-- ✅ Use a transaction instead:
BEGIN;
SELECT ... FOR UPDATE;
UPDATE ...;
COMMIT;

InnoDB only — Galera replicates only InnoDB tables. MyISAM has experimental support via

wsrep_replicate_myisam

but is not recommended for production.

Transaction size limits — default caps: 128K rows (

wsrep_max_ws_rows

) and 2GB (

wsrep_max_ws_size

). Extremely large transactions degrade cluster performance significantly and may require config tuning. Note: in MariaDB 13.0+, the default

binlog_row_event_max_size

is 64 KB (up from older 8 KB) — relevant when sizing replication events for write-heavy workloads.

Binary log must be ROW format — do not change

binlog_format

at runtime in a Galera cluster.

Write-set retry on conflict (12.1+) —

wsrep_applier_retry_count

controls how many times an applier retries a write set before erroring out. Tune this if your workload sees transient certification conflicts on busy clusters.

Automatic SST user account management (11.6+, MDEV-31809) — Galera now manages the dedicated SST (State Snapshot Transfer) user account automatically; you no longer have to create and grant it manually on every node.

IP allowlist for nodes joining the cluster (10.10+, MDEV-27246) —

wsrep_allowlist

restricts which IPs can make SST/IST requests, reducing the attack surface on a Galera cluster's intra-node traffic.

Query cache: The query cache was removed in later MariaDB versions and was not required in Galera since MariaDB 10.1.2. No action needed on modern installations.

若不注意以下约束，在Galera中会出现问题：

所有表必须有主键：

sql

-- ✗ Galera中无主键表执行DELETE会失败：
CREATE TABLE logs (message TEXT);

-- ✅ 始终定义主键：
CREATE TABLE logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, message TEXT);

AUTO_INCREMENT值存在间隙——Galera为每个节点使用

auto_increment_increment

和

auto_increment_offset

避免冲突，导致ID不连续。切勿依赖Galera中连续的自增值。

不支持LOCK TABLES、GET_LOCK()和
FLUSH TABLES {table list} WITH READ LOCK
——请使用事务。注意：全局

FLUSH TABLES WITH READ LOCK

（无表列表）支持：

sql

-- ✗ Galera中不支持：
LOCK TABLES orders WRITE;
-- ✅ 改用事务：
BEGIN;
SELECT ... FOR UPDATE;
UPDATE ...;
COMMIT;

仅支持InnoDB——Galera仅复制InnoDB表。MyISAM通过

wsrep_replicate_myisam

提供实验性支持，但不推荐用于生产环境。

事务大小限制——默认上限：128K行（

wsrep_max_ws_rows

）和2GB（

wsrep_max_ws_size

）。超大事务会显著降低集群性能，可能需要调整配置。注意：在MariaDB 13.0+版本中，默认

binlog_row_event_max_size

为64 KB（旧版本为8 KB）——对写入密集型工作负载的复制事件大小调整有重要意义。

二进制日志必须为ROW格式——在Galera集群中不要运行时修改

binlog_format

。

写入集冲突重试（12.1+版本）——

wsrep_applier_retry_count

控制应用器在报错前重试写入集的次数。若工作负载在繁忙集群中出现临时认证冲突，可调整该参数。

自动SST用户账户管理（11.6+版本，MDEV-31809）——Galera现在自动管理专用的SST（状态快照传输）用户账户；无需在每个节点上手动创建和授权。

节点加入集群的IP白名单（10.10+版本，MDEV-27246）——

wsrep_allowlist

限制可发起SST/IST请求的IP，减少Galera集群节点间流量的攻击面。

查询缓存： 查询缓存在后续MariaDB版本中已移除，且自MariaDB 10.1.2版本起Galera不再需要它。现代安装无需额外操作。

Stale Reads and Consistency

stale读取与一致性

Galera is "virtually synchronous" — a committed write on one node may not be immediately visible on another without additional synchronization:

sql

-- Force a sync point before reading (performance cost):
SET SESSION wsrep_sync_wait = 1;
SELECT * FROM orders WHERE id = 42;

Use

wsrep_sync_wait

only where strict read-after-write consistency is required. For most reads, eventual consistency across nodes (milliseconds) is acceptable.

Galera是"虚拟同步"——一个节点上提交的写入可能不会立即在另一个节点上可见，需额外同步：

sql

-- 读取前强制同步（会有性能开销）：
SET SESSION wsrep_sync_wait = 1;
SELECT * FROM orders WHERE id = 42;

仅在需要严格的读-写一致性时使用

wsrep_sync_wait

。对于大多数读取场景，节点间的最终一致性（毫秒级）已足够。

Lost Updates in Galera

Galera中的更新丢失

Galera does not prevent lost updates in read-modify-write patterns. Use

SELECT ... FOR UPDATE

explicitly:

sql

-- ✗ Race condition — another node could modify between SELECT and UPDATE:
SELECT balance FROM accounts WHERE id = 1;
-- ... application logic ...
UPDATE accounts SET balance = new_value WHERE id = 1;

-- ✅ Lock the row at read time:
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = new_value WHERE id = 1;
COMMIT;

Galera无法防止读-改-写模式下的更新丢失。请显式使用

SELECT ... FOR UPDATE

：

sql

-- ✗ 竞态条件——另一个节点可能在SELECT和UPDATE之间修改数据：
SELECT balance FROM accounts WHERE id = 1;
-- ... 应用逻辑 ...
UPDATE accounts SET balance = new_value WHERE id = 1;

-- ✅ 读取时锁定行：
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = new_value WHERE id = 1;
COMMIT;

Replication is Not a Backup

复制不等于备份

DROP TABLE

DELETE FROM table

on the primary replicates to all replicas immediately. Replication protects against hardware failure — not against accidental data changes. Maintain independent backups (

mariadb-dump

mariadb-backup

) on a schedule separate from replication.

Delayed replication is one mitigation — intentionally lag a replica by a set time:

sql

CHANGE MASTER TO MASTER_DELAY = 3600;  -- 1 hour lag

This gives a recovery window for accidental changes, but it is still not a substitute for backups.

Point-in-time recovery (new in 13.0) — the

innodb_log_archive

variable (MDEV-37949) makes InnoDB preserve the write-ahead log as a continuous sequence of files instead of overwriting the circular redo log. Combined with a base backup, this is intended to enable PITR and incremental backups without relying on the binary log alone. It is new in the 13.0 rolling release (not on the 11.8 LTS baseline) — treat it as recent and validate before depending on it for recovery.

主库上的

DROP TABLE

或

DELETE FROM table

会立即同步到所有副本。复制用于防范硬件故障——而非意外数据变更。需维护独立的备份（

mariadb-dump

、

mariadb-backup

），备份计划独立于复制。

延迟复制是一种缓解方案——故意让副本延迟一段时间：

sql

CHANGE MASTER TO MASTER_DELAY = 3600;  -- 延迟1小时

这为意外变更提供了恢复窗口，但仍不能替代备份。

时间点恢复（13.0版本新增）——

innodb_log_archive

变量（MDEV-37949）使InnoDB将预写日志保存为连续文件序列，而非覆盖循环重做日志。结合基础备份，无需仅依赖二进制日志即可实现时间点恢复和增量备份。它是13.0滚动版本的新特性（不在11.8 LTS基线中）——视为近期特性，依赖其进行恢复前需验证。

Failover Tools

故障转移工具

Built-in (no proxy needed) — Galera failover is automatic: any surviving node keeps accepting writes. For async/GTID replication, MariaDB does controlled primary switchover natively via GTID and
```
MASTER_DEMOTE_TO_SLAVE
```
(see Standard Async Replication above), typically driven by your own scripts or an orchestrator.
ProxySQL — open-source (GPLv3) proxy, widely used for read-write splitting and connection pooling with MariaDB.
MaxScale — MariaDB Corporation's proxy with automatic failover, read-write splitting, and connection routing (detects primary failure and promotes the most up-to-date replica; requires GTID replication). Not open source — recent versions (25.01+) are closed-source commercial, and earlier versions used the source-available Business Source License (BSL), not an OSI open-source license. mariadb.com/docs/maxscale

For basic Galera HA no proxy is required; ProxySQL or MaxScale add connection routing.

Galera load balancing requires cluster-state-aware monitoring. Do not route to all Galera nodes equally — a node in SST (full data copy) or desynced state may be up and accepting connections but behind by potentially millions of transactions. Check
wsrep_local_state
: only
4
(Synced) is safe to route traffic to. ProxySQL can be configured with health checks that monitor
wsrep_
status variables; MaxScale uses the
galeramon
monitor for this purpose. Without state-aware routing, writes to a donor/desynced node produce stale reads or integrity issues.

内置（无需代理）——Galera故障转移自动完成：任何存活节点都会继续接受写入。对于异步/GTID复制，MariaDB通过GTID和
```
MASTER_DEMOTE_TO_SLAVE
```
原生支持受控主库切换（见上文标准异步复制），通常由自定义脚本或编排器驱动。
ProxySQL——开源（GPLv3）代理，广泛用于MariaDB的读写分离和连接池。
MaxScale——MariaDB Corporation的代理，支持自动故障转移、读写分离和连接路由（检测主库故障并提升最同步的副本；需GTID复制）。非开源——近期版本（25.01+）为闭源商业软件，早期版本使用源代码可用的商业源码许可证（BSL），而非OSI批准的开源许可证。详见mariadb.com/docs/maxscale

基础Galera高可用无需代理；ProxySQL或MaxScale可添加连接路由功能。

Galera负载均衡需集群状态感知监控。 不要均等路由到所有Galera节点——处于SST（全量数据复制）或脱同步状态的节点可能已启动并接受连接，但可能落后数百万事务。检查
wsrep_local_state
：仅
4
（已同步）状态的节点才安全。ProxySQL可配置监控
wsrep_
状态变量的健康检查；MaxScale使用
galeramon
监控实现此功能。若无状态感知路由，向捐赠/脱同步节点写入会导致stale读取或完整性问题。

Sources

参考资料

For topics not covered here, see the official MariaDB documentation at mariadb.com/docs.

未涵盖的主题，请参阅官方MariaDB文档：mariadb.com/docs。