Toggle source

Mariadb Galera cluster

The MariaDB and Galera cluster works with a master server, to which nodes are added that synchronize with each other.

When the master server goes offline, a new MySQL server is designated as master to continue serving the data.

Install & setup

On all nodes (master includes):

sudo apt install sudo mariadb-server && sudo systemctl stop mariadb

On master node:

sudo mkdir /etc/systemd/system/mariadb.service.d/ -p
sudo nano /etc/systemd/system/mariadb.service.d/timeout.conf

[Service]
TimeoutSec=infinity

sudo nano /etc/mysql/mariadb.conf.d/99-galera.cnf

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Can use ip or hostname (requires filling in /etc/hosts)
wsrep_cluster_address=gcomm://mariadb-node01,mariadb-node02,mariadb-node03
binlog_format=row
default_storage_engine=InnoDB
# innodb
innodb_autoinc_lock_mode=2
innodb_buffer_pool_size=128M
# Options
bind-address=0.0.0.0
wsrep_cluster_name=cluster
wsrep_node_name=master
wsrep_node_address=192.168.1.110
wsrep_sst_method=rsync

You need to customize:

  • wsrep_cluster_address
  • wsrep_cluster_name
  • wsrep_node_name
  • wsrep_node_address

See synced nodes from master:

mysql -u root -p

show status like 'wsrep_cluster_size';

On proxmox, clone master node and modify galera conf like this:

sudo nano /etc/mysql/mariadb.conf.d/99-galera.cnf

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://mariadb-node01,mariadb-node02,mariadb-node03
#wsrep_cluster_address=gcomm://
binlog_format=row
default_storage_engine=InnoDB
# innodb
innodb_autoinc_lock_mode=2
innodb_buffer_pool_size=128M
#options
bind-address=0.0.0.0
wsrep_cluster_name=cluster
wsrep_node_name=node02
wsrep_node_address=192.168.1.111
wsrep_sst_method=rsync

Same on other nodes.

Start galera on master node:

sudo galera_new_cluster

Don't forget to adapt things and complete /etc/hosts if needed.

Import

If cluster doesn't want to start

Connect to master node and set like this:

mysql -u root -p
SET GLOBAL wsrep_provider_options='pc.bootstrap=YES';

IPV6

See here

Change wsrep sync method

We can use rsync or mariadb backup.

This how to use mariadb backup.

First, we need to create a mysql user:

mysql -u root -p
CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'secure_password';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';
FLUSH PRIVILEGES;

Then we need to change the galera conf:

#wsrep_sst_method=rsync
wsrep_sst_method=mariabackup
wsrep_sst_auth=mariabackup:secure_password

Restart nodes.

We can do this node by node.