The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-14. For the latest, interactive version please visit https://mariadb.com/kb/.

Galera Cluster System Variables

Contents

  1. wsrep_auto_increment_control
  2. wsrep_causal_reads
  3. wsrep_certification_rules
  4. wsrep_certify_nonPK
  5. wsrep_cluster_address
  6. wsrep_cluster_name
  7. wsrep_convert_LOCK_to_trx
  8. wsrep_data_home_dir
  9. wsrep_dbug_option
  10. wsrep_debug
  11. wsrep_desync
  12. wsrep_dirty_reads
  13. wsrep_drupal_282555_workaround
  14. wsrep_forced_binlog_format
  15. wsrep_gtid_domain_id
  16. wsrep_gtid_mode
  17. wsrep_ignore_apply_errors
  18. wsrep_load_data_splitting
  19. wsrep_log_conflicts
  20. wsrep_max_ws_rows
  21. wsrep_max_ws_size
  22. wsrep_mysql_replication_bundle
  23. wsrep_node_address
  24. wsrep_node_incoming_address
  25. wsrep_node_name
  26. wsrep_notify_cmd
  27. wsrep_on
  28. wsrep_OSU_method
  29. wsrep_patch_version
  30. wsrep_provider
  31. wsrep_provider_options
  32. wsrep_recover
  33. wsrep_reject_queries
  34. wsrep_replicate_myisam
  35. wsrep_restart_slave
  36. wsrep_retry_autocommit
  37. wsrep_slave_FK_checks
  38. wsrep_slave_threads
  39. wsrep_slave_UK_checks
  40. wsrep_sr_store
  41. wsrep_sst_auth
  42. wsrep_sst_donor
  43. wsrep_sst_donor_rejects_queries
  44. wsrep_sst_method
  45. wsrep_sst_receive_address
  46. wsrep_start_position
  47. wsrep_sync_wait
  48. wsrep_trx_fragment_size
  49. wsrep_trx_fragment_unit

This page documents system variables related to Galera Cluster. For options that are not system variables, see Galera Options.

See Server System Variables for a complete list of system variables and instructions on setting them.

Also see the Full list of MariaDB options, system and status variables.

wsrep_auto_increment_control

  • Description: If set to 1 (the default), will automatically adjust the auto_increment_increment and auto_increment_offset variables according to the size of the cluster, and when the cluster size changes. This avoids replication conflicts due to auto_increment. In a master-slave environment, can be set to OFF.
  • Commandline: --wsrep-auto-increment-control[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: ON

wsrep_causal_reads

  • Description: If set to ON (OFF is default), enforces read-committed characteristics across the cluster. In the case that a master applies an event more quickly than a slave, the two could briefly be out-of-sync. With this variable set to ON, the slave will wait for the event to be applied before processing further queries. Setting to ON also results in larger read latencies. Deprecated by wsrep_sync_wait=1.
  • Commandline: --wsrep-causal-reads[={0|1}]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: OFF

wsrep_certification_rules

  • Description: Certification rules to use in the cluster. Possible values are:
    • strict: Stricter rules that could result in more certification failures. For example with foreign keys, certification failure could result if different nodes receive non-conflicting insertions at about the same time that point to the same row in a parent table
    • optimized: relaxed rules that allow more concurrency and cause less certification failures.
  • Commandline: --wsrep-certifcation-rules
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Enumeration
  • Default Value: strict
  • Valid Values: strict, optimized
  • Introduced: MariaDB 10.4.3, MariaDB 10.3.13, MariaDB 10.2.22, MariaDB 10.1.38

wsrep_certify_nonPK

  • Description: When set to ON (the default), primary keys will be automatically generated for tables without one. This is required for parallel applying, and strongly recommended for all tables.
  • Commandline: --wsrep-certify-nonPK[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: ON

wsrep_cluster_address

  • Description: The addresses of cluster nodes to connect to when starting up, for example gcomm://192.168.0.1:1234?gmcast.listen_addr=0.0.0.0:2345. Good practice is to specify all possible cluster nodes, in the form gcomm://<node1 or ip:port>,<node2 or ip2:port>,<node3 or ip3:port>. Specifying an empty ip (gcomm://) will cause the node to start a new cluster (which should not be done in the my.cnf file, as after each restart the server will not rejoin the current cluster). The variable can be changed at runtime in some configurations, and will result in the node closing the connection to any current cluster, and connecting to the new address. If specifying a port, note that this is the Galera port, not the MariaDB port.
  • Commandline: --wsrep-cluster-address=value
  • Scope: Global
  • Dynamic: No
  • Data Type: String

wsrep_cluster_name

  • Description: The name of the cluster. Nodes cannot connect to clusters with a different name, so needs to be identical on all nodes in the same cluster.
  • Commandline: --wsrep-cluster-name=value
  • Scope: Global
  • Dynamic: Yes
  • Data Type: String
  • Default Value: my_wsrep_cluster

wsrep_convert_LOCK_to_trx

  • Description: Converts LOCK/UNLOCK TABLES statements to BEGIN and COMMIT. Used mainly for getting older applications to work with a multi-master setup, use carefully, as can result in extremely large writesets.
  • Commandline: --wsrep-convert-LOCK-to-trx[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: OFF

wsrep_data_home_dir

  • Description: Directory where wsrep provider will store its internal files.
  • Commandline: --wsrep-data-home-dir=value
  • Scope: Global
  • Dynamic: No
  • Data Type: String
  • Default Value: The datadir variable value.

wsrep_dbug_option

  • Description: Used to pass the DBUG options to the wsrep provider.
  • Commandline: --wsrep-dbug-option=value
  • Scope: Global
  • Dynamic: Yes
  • Data Type: String

wsrep_debug

wsrep_desync

  • Description: When a node receives more write-sets than it can apply, the transactions are placed in a received queue. If the node's received queue has too many write-sets waiting to be applied (as defined by the gcs.fc_limit WSREP provider option), then the node would usually engage Flow Control. However, when this option is set to ON, Flow Control will be disabled for the desynced node. The desynced node works through the received queue until it reaches a more manageable size. The desynced node continues to receive write-sets from the other nodes in the cluster. The other nodes in the cluster do not wait for the desynced node to catch up, so the desynced node can fall even further behind the other nodes in the cluster. You can check if a node is desynced by checking if the wsrep_local_state_comment status variable is equal to Donor/Desynced.
  • Commandline: --wsrep-desync[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: OFF

wsrep_dirty_reads

  • Description: By default, when not synchronized with the group (wsrep_ready=OFF) a node will reject all queries other than SET and SHOW. If wsrep_dirty_reads is set to 1, queries which do not change data, like SELECT queries (dirty reads), creating of prepare statement, etc. will be accepted by the node.
  • Commandline: --wsrep-dirty-reads[={0|1}]
  • Scope: Global,Session
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: OFF
  • Valid Values: ON, OFF
  • Introduced: MariaDB Galera 5.5.42, MariaDB Galera 10.0.16, MariaDB 10.1.3

wsrep_drupal_282555_workaround

  • Description: If set to ON, a workaround for Drupal/MySQL/InnoDB bug #282555 is enabled. This is a bug where, in some cases, when inserting a DEFAULT value into an AUTO_INCREMENT column, a duplicate key error may be returned.
  • Commandline: --wsrep-drupal-282555-workaround[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: OFF

wsrep_forced_binlog_format

  • Description: A binary log format that will override any session binlog format settings.
  • Commandline: --wsrep-forced-binlog-format=value
  • Scope: Global
  • Dynamic: Yes
  • Default Value: NONE
  • Data Type: Enum
  • Valid Values: STATEMENT, ROW, MIXED or NONE (which resets the forced binlog format state).

wsrep_gtid_domain_id

  • Description: This system variable defines the GTID domain ID that is used for wsrep GTID mode.
  • Commandline: --wsrep-gtid-domain-id=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 4294967295
  • Introduced: MariaDB 10.1.4

wsrep_gtid_mode

  • Description: Wsrep GTID mode attempts to keep GTIDs consistent for Galera Cluster write sets on all cluster nodes. GTID state is initially copied to a joiner node during an SST. If you are planning to use Galera Cluster with MariaDB replication, then wsrep GTID mode can be helpful.
  • Commandline: --wsrep-gtid-mode[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Introduced: MariaDB 10.1.4

wsrep_ignore_apply_errors

  • Description: If set, replication errors are ignored.
  • Commandline: --wsrep-ignore-apply-errors
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Numeric
  • Default Value: 0
  • Range: 0 to 7
  • Introduced: MariaDB 10.4.2

wsrep_load_data_splitting

wsrep_log_conflicts

  • Description: If set to ON (OFF is default), details of conflicting MDL as well as InnoDB locks in the cluster will be logged.
  • Commandline: --wsrep-log-conflicts[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: OFF

wsrep_max_ws_rows

wsrep_max_ws_size

wsrep_mysql_replication_bundle

  • Description: Determines the number of replication events that are grouped together. Experimental implementation aimed to assist with bottlenecks when a single slave faces a large commit time delay. If set to 0 (the default), there is no grouping.
  • Commandline: --wsrep-mysql-replication-bundle=#
  • Scope: Global
  • Dynamic: No
  • Data Type: Numeric
  • Default Value: 0
  • Range: 0 to 1000

wsrep_node_address

  • Description: Specifies the node's network address, in the format ip address[:port]. As of MariaDB 10.1.8, supports IPv6. The default behavior is for the node to pull the address of the first network interface on the system and the default Galera port. This autoguessing can be unreliable, particularly in the following cases:
    • cloud deployments
    • container deployments
    • servers with multiple network interfaces.
    • servers running multiple nodes.
    • network address translation (NAT).
    • clusters with nodes in more than one region.
  • Commandline: --wsrep-node-address=value
  • Scope: Global
  • Dynamic: No
  • Data Type: String
  • Default Value: Primary network address, usually eth0 with a default port of 4567, or 0.0.0.0 if no IP address.

wsrep_node_incoming_address

  • Description: This is the address from which the node listens for client connections. If an address is not specified or it's set to AUTO (default), mysqld uses either --bind-address or --wsrep-node-address, or tries to get one from the list of available network interfaces, in the same order.
  • Commandline: --wsrep-node-incoming-address=value
  • Scope: Global
  • Dynamic: No
  • Data Type: String
  • Default Value: AUTO

wsrep_node_name

  • Description: Name of this node. This name can be used in wsrep_sst_donor as a preferred donor. Note that multiple nodes in a cluster can have the same name.
  • Commandline: --wsrep-node-name=value
  • Scope: Global
  • Dynamic: Yes
  • Data Type: String
  • Default Value: The server's hostname.

wsrep_notify_cmd

  • Description: Command to be executed each time the node state or the cluster membership changes. Can be used for raising an alarm, configuring load balancers and so on. See the Codership Notification Script page for more details.
  • Commandline: --wsrep-notify-command=value
  • Scope: Global
  • Dynamic: Yes
  • Data Type: String
  • Default Value: Empty

wsrep_on

  • Description: Whether or not wsrep replication is enabled. If the global value is set to OFF (the default since MariaDB 10.1), it is not possible to load the provider and join the node in the cluster. If only the session value is set to OFF, the operations from that particular session are not replicated in the cluster, but other sessions and applier threads will continue as normal. The session value of the variable does not affect the node's membership and thus, regardless of its value, the node keeps receiving updates from other nodes in the cluster. Before MariaDB 10.1, even though this variable is ON by default, its value gets automatically adjusted based on whether mandatory configurations to turn on Galera replication have been specified. Since MariaDB 10.1, it is set to OFF by default and must be turned on to enable Galera replication.
  • Commandline: --wsrep-on[={0|1}]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: OFF (>= MariaDB 10.1), ON (<= MariaDB Galera Cluster 10.0),
  • Valid Values: ON, OFF

wsrep_OSU_method

  • Description: Online schema upgrade method. The default is TOI, specifying the setting without the optional parameter will set to RSU.
    • TOI: Total Order Isolation. In each cluster node, DDL is processed in the same order regarding other transactions, guaranteeing data consistency. However, affected parts of the database will be locked for the whole cluster.
    • RSU: Rolling Schema Upgrade. DDL processing is only done locally on the node, and the user needs perform the changes manually on each node. The node is desynced from the rest of the cluster while the processing takes place to avoid the blocking other nodes. Schema changes must be backwards compatible in the same way as for ROW based replication to avoid breaking replication when the DDL processing is complete on the single node, and replication recommences.
  • Commandline: --wsrep-OSU-method[=value]
  • Scope: Global, Session (since MariaDB Galera 10.0.19)
  • Dynamic: Yes
  • Data Type: Enum
  • Default Value: TOI
  • Valid Values: TOI, RSU

wsrep_patch_version

  • Description: Wsrep patch version, for example wsrep_25.10.
  • Commandline: None
  • Scope: Global
  • Dynamic: No
  • Data Type: String
  • Default Value: None
  • Introduced: MariaDB 10.1.5

wsrep_provider

  • Description: Location of the wsrep library, usually /usr/lib/libgalera_smm.so on Debian and Ubuntu, and /usr/lib64/libgalera_smm.so on Red Hat/CentOS.
  • Commandline: --wsrep-provider=value
  • Scope: Global
  • Dynamic: Yes
  • Data Type: String
  • Default Value: None

wsrep_provider_options

  • Description: Semicolon (;) separated list of wsrep options (see wsrep_provider_options).
  • Commandline: --wsrep-provider-options=value
  • Scope: Global
  • Dynamic: No
  • Data Type: String
  • Default Value: Empty

wsrep_recover

  • Description: If set to ON when the server starts, the server will recover the sequence number of the most recent write set applied by Galera, and it will be output to stderr, which is usually redirected to the error log. At that point, the server will exit. This sequence number can be provided to the wsrep_start_position system variable.
  • Commandline: --wsrep-recover[={0|1}]
  • Scope: Global
  • Dynamic: No
  • Data Type: Boolean
  • Default Value: OFF

wsrep_reject_queries

  • Description: Variable to set to reject queries from client connections, useful for maintenance. The node continues to apply write-sets, but an Error 1047: Unknown command error is generated by a client query.
    • NONE - Not set. Queries will be processed as normal.
    • ALL - All queries from client connections will be rejected, but existing client connections will be maintained.
    • ALL_KILL All queries from client connections will be rejected, and existing client connections, including the current one, will be immediately killed.
  • Commandline: --wsrep-reject-queries[=value]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Enum
  • Default Value: NONE
  • Valid Values: NONE, ALL, ALL_KILL
  • Introduced: MariaDB 10.3.6, MariaDB 10.2.14, MariaDB 10.1.32

wsrep_replicate_myisam

  • Description: Whether or not DML updates for MyISAM tables will be replicated. This functionality is still experimental and should not be relied upon in production systems.
  • Commandline: --wsrep-replicate-myisam[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Default Value: OFF
  • Data Type: Boolean
  • Valid Values: ON, OFF

wsrep_restart_slave

  • Description: If set to ON, the replication slave is restarted automatically, when node joins back to cluster.
  • Commandline: --wsrep-restart-slave[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Default Value: OFF
  • Data Type: Boolean
  • Introduced: MariaDB Galera 5.5.39, MariaDB Galera 10.0.10

wsrep_retry_autocommit

  • Description: Number of times autocommited queries will be retried due to cluster-wide conflicts before returning an error to the client. If set to 0, no retries will be attempted, while a value of 1 (the default) or more specifies the number of retries attempted. Can be useful to assist applications using autocommit to avoid deadlocks.
  • Commandline: --wsrep-retry-autocommit=value
  • Scope: Global
  • Dynamic: No
  • Data Type: Numeric
  • Default Value: 1
  • Range: 0 to 10000

wsrep_slave_FK_checks

  • Description: If set to ON (the default), the applier slave thread performs foreign key constraint checks.
  • Commandline: --wsrep-slave-FK-checks[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: ON
  • Introduced: MariaDB Galera 5.5.39, MariaDB Galera 10.0.12

wsrep_slave_threads

  • Description: Number of slave threads used to apply Galera write sets in parallel. The Galera slave threads are able to determine which write sets are safe to apply in parallel. However, if your cluster nodes seem to have frequent consistency problems, then setting the value to 1 will probably fix the problem. See About Galera Replication: Galera Slave Threads for more information.
  • Commandline: --wsrep-slave-threads=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Numeric
  • Default Value: 1
  • Range: 1 to 512

wsrep_slave_UK_checks

  • Description: If set to ON, the applier slave thread performs secondary index uniqueness checks.
  • Commandline: --wsrep-slave-UK-checks[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: OFF
  • Introduced: MariaDB Galera 5.5.39, MariaDB Galera 10.0.12

wsrep_sr_store

  • Description: Storage for streaming replication fragments.
  • Commandline: --wsrep-sr-store=val
  • Scope: Global
  • Dynamic: No
  • Data Type: Enum
  • Default Value: table
  • Valid Values: table, none
  • Introduced: MariaDB 10.4.2

wsrep_sst_auth

  • Description: Username and password of the user to use for replication. Unused if wsrep_sst_method is set to rsync, while for other methods it should be in the format <user>:<password>. The contents are masked in logs and when querying the value with SHOW VARIABLES. See Introduction to State Snapshot Transfers (SSTs) for more information.
  • Commandline: --wsrep-sst-auth=value
  • Scope: Global
  • Dynamic: Yes
  • Data Type: String
  • Default Value: (Empty)

wsrep_sst_donor

  • Description: Comma-separated list (from 5.5.33) or name (as per wsrep_node_name) of the servers as donors, or the source of the state transfer, in order of preference. The donor-selection algorithm, in general, prefers a donor capable of transferring only the missing transactions (IST) to the joiner node, instead of the complete state (SST). Thus, it starts by looking for an IST-capable node in the given donor list followed by rest of the nodes in the cluster. In case multiple candidate nodes are found outside the specified donor list, the node in the same segment (gmcast.segment) as the joiner is preferred. If none of the existing nodes in the cluster can serve the missing transactions through IST, the algorithm moves on to look for a suitable node to transfer the entire state (SST). It first looks at the nodes specified in the donor list (irrespective of their segment). If no suitable donor is still found, the rest of the donor nodes are checked for suitability only if the donor list has a "terminating-comma". Note that a stateless node (the Galera arbitrator) can never be a donor. See Introduction to State Snapshot Transfers (SSTs) for more information.
  • Commandline: --wsrep-sst-donor=value
  • Scope: Global
  • Dynamic: Yes
  • Data Type: String
  • Default Value:

wsrep_sst_donor_rejects_queries

  • Description: If set to ON (OFF is default), the donor node will reject incoming queries, returning an UNKNOWN COMMAND error code. Can be used for informing load balancers that a node is unavailable.
  • Commandline: --wsrep-sst-donor-rejects-queries[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: Boolean
  • Default Value: OFF

wsrep_sst_method

wsrep_sst_receive_address

  • Description: This is the address where other nodes (donor) in the cluster connect to in order to send the state-transfer updates. If an address is not specified or its set to AUTO (default), mysqld uses --wsrep_node_address's value as the receiving address. However, if --wsrep_node_address is not set, it uses address from either --bind-address or tries to get one from the list of available network interfaces, in the same order. Note: setting it to localhost will make it impossible for nodes running on other hosts to reach this node. See Introduction to State Snapshot Transfers (SSTs) for more information.
  • Commandline: --wsrep-sst-receive-address=value
  • Scope: Global
  • Dynamic: Yes
  • Data Type: String
  • Default Value: AUTO

wsrep_start_position

  • Description: The start position that the node should use in the format: UUID:seq_no. The proper value to use for this position can be recovered with wsrep_recover.
  • Commandline: --wsrep-start-position=value
  • Scope: Global
  • Dynamic: Yes
  • Data Type: String
  • Default Value: 00000000-0000-0000-0000-000000000000:-1

wsrep_sync_wait

wsrep_trx_fragment_size

  • Description: Size of transaction fragments for streaming replication (measured in units as specified by wsrep_trx_fragment_unit)
  • Commandline: --wsrep-trx-fragment-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 2147483647
  • Introduced: MariaDB 10.4.2

wsrep_trx_fragment_unit

  • Description: Unit for streaming replication transaction fragments' size:
    • bytes: transaction’s binlog events buffer size in bytes
    • rows: number of rows affected by the transaction
    • statements: number of SQL statements executed in the multi-statement transaction
  • Commandline: --wsrep-trx-fragment-unit=value
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enum
  • Default Value: bytes
  • Valid Values: bytes, rows or statements
  • Introduced: MariaDB 10.4.2
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.