MariaDB Galera Cluster

From Gentoo Wiki
Jump to:navigation Jump to:search

Introduction

This is a guide for configuring a 3 node MariaDB Galera Cluster.
A MariaDB Galera cluster requires a minimal of 3 nodes. However, one of the members of the cluster can be an arbitrator (2 node + 1 arbitrator). Despite not participating in data replication, the arbitrator still needs to be on a 3rd physical node.
[1]

Design

The Maria Galera Cluster is multi master cluster featuring synchronous writes across all nodes. Each node requires a static IP address to function.

If you have a local dns server, you can make use of that. else make use of your hosts file. This will make your system clear about the other nodes and ip address.

FILE /etc/hostsadd you hostname if you don't have it
127.0.0.1   localhost
192.168.10.11   node01
192.168.10.12   node02
192.168.10.13   node03

[2] [3]

Packages

Add the galera useflag to MariaDB:

FILE /etc/portage/package.useenable galera replication support
dev-db/mariadb galera

Add the garbd useflag (only) on the arbitrator. If you have 3 nodes you can skip this useflag.

FILE /etc/portage/package.useadd this extra useflag
sys-cluster/galera garbd

Then, emerge the following packages:

root #emerge --ask dev-db/mariadb sys-cluster/galera

USE flags for dev-db/mariadb An enhanced, drop-in replacement for MySQL

backup Build mariadb-backup which supports SST and hot backup of InnoDB, Aria and MyISAM including compression and encryption
bindist Flag to enable or disable options for prebuilt (GRP) packages (eg. due to licensing issues)
columnstore Build the ColumnStore storage engine
cracklib Support for cracklib strong password checking
debug Enable extra debug codepaths, like asserts and extra output. If you want to get meaningful backtraces see https://wiki.gentoo.org/wiki/Project:Quality_Assurance/Backtraces
extraengine Add support for alternative storage engines (Archive, CSV, Blackhole, Federated(X), Partition)
galera Enables galera replication
innodb-lz4 Enables lz4 compression methods for InnoDB/XtraDB
innodb-lzo Enables lzo compression methods for InnoDB/XtraDB
innodb-snappy Enables snappy compression methods for InnoDB/XtraDB using app-arch/snappy
jdbc Enable the CONNECT engine to access foreign databases via JDBC
jemalloc Use dev-libs/jemalloc for memory management
kerberos Add kerberos support
latin1 Use LATIN1 encoding instead of UTF8
mroonga Add support for the Mroonga engine for interfacing with the Groonga text search
numa Enable NUMA support using sys-process/numactl (NUMA kernel support is also required)
odbc Add ODBC Support (Open DataBase Connectivity)
oqgraph Add support for the Open Query GRAPH engine
pam Enable the optional PAM authentication plugin for the server
perl Add optional support/bindings for the Perl language
profiling Add support for statement profiling (requires USE=community).
rocksdb Add support for RocksDB; a key/value, LSM database optimized for flash storage
s3 Build the S3 storage engine
selinux !!internal use only!! Security Enhanced Linux support, this must be set by the selinux profile or breakage will occur
server Build the server program
sphinx Add suport for the sphinx full-text search engine
sst-mariabackup Add tools needed to support the mariabackup SST method
sst-rsync Add tools needed to support the rsync SST method
static !!do not set this during bootstrap!! Causes binaries to be statically linked instead of dynamically
systemd Enable use of systemd-specific libraries and features like socket activation or session tracking
systemtap Build support for profiling and tracing using dev-debug/systemtap
tcmalloc Use the dev-util/google-perftools libraries to replace the malloc() implementation with a possibly faster one
test Install upstream testsuites for end use.
xml Add support for XML files
yassl Enable SSL connections and crypto functions using the bundled yaSSL

USE flags for sys-cluster/galera Synchronous multi-master replication engine that provides the wsrep API

garbd Install Galera Arbitrator - a stateless daemon which acts as a lightweight group member
ssl Add support for SSL/TLS connections (Secure Socket Layer / Transport Layer Security)
test Enable dependencies and/or preparations necessary to run tests (usually controlled by FEATURES=test but can be toggled independently)

Database Initialization

Run this command and keyin your mariadb root password

root #emerge --config dev-db/mariadb
Configuring pkg...

 * Please provide a password for the mysql 'root' user now
 * or through the /root/.my.cnf file.
 * Avoid ["'\_%] characters in the password
    >
 * Retype the password
    >
 * Creating the mysql database and setting proper
 * permissions on it ...
 * Command: '/usr/share/mysql/scripts/mysql_install_db'                                                                                                                                  [ ok ]
 * Setting root password ...                                                                                                                                           [ ok ]
 * Loading "zoneinfo", this step may require a few seconds ... ...                                                                                                     [ ok ]
 * Stopping the server ...
 * Done

Setup Mariadb and Galera

Node01

FILE /etc/mysql/my.cnfMake the change below
# Listen on (IPv4) all interfaces
bind-address                            = 0.0.0.0

# unique per-node id, greater than 0
server-id = 1 

wsrep_on
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# You should change this name to something meaningful
wsrep_cluster_name="my_mariadb_cluster"

# List all nodes of the cluster including this one
wsrep_cluster_address="gcomm://192.168.10.11,192.168.10.12,192.168.10.13"

# We will come back to change this again later
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.10.11"
wsrep_node_name="node01"

Node02

FILE /etc/mysql/my.cnfMake the change below
# Listen on (IPv4) all interfaces
bind-address                            = 0.0.0.0

# unique per-node id, greater than 0
server-id = 2

wsrep_on
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# You should change this name to something meaningful
wsrep_cluster_name="my_mariadb_cluster"

# List all nodes of the cluster including this one
wsrep_cluster_address="gcomm://192.168.10.11,192.168.10.12,192.168.10.13"

# We will come back to change this again later
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.10.12"
wsrep_node_name="node02"

Node03

FILE /etc/mysql/my.cnfMake the change below
# Listen on (IPv4) all interfaces
bind-address                            = 0.0.0.0

# unique per-node id, greater than 0
server-id = 3

wsrep_on
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# You should change this name to something meaningful
wsrep_cluster_name="my_mariadb_cluster"

# List all nodes of the cluster including this one
wsrep_cluster_address="gcomm://192.168.10.11,192.168.10.12,192.168.10.13"

# We will come back to change this again later
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.10.13"
wsrep_node_name="node03"

Start mariadb on the first node

When a node starts, by default it assumes that the cluster is already initialised and tries to join it. If no node is initialised, the cluster will not be able to start since all nodes will just try to join a (non) existing cluster. Therefore, the first node in the cluster to be online needs to be instructed to become a "new" cluster (read into STARTING THE FIRST CLUSTER NODE for more defaults):

root #/etc/init.d/mysql bootstrap_galera


Start mariadb on other nodes

root #/etc/init.d/mysql start

Checking if all nodes have joined and are running correctly

Try to run this command on node 1:

root #mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1102
Server version: 10.1.29-MariaDB Source distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep%';
# Focus on the 2 values below.
| wsrep_cluster_size           | 3                                     |
| wsrep_ready                  | ON                                    |

If the cluster size is equal to you node size you are good to go. And wsrep_ready meant the cluster is ON.

Garbd Arbitrator Configuration

Note
This is still a physical 3 node, just not running mariadb.
Warning
While the Galera Arbitrator does not participate in replication, it does receive the same data as all other nodes. You must secure its network connection.[1]
Warning
Unless this has been fixed in your installation, garbd does not work when specifying cluster address without the port. Make sure to include the port numbers as shown below.
FILE /etc/conf.d/garbdMake the change below
# A space-separated list of node addresses (address[:port]) in the cluster
GALERA_NODES="192.168.0.10:4567 192.168.0.11:4567"

# Galera cluster name, should be the same as on the rest of the nodes.
GALERA_GROUP="my_mariadb_cluster"
Note
Not idea why, but cannot turn on Log or it will not start
root #/etc/init.d/garbd start

Finalize configuration

The Mariadb cluster should now be up.

Start mariadb on boot

For openrc, do:

root #rc-update add mysql default
 * service mysql added to runlevel default

Secure mariadb

Secure your mariadb for production use

root #mysql_secure_installation

External Reference