Getting Started with MariaDB Galera Cluster
Who hasn't desired to install a MariaDB GaleraDB cluster? There is a new best practice guide to follow.
Explain how Versions works
Version numbering in Galera consists of three different components:
- Galera wsrep (write-set replication) Patch for MariaDB or MySQL
- Galera Replication Shared Library (libgalera_smm.so)
- Galera wsrep API
1.1 The patch component version number appears usually together with the API version number in the server version string of MariaDB.
Versions example
Server version: 5.5.38-MariaDB-wsrep-log MariaDB Server, wsrep_25.10.r3997
5.5.38-MariaDB-wsrep-log Maria
wsrep_25.10.r3997 Galera version
25 API Galera wsrep API version
10 PATCH release for the MySQL or MariaDB
r3997 the last bazaar commit in the Galera wsrep mysql patch source tree
25 LIBRARY (same as wsrep API)
wsrep_25.10.r3997 Galera version
25 API Galera wsrep API version
10 PATCH release for the MySQL or MariaDB
r3997 the last bazaar commit in the Galera wsrep mysql patch source tree
25 LIBRARY (same as wsrep API)
- shared library
The Galera replication shared library (2) should be the same version as the Galera wsrep API version (3).
If you are installing the library for the first time, check the package version number before installing it.
However, if the API is already installed, you can check which version the server is already running by executing the following within the mysql or mariadb client:mysql> SHOW GLOBAL STATUS LIKE 'wsrep_provider_version'; # The name of the wsrep Provider version string. +------------------------+------------+ | Variable_name | Value | +------------------------+------------+ | wsrep_provider_version | 25.3.5(rXXXX) | +------------------------+------------+ 1 row in set (0.07 sec) - Galera cluster is included starting from MariaDB 10.1
To use MariaDB Galera Cluster, there are two primary packages that you need to install:
- A MariaDB Server version that supports Galera Cluster.
- The Galera wsrep provider library.
In MariaDB 10.1 and above, Galera Cluster support is actually included in the standard MariaDB Server packages.
Previously, standard MariaDB Server package + you will also have to install an additional package to obtain the Galera wsrep provider library.
our GC is configured with this package versions
Galera S1
select '$f', @@version,@@version_comment, @@innodb_version;
10.10.19.3 10.0.21-MariaDB-1~precise-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4144 5.6.25-73.1- MariaDB 10.0.21-MariaDB-1~precise-wsrep-log (https://mariadb.com/kb/en/library/mariadb-10021-release-notes/)
- MySQL-wsrep wsrep_25.10.r4144
- Galera-3 25.3.9-precise Replication framework for transactional applications (dpkg)dpkg --list | grep -i galer
root@ifactory-s1-db-node-03:~# dpkg --list| grep -E "maria|galera" rc galera 25.3.5-wheezy Galera Replication Framework ii galera-3 25.3.9-precise Replication framework for transactional applications ii libmariadbclient18 10.0.21+maria-1~precise MariaDB database client library ii libmysqlclient18 10.0.21+maria-1~precise Virtual package to satisfy external depends ii mariadb-client-10.0 10.0.21+maria-1~precise MariaDB database client binaries ii mariadb-client-core-10.0 10.0.21+maria-1~precise MariaDB database core client binaries ii mariadb-common 10.0.21+maria-1~precise MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf) ii mariadb-galera-server 10.0.21+maria-1~precise MariaDB database server with Galera cluster ii mariadb-galera-server-10.0 10.0.21+maria-1~precise MariaDB database server with Galera cluster binaries ii mysql-common 10.0.21+maria-1~precise MariaDB database common files (e.g. /etc/mysql/my.cnf)
Mirrors to download the current Galera cluster versions
- Ubuntu 12.04.5 LTS Precise
- MariaDB 10.0 https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-100/
- Galera-3 http://ftp.hosteurope.de/mirror/archive.mariadb.org/mariadb-10.0.21/repo/ubuntu/pool/main/g/galera-3/
- MariaDB http://ftp.hosteurope.de/mirror/archive.mariadb.org/mariadb-10.0.21/repo/ubuntu/pool/main/m/mariadb-10.0/
- Last available stable versions
https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/
Galera Cluster consists of two parts:
the Galera Replication Library (galera-3) and
a version of MySQL extended with the Write Set Replication (WSREP) API (mysql-wsrep).
the Galera Replication Library (galera-3) and
a version of MySQL extended with the Write Set Replication (WSREP) API (mysql-wsrep).
mariadb versions available
mariadb 10.3last stable version of MariaDB 10.3 is MariaDB 10.3.19 Stable (GA)
wsrep is inside the MariaDB and the Galera wsrep library updated to 25.3.28mariadb 10.4last stable version of MariaDB 10.4 is MariaDB 10.4.8 will be a Stable (GA) release.
Installation of Galera-3 & MariaDB server
Goal install a MariaDB Galera cluster
Version needs to be compatible with Ubuntu 16.04 Xenial
Version needs to be compatible with Ubuntu 16.04 Xenial
environment
cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04.6 LTS"
packages
- The Galera wsrep provider library.
Galera 25.3.14-1 default available in the Ubuntu 16 repos.
apt-cache showpkg galera-3
Package: galera-3
Versions: 25.3.14-1
Provides:
25.3.14-1 - wsrep (= ) percona-xtradb-cluster-galera-25 (= ) galera3 (= ) galera (= )
dpkg -L galera-3
/.
/usr
/usr/lib
/usr/lib/galera
/usr/lib/galera/libgalera_smm.so
....
/usr/lib/libgalera_smm.so- A MariaDB Server version that supports Galera Cluster.
Galera-3 packages (Galera 25.3.14) supports MariaDB 10.1.12.
However, MariaDB server default available in the Ubuntu~precise is 10.0.38
(https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-100/)
And MariaDB 10.0 is no longer being supported. Please use a more recent release. Hence use 10.1
However, MariaDB server default available in the Ubuntu~precise is 10.0.38
(https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-100/)
And MariaDB 10.0 is no longer being supported. Please use a more recent release. Hence use 10.1
apt-cache show mariadb-server
Version: 10.1.43+maria-1~xenial
Installed-Size: 10
Depends: mariadb-server-10.1 (= 10.1.43+maria-1~xenial)
Packages version to install
- MariaDB-server 10.1.43 Stable
- Galera-3 packages (Galera 25.3.14) supports MariaDB 10.1.12.
When a node is receiving a state transfer it cannot process and apply incoming writesets because it has no state to apply them to yet
Swap Size Requirements
the writesets are cached in memory and, if the system runs out of memory either the state transfer will fail or the cluster will block waiting for the state transfer to end.
the writesets are cached in memory and, if the system runs out of memory either the state transfer will fail or the cluster will block waiting for the state transfer to end.
To control memory usage for writeset caching, check the Galera parameters:
- gcs.recv_q_hard_limit, (Maximum allowed size of recv queue. This should normally be half of (RAM + swap). If this limit is exceeded, Galera Cluster will abort the server.)
- gcs.recv_q_soft_limit, (The fraction of gcs.recv_q_hard_limit after which replication rate will be throttled.)
- gcs.max_throttle (How much to throttle replication rate during state transfer (to avoid running out of memory)
GALERA S1
gcs.max_throttle = 0.25;
gcs.recv_q_hard_limit = 9223372036854775807;
gcs.recv_q_soft_limit = 0.25;
gcs.max_throttle = 0.25;
gcs.recv_q_hard_limit = 9223372036854775807;
gcs.recv_q_soft_limit = 0.25;
- Currently, replication works only with the InnoDB storage engine
- Any writes to tables of other types, including system (MySQL) tables are not replicated (this limitation excludes DDL statements such as CREATE USER, which implicitly modify the mysql. tables
- Unsupported explicit locking include LOCK TABLES, FLUSH TABLES {explicit table list} WITH READ LOCK,
- All tables should have a primary key (multi-column primary keys are supported)
- DELETE operations are unsupported on tables without a primary key.
- The general query log and the slow query log cannot be directed to a table. If you enable these logs, then you must forward the log to a file by setting log_output=FILE.
- XA transactions not supported https://mariadb.com/kb/en/library/xa-transactions/
- Transaction size. While Galera does not explicitly limit the transaction size, a writeset is processed as a single memory-resident buffer and as a result, extremely large transactions (e.g. LOAD DATA) may adversely affect node performance. To avoid that, the wsrep_max_ws_rows and wsrep_max_ws_size system variables limit transaction rows to 128K and the transaction size to 1Gb by default. If necessary, users may want to increase those limits. Future versions will add support for transaction fragmentation.
- While binlog_format is checked on startup and can only be ROW
- Performance: by design performance of the cluster cannot be higher than performance of the slowest node;
- Replication filters: When using Galera cluster, replication filters should be used with caution. See Configuring MariaDB Galera Cluster: Replication - - Filters for more details. See also MDEV-421 and MDEV-6229.
- FLUSH PRIVILEGES is not replicated.
- The disk-based Galera gcache is not encrypted
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup > mariadb_repo_setup
bash mariadb_repo_setup --mariadb-server-version=10.1
$sudo apt-cache search mariadb-server
sudo: unable to resolve host ip-10-112-50-125
auth2db - Powerful and eye-candy IDS logger, log viewer and alert generator
mariadb-server-10.0 - MariaDB database server binaries
mariadb-server-core-10.0 - MariaDB database core server files
mariadb-server - MariaDB database server (metapackage depending on the latest version)
mariadb-server-10.1 - MariaDB database server binaries
mariadb-server-10.1-dbgsym - Debug symbols for mariadb-server-10.1
mariadb-server-core-10.1 - MariaDB database core server files
mariadb-server-core-10.1-dbgsym - Debug symbols for mariadb-server-core-10.1
In MariaDB 10.1 and later, Galera Cluster support has been included in the standard MariaDB Server packages, so you will need to install the mariadb-server package, as you normally would.
sudo apt-get install mariadb-server mariadb-client galera
galera-3 is already the newest version (25.3.28-xenial).
Optional package if the node is an arbitrator and not a proper Galera node
This package should be installed on whatever node you want to serve as the arbitrator.
This package should be installed on whatever node you want to serve as the arbitrator.
sudo apt-get install galera-arbitrator-3
Configuring the MariaDB options files
cat /etc/mysql/my.cnf
# MariaDB database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
To have a clear configuration setting, the Galera options should be moved to an external options file.
Example
Configure the wsrep_cluster_address with the list of server IP belonging to the cluster
Configure the wsrep_cluster_address with the list of server IP belonging to the cluster
vi /etc/mysql/conf.d/galera.cnf
[mysqld]
server-id=00000001
# Mandatory settings
# DONT use [galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.112.50.125,10.112.50.121,10.112.50.118"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
# Allow server to accept connections on all interfaces.
bind-address=0.0.0.0
# Optional setting
wsrep_cluster_name="playground_cluster"
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=0
query_cache_size=0 # mandatory for the galeradb
query_cache_type=0 # mandatory for the galeradb
log_error=/var/log/mysql/mysql.err # Otherwise the log messages can be found in the journal
# Optional setting to verify
# wsrep_sst_method = xtrabackup-v2
# wsrep_sst_auth = "xtrabackup:xxxxxxxxxxxxxxxxx"
# wsrep_provider_options="gcache.size=10G"
Several options are mandatory, which means that they must be set in order for Galera Cluster to be enabled or to work properly with MariaDB
- wsrep_provider — Path to the Galera library
- wsrep_cluster_address — See Galera Cluster address format and usage
- binlog_format=ROW — See Binary Log Formats
- default_storage_engine=InnoDB
- innodb_autoinc_lock_mode=2
- innodb_doublewrite=1 — This is the default value, but it should not be changed when using Galera provider version >= 2.0.
- query_cache_size=0 — Only mandatory for MariaDB versions prior to MariaDB Galera Cluster 5.5.40, MariaDB Galera Cluster 10.0.14, and MariaDB 10.1.2.
- wsrep_on=ON — Enable wsrep replication (starting 10.1.1)
- innodb_flush_log_at_trx_commit=0 — This is not usually recommended in the case of standard MariaDB
Network Ports
Galera Cluster needs access to the following 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.
- 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.
To first node of a new cluster needs to be bootstrapped by starting mysqld on that node with the option --wsrep-new-cluster option.
This option tells the node that there is no existing cluster to connect to.
The node will create a new UUID to identify the new cluster.
This option tells the node that there is no existing cluster to connect to.
The node will create a new UUID to identify the new cluster.
the way to bootstrap a new cluster depends on the OS and what system manager uses
Different methods to bootstrapping the new cluster
- If systemd is used (preferred method)
root@galera-1:~$galera_new_cluster systemctl status mariadb.service systemctl status mysql
- Manually start the server
mysqld --wsrep-new-cluster - If Sysvinit is the definite system manager
service mysql bootstrap
!! Do not use the --wsrep-new-cluster option when connecting to an existing cluster. Restarting the node with this option set will cause the node to create new UUID to identify the cluster again, and the node won't reconnect to the old cluster. See the next section about how to reconnect to an existing cluster.
Once you have a cluster running and you want to add/reconnect another node to it,
you must supply an address of one or more of the existing cluster members in the wsrep_cluster_address option
you must supply an address of one or more of the existing cluster members in the wsrep_cluster_address option
galera-node-2 & galera-node-3
systemctl status mariadb.service # stop it is running
Verify that the wsrep_cluster_address option variable is set up correctly in the config file
It is even OK to list a node's own IP address in wsrep_cluster_address, since Galera Cluster is smart enough to ignore it.
$grep "wsrep_cluster_address" /etc/mysql/conf.d/galera.cnf
wsrep_cluster_address="gcomm://10.112.50.125,10.112.50.121,10.112.50.118"
Restarting the Cluster
If you shut down all nodes at the same time, then you have effectively terminated the cluster.
Of course, the cluster's data still exists, but the running cluster no longer exists.
When this happens, you'll need to bootstrap the cluster again.
Of course, the cluster's data still exists, but the running cluster no longer exists.
When this happens, you'll need to bootstrap the cluster again.
If the cluster is not bootstrapped and mysqld on the first node is just started normally,
then the node will try to connect to at least one of the nodes listed in the wsrep_cluster_address option.
If no nodes are currently running, then this will fail.
Bootstrapping the first node solves this problem.
then the node will try to connect to at least one of the nodes listed in the wsrep_cluster_address option.
If no nodes are currently running, then this will fail.
Bootstrapping the first node solves this problem.
In some cases Galera will refuse to bootstrap a node if it detects that it might not be the most advanced node in the cluster.
If you know for sure which node is the most advanced you can edit the grastate.dat file in the datadir.
You can set safe_to_bootstrap=1 on the most advanced node.
You can set safe_to_bootstrap=1 on the most advanced node.
sed -i 's/safe_to_bootstrap=0/safe_to_bootstrap=1/' /var/lib/mysql/grastate.dat
You can determine which node is the most advanced by checking grastate.dat on each node and looking for the node with the highest seqno. If the node crashed and seqno=-1, then you can find the most advanced node by recovering the seqno on each node with the wsrep_recover option
mysqld --wsrep_recover
OR
galera_recovery # with systemd as system manager
WSREP: Recovered position fca1f6a5-177e-11ea-a151-c7f8b08bc0bc:11
--wsrep_start_position=fca1f6a5-177e-11ea-a151-c7f8b08bc0bc:11
it redirects mysqld log output to a file named with the format /tmp/wsrep_recovery.XXXXXX
Once the safe_to_bootstrap variable is changed to be 1, the cluster should restart and recover
cat /var/lib/mysql/grastate.dat
=> safe_to_bootstrap=1
galera_new_cluster # First node
systemctl start mysql # Second/Third nodes
!! To note
Using wsrep_start_position hasn't worked out if all nodes of the cluster were crashed,
service mysql start --wsrep_start_position=8d211006-5bf5-11e2-0800-067f71542765:434636
Using wsrep_start_position hasn't worked out if all nodes of the cluster were crashed,
service mysql start --wsrep_start_position=8d211006-5bf5-11e2-0800-067f71542765:434636
User Case - Three servers crashed
Cluster with three nodes, the three servers were shut down brutally without stopping first the cluster.
Cluster with three nodes, the three servers were shut down brutally without stopping first the cluster.
Node1-Node2-Node3 have the grastate.dat reporting the same content
root@galera-1:cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid: fca1f6a5-177e-11ea-a151-c7f8b08bc0bc
seqno: -1
safe_to_bootstrap: 0
State Snapshot Transfers (SSTs)
SST
Full data copy
In a State Snapshot Transfer (SST), the cluster provisions nodes by transferring a full data copy from one node to another.
When a new node joins the cluster, the new node initiates a State Snapshot Transfer to synchronize its data with a node that is already part of the cluster.
SST
Full data copy
In a State Snapshot Transfer (SST), the cluster provisions nodes by transferring a full data copy from one node to another.
When a new node joins the cluster, the new node initiates a State Snapshot Transfer to synchronize its data with a node that is already part of the cluster.
Incremental State Transfers (ISTs)
In an Incremental State Transfer (SST), the cluster provisions nodes by transferring a node's missing writesets from one node to another. When a new node joins the cluster, the new node initiates a Incremental State Transfer to synchronize its data with a node that is already part of the cluster.
In an Incremental State Transfer (SST), the cluster provisions nodes by transferring a node's missing writesets from one node to another. When a new node joins the cluster, the new node initiates a Incremental State Transfer to synchronize its data with a node that is already part of the cluster.
If a node has only been out of a cluster for a little while, then an IST is generally faster than an SST.
Monitoring
SHOW GLOBAL STATUS LIKE 'wsrep_%';
Cluster Change Notifications
Cluster-change-notifications
The cluster nodes can be configured to invoke a command when cluster membership or node status changes.
This mechanism can also be used to communicate the event to some external monitoring agent.
This is configured by setting wsrep_notify_cmd. See Galera Cluster documentation: Notification Command for more information.
Cluster-change-notifications
The cluster nodes can be configured to invoke a command when cluster membership or node status changes.
This mechanism can also be used to communicate the event to some external monitoring agent.
This is configured by setting wsrep_notify_cmd. See Galera Cluster documentation: Notification Command for more information.
auth_socket issue
Add the auth_socket plugin to the have it loaded by the server
vi /etc/mysql/mariadb.conf.d/50-server.cnf
under [mysqld]
plugin-load-add = auth_socket.so
sudo systemctl restart mariadb.service
systemctl status mariadb.service
systemctl status mysql
If you plan to disable the auth_socket plugin
disable unix socket authentication for the root user…
use mysql;
update user set plugin='' where User='root';
flush privileges;
Error that reveals the missing plugin
ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded
Error log
MariaDB always writes its error log, but the destination is configurable.
Writing the Error Log to a File
To configure the error log to be written to a file, you can set the log_error system variable. You can configure a specific file name.
However, if a specific file name is not configured, then the log will be written to the ${hostname}.err file in the datadir directory by default.
To configure the error log to be written to a file, you can set the log_error system variable. You can configure a specific file name.
However, if a specific file name is not configured, then the log will be written to the ${hostname}.err file in the datadir directory by default.
log_error=/var/log/mysql/mysql.err
Writing the Error Log to Syslog on Unix
systemd also has its own logging system called the journal, and some errors may get logged there instead. See Systemd:Systemd Journal for more information.
journalctl -r Default Option File Locations on Linux, Unix, Mac
In MariaDB 10.0.13 and later, the locations are dependent on whether the DEFAULT_SYSCONFDIR cmake option was defined when MariaDB was built. This option is usually defined as /etc
Location Scope
/etc/my.cnf Global
/etc/mysql/my.cnf Global
$MYSQL_HOME/my.cnf Server
defaults-extra-file File specified with --defaults-extra-file, if any
~/.my.cnf User
/etc/my.cnf Global
/etc/mysql/my.cnf Global
$MYSQL_HOME/my.cnf Server
defaults-extra-file File specified with --defaults-extra-file, if any
~/.my.cnf User
links
https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/
https://mariadb.com/resources/blog/deciphering-galera-version-numbers/
https://mariadb.com/kb/en/meta/galera-versions/
https://mariadb.com/resources/blog/deciphering-galera-version-numbers/
https://mariadb.com/kb/en/meta/galera-versions/
Where is the wsrep provider option???
mysql> select @@wsrep_provider_options\G
*************************** 1. row ***************************
@@wsrep_provider_options: base_host = 10.10.19.3; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 2; evs.version = 0; evs.view_forget_timeout = P1D; gcache.dir = /data/mysql/data/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/mysql/data//galera.cache; gcache.page_size = 128M; gcache.size = 10G; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500;
gcs.max_throttle = 0.25;
gcs.recv_q_hard_limit = 9223372036854775807;
gcs.recv_q_soft_limit = 0.25;
gcs.sync_donor = no; gmcast.listen_addr = tcp://0.0.0.0:4567; gmcast.mcast_addr = ; gmcast.mcast_ttl = 1; gmcast.peer_timeout = PT3S; gmcast.segment = 0; gmcast.time_wait = PT5S; gmcast.version = 0; ist.recv_addr = 10.10.19.3; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.linger = PT20S; pc.npvo = false; pc.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = P30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 7; socket.checksum = 2;
1 row in set (0.01 sec)