Posted on by Ondřej Lachman

How to Make High Available Mysql Cluster with Galera

Databases are often the most critical part of an infrastructure. Failure of a database server often leads to a failure of other components because a database server is required for their functionality.

Everyone does backups, and it is the first thing you should do if the data stored in your database server is crucial, but backups are run weekly, daily, hourly… And sometimes even a few minutes of outage can cause the loss of a lot of money, that’s when high availability comes to mind.

Galera is synchronous multi-master replication for innoDB, the main advantage is that applications do not required the knowledge as to which mysql server they can write to, they can write to any node in galera cluster and commits are then applied on other nodes in cluster.

Let’s start with the setup.

Galera requires at least 3 nodes, so we need 3 servers with mariadb installed, let’s look at the setup of the first node

sudo nano /etc/mysql/mariadb.cnf

Uncomment or add these lines

character-set-server = utf8
character_set_server = utf8

Then we need to add galera specific configuration

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

Add below config for node1

[mysqld]
bind-address=0.0.0.0
default_storage_engine=InnoDB
binlog_format=row
innodb_autoinc_lock_mode=2

# Galera cluster configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://(“ip1,ip2,ip3”)
wsrep_cluster_name="galera-cluster"
wsrep_sst_method=rsync

# Cluster node configuration
wsrep_node_address="ip1
wsrep_node_name="node-1".

Add config on other cluster nodes, don’t forget to change wsrep_node_address and wsrep_node_name Stop mariadb on all nodes

service mariadb stop

on node1 run this command to start a new cluster

galera_new_cluster

Enter mysql and check galera cluster status with

show status like 'wsrep_%';

Look for wsrep_cluster_size, value should be 1

Start mariadb on node2

service mariadb start

And again, check for galera cluster

show status like 'wsrep_%';

wsrep_cluster-size should be 2 now, follow the same orders with node3, size should be 3 after that

let’s create a test database to see if the syncing nodes work

On node1

MariaDB [(node1)]> create database testdb;
Query OK, 1 row affected (0.038 sec)

And let’s check if new db is present on node2

MariaDB [(node2)]> show databases;
| Database           |
+--------------------+
| testdb             |

As we can see, our database is present, so the galera cluster is synced and working!

Ondřej Lachman