Configuring MariaDB Galera Cluster

Configure MariaDB Galera Cluster with the mandatory wsrep options, performance settings, replication binary log behavior, network ports, and multi-instance setup on a single host.

A number of options must be set for Galera Cluster to work with MariaDB. These should be set in the MariaDB option file.

Mandatory Options

Several options are mandatory, meaning they must be set for Galera Cluster to be enabled or to work properly. The mandatory options are:

• wsrep_provider — Path to the Galera library

• wsrep_cluster_address — See Galera Cluster address format and usage

• binlog_format=ROW — See Binary Log Formats

• wsrep_on=ON — Enable wsrep replication

• default_storage_engine=InnoDB — This is the default value, or alternately wsrep_replicate_myisam=1 (before MariaDB 10.6) or wsrep_mode=REPLICATE_ARIA,REPLICATE_MYISAM (MariaDB 10.6 and later).

  • innodb_doublewrite=1 — This is the default value, and should not be changed.

Performance-related Options

These are optional optimizations that can be made to improve performance.

• innodb_flush_log_at_trx_commit=0 or innodb_flush_log_at_trx_commit=2 — These settings can result in significantly better write performance by relaxing InnoDB's ACID durability. However, using these values introduces a risk of losing acknowledged transactions during simultaneous power failures, orchestrated server terminations, or a replicated trigger of a crashing bug across the cluster. For maximum durability, the default value of 1 is recommended.

• innodb_autoinc_lock_mode=2 — This tells InnoDB to use interleaved method. Interleaved is the fastest and most scalable lock mode, and should be used when BINLOG_FORMAT is set to ROW.

  Setting the auto-increment lock mode for InnoDB to interleaved, you’re allowing slaves threads to operate in parallel.

• wsrep_slave_threads=4 — This makes state transfers quicker for new nodes. You should start with four slave threads per CPU core.

  The logic here is that, in a balanced system, four slave threads can typically saturate a CPU core. However, I/O performance can increase this figure several times over. For example, a single-core ThinkPad R51 with a 4200 RPM drive can use thirty-two slave threads. The value should not be set higher than wsrep_cert_deps_distance.

Writing Replicated Write Sets to the Binary Log

Like with MariaDB replication, write sets that are received by a node with Galera Cluster's certification-based replication are not written to the binary log by default. If you would like a node to write its replicated write sets to the binary log, then you will have to set log_slave_updates=ON. This is especially helpful if the node is a replication master. See Using MariaDB Replication with MariaDB Galera Cluster: Configuring a Cluster Node as a Replication Master.

Replication Filters

Like with MariaDB replication, replication filters can be used to filter write sets from being replicated by Galera Cluster's certification-based replication. However, they should be used with caution because they may not work as you'd expect.

The following replication filters are honored for InnoDB DML, but not DDL:

• binlog_do_db

• binlog_ignore_db

• replicate_wild_do_table

• replicate_wild_ignore_table

The following replication filters are honored for DML and DDL for tables that use both the InnoDB and MyISAM storage engines:

• replicate_do_table

• replicate_ignore_table

However, it should be kept in mind that if replication filters cause inconsistencies that lead to replication errors, then nodes may abort.

See also MDEV-421 and MDEV-6229.

Network Ports

Galera Cluster needs access to the following ports:

• Standard MariaDB Port (default: 3306) - For MySQL client connections and State Snapshot Transfers that use the mysqldump method. This can be changed by setting port.

• Galera Replication Port (default: 4567) - For Galera Cluster replication traffic, multicast replication uses both UDP transport and TCP on this port. Can be changed by setting wsrep_node_address.

• Galera Replication Listening Interface (default: 0.0.0.0:4567) needs to be set using gmcast.listen_addr, either

  • in wsrep_provider_options: wsrep_provider_options='gmcast.listen_addr=tcp://<IP_ADDR>:<PORT>;'

  • or in wsrep_cluster_address

• IST Port (default: 4568) - For Incremental State Transfers. Can be changed by setting ist.recv_addr in wsrep_provider_options.

• SST Port (default: 4444) - For all State Snapshot Transfer methods other than mysqldump. Can be changed by setting wsrep_sst_receive_address.

Multiple Galera Cluster Instances on One Server

If you want to run multiple Galera Cluster instances on one server, then you can do so by starting each instance with mysqld_multi, or if you are using systemd, then you can use the relevant systemd method for interacting with multiple MariaDB instances.

You need to ensure that each instance is configured with a different datadir.

You also need to ensure that each instance is configured with different network ports.

_{This page is licensed: CC BY-SA / Gnu FDL}