Header Ads

Setup Galera Cluster on MariaDB for Debian 10

Galera cluster feature in MariaDB allows syncing databases between servers. If you haven't installed MariaDB, check our older posts on LEMP stack and LAMP stack.


MariaDB Galera Cluster on Debian


In this guide, let's create a source-source cluster with two nodes (servers). Source nodes can both read and write to the cluster, whereas replica nodes can only read.

Here we assume, the first node has x.x.x.x as IP address and example1.com as host-name and second with y.y.y.y and example2.com respectively. Same guide can be extended for any number of nodes in a cluster. Replace both IP addresses and host-names as required.

Open both nodes on two separate terminal tabs, so it will be easy to match and replicate commands on each node.


1. Preparing each node with latest packages

Check MariaDB version and upgrade every node to include following packages.

Debian 10 distro now comes with MariaDB 10.3 which includes support for galera cluster. If MariaDB version is less than 10.1, it should be upgraded.

To check version, execute following command.

`mysql -u root -e "SELECT VERSION();"`

Install Rsync if not installed already.

`apt install rsync`


2. Setup Galera on first node (example1.com)

Edit the galera config file

`nano /etc/mysql/conf.d/galera.cnf`

And add/update following lines (replace x.x.x.x, example1.com, example_cluster and y.y.y.y)

`[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Cluster Configuration [this cluster] [change placeholder values]
wsrep_cluster_name="example_cluster"
wsrep_cluster_address="gcomm://x.x.x.x,y.y.y.y"

# Galera Node Configuration [this node] [change placeholder values]
wsrep_node_address="x.x.x.x"
wsrep_node_name="example1.com"`


3. Setup Galera on second node (example2.com)

Edit the galera config file

`nano /etc/mysql/conf.d/galera.cnf`

And add/update following lines (replace x.x.x.x, example2.com, example_cluster and y.y.y.y)

`[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Cluster Configuration [this cluster] [change placeholder values]
wsrep_cluster_name="example_cluster"
wsrep_cluster_address="gcomm://x.x.x.x,y.y.y.y"

# Galera Node Configuration [this node] [change placeholder values]
wsrep_node_address="y.y.y.y"
wsrep_node_name="example2.com"`

4. Allow incoming connections on first node (example1.com)

If using iptables, as mentioned in our previous guide (Installing LEMP stack), execute the following lines on terminal as commands to open ports for rsync(873) and galera(3306,4567,4568,4444) incoming connections.

Check if iptables rules are configured properly

`iptables -S`

If fail2ban is installed, reset iptables rules and restore from saved file

`iptables-save | awk '/^[*]/ { print $1 } /^:[A-Z]+ [^-]/ { print $1 " ACCEPT" ; } /COMMIT/ { print $0; }' | iptables-restore`

`iptables-restore < /etc/iptables.rules`

Replace y.y.y.y with the IP address of second node (example2.com).

`iptables -A INPUT -p tcp -s y.y.y.y/32 -m multiport --dports 873,3306,4567,4568,4444 --syn -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT`

`iptables -A INPUT -p udp -s y.y.y.y/32 --dport 4567 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT`

To check and backup new set of iptables rules

`iptables -S`

`iptables-save > /etc/iptables.rules`


5. Allow incoming connections on second node (example2.com)

Replace x.x.x.x with the IP address of first node (example1.com).

`iptables -A INPUT -p tcp -s x.x.x.x/32 -m multiport --dports 873,3306,4567,4568,4444 --syn -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT`

`iptables -A INPUT -p udp -s x.x.x.x/32 --dport 4567 --syn -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT`

To backup new set of iptables rules

`iptables-save > /etc/iptables.rules`


6. Stop all nodes before starting them with Galera

On all nodes, stop mariadb and mysql processes

`service mariadb stop`

`killall -9 mysql mysqld_safe mysqld mysql-systemd`


7. Start first node with galera

Since we are bootstrapping (first time) our cluster, chose a node to start them with the galera_new_cluster script. Execute in command line,

`galera_new_cluster`

Verify number of nodes active in the cluster

`mysql -u root -e "SHOW STATUS LIKE 'wsrep_cluster_size'"`


8. Start second node with galera

On the second node, a simple restart can connect the node to the cluster

`systemctl restart mariadb`

Verify again for number of nodes active in the cluster. It should be now 2 on both nodes.

`mysql -u root -e "SHOW STATUS LIKE 'wsrep_cluster_size'"`

 

9. Optional

9.A To restart cluster when both nodes are offline

Check grastate.dat file for safe_to_bootstrap flag on both nodes

`cat /var/lib/mysql/grastate.dat`

If any node has safe_to_bootstrap set to 1, boot MariaDB on that node first and then the other nodes.

If both nodes are set to 0, then manually set one of it to 1. And run commands on step 7 on that node and step 8 on other nodes.

No comments