This tutorial will be showing you how to set up MariaDB Galera Cluster on Ubuntu 18.04, 18.10 and 16.04 server. MariaDB is an open-source drop-in replacement for MySQL database server.
What is Galera Cluster?
Previously, I talked about master-slave replication in MariaDB. It’s a setup where data modifications on the master server will be replicated to the slave, but changes on the slave will not be replicated to the master. Galera Cluster is a synchronous multi-master cluster for MySQL, MariaDB and Percona database servers to implement a high-performance and high-availability for data refundancy. A multi-master cluster allows read and write to any node in the cluster. Data modifications on any node is replicated to all other nodes.
Galera cluster is an open-source data replication and clustering technology developed by Codership, a Finnish company. There are 3 versions of Galera Cluster:
- Galear cluster for MySQL: the original Galera developed by Codership
- MariaDB Galera cluster: a fork of Codership Galera
- Percona XtraDB cluster: another fork of Codership Galera
In this tutorial, we will be using the MariaDB Galera cluster.
Features and Benefits of MariaDB Galera Cluster
- High availability. If any individual node in the cluster fails, the other nodes can continue providing service without the need of manual failover procedures.
- High data consistency. Galera cluster uses synchronous replication, so no slave lag or diverged data is allowed between the nodes and no data is lost after a node crash. Transactions are committed in the same order on all nodes.
- Active-active multi-master topology.
- Read and write to any cluster node. The cluster acts like a standalone MariaDB server.
- Both read and write scalability. No need to split read and write on different nodes.
- Automatic membership control. Failed nodes drop from the cluster.
- Automatic node provisioning. No need to manually dump database and import it on new nodes.
- Multiple-threaded slave, true parallel replication, on row level
- Transparent to applications. Direct client connections, native MariaDB look & feel
- Smaller client latencies
With Galera cluster, you can eliminate single point of failure and achieve better performance at the same time. Galera cluster performs well both in LAN and WAN environments. You can have nodes in the cloud, even on small server instances, across multiple data centers and different continents. Together with a file synchronization tool like Resilio Sync and an anycast CDN + load balancing service like Clouflare, website owners can bring their contents as close to visitors as possible no matter where visitors are located.
Prerequisites of Setting Up Galera Cluster on Ubuntu
How many nodes should you put in the cluster? Well, there are no upper limit, but you should always choose an odd number: 3, 5, 7 and so on， to prevent the split brain problem, which I will talk about in a future article. Galera cluster requires at least 3 nodes to be crash-safe. To follow this tutorial, you will need at least 3 MariaDB database servers running on Ubuntu, each server with at least 512MB RAM. Use 1GB RAM or above on each server for smooth operation and better performance. It’s recommended to use the same hardware configuration on every node because the cluster will be as slow as the slowest node.
In this tutorial, I’m using 3 Vultr VPS (Virtual Private Server) in 3 different data centers (Silicon Valley, Frankfurt and Singapore), so my database will still be available in case there is a power outage/network problem in one of the data centers. You can either install MariaDB server from the default Ubuntu repository, or install the latest version from mariadb.org repository, and it’s recommended to install the same version on your servers.
Note: 1) Galera cluster only runs on Linux and Unix-like OS. Microsoft Windows is not supported. 2) If your Galera cluster spans continents, there will be latency from 100ms to 300ms. The latency between my 3 servers is around 165ms. I also found that sometimes IPv6 latency is significantly higher than IPv4 latency and other times IPv4 latency is lower than IPv6 latency.
All MariaDB servers must be using InnoDB or XtraDB storage engine, because Galera cluster only supports these two storage engines. Any writes to tables of other engines will not be replicated to other nodes. To check the default storage engines used by your database, log into MariaDB monitor and run the following statement:
MariaDB [(none)]> show variables like 'default_storage_engine';
Note that a database may have tables that use different storage engines. To check, run the following statement. Replace “database_name” with your real database name.
MariaDB [(none)]> select table_name,engine from information_schema.tables where table_schema="database_name" and engine="myISAM";
If you found a table using storage engine other than InnoDB, you can change it to InnoDB. For example, to change a table from using MyISAM to InnoDB, run
MariaDB [(none)]> use database_name; MariaDB [(none)]> alter table table_name engine = InnoDB;
The first statement selects a particular database and the second statement will change the storage engine of a table to InnoDB. The tables in the default 3 databases (
performance_schema) don’t use InnoDB/XtraDB storage engine and there’s no need to change it.
I also recommend reading known limitations of MariaDB Galera cluster before setting up a Galera cluster.
Step 1: Configuring Each Node in the Cluster
Prior to MariaDB 10.1, sysadmins need to install the
mariadb-galera-server package in order to set up a cluster. As of MariaDB 10.1, the Galera cluster feature is bundled into MariaDB. If you have MariaDB 10.1 or above running on Ubuntu 18.04, you just need to install one more package:
galera-3 – the Galera wsrep (write-set replication) provider library.
sudo apt install galera-3
Usually, this package is automatically installed when you install MariaDB server on Ubuntu. Now run the following command to edit the main MariaDB configuration file on each node. (If the
50-server.cnf file doesn’t exist on your Ubuntu server, then edit
/etc/my.cnf config file.)
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Add the following configurations in the
[mysqld] # Galera Cluster configurations wsrep_on = ON wsrep_provider = /usr/lib/galera/libgalera_smm.so wsrep_cluster_address = "gcomm://IP_address_of_node1,IP_address_of_node2,IP_address_of_node3" default_storage_engine = InnoDB binlog_format = row innodb_autoinc_lock_mode = 2 innodb_force_primary_key = 1 innodb_doublewrite = 1
- The first variable enables write-set replication.
- The second variable specifies the location of wsrep library. Usually it’s
/usr/lib/libgalera_smm.sofile is a symbolic link.
- The third variable defines the IP address of very node in the cluster, separated by comma.
- Galera only supports InnoDB or its fork XtraDB, so it’s important to set the
- Binary log is needed for Galera cluster and its format must be ROW. Statement-based or mixed replication isn’t supported.
innodb_autoinc_locak_modevariable has 3 possible values: 0, 1 or 2. We must set it to 2 (interleaved lock mode) for Galera cluster.
- Galera cluster requires all tables having a primary key (Invisible primary key is not supported). So it’s a good idea to enforce a primary key on every table. CREATE TABLE without primary key will not be accepted, and will return an error. This is also true when you import a database.
- InnoDB doublewrite buffer is enabled by default and it should not be changed when using Galera wsrep provider library version 3.
You can optionally add the following lines in
wsrep_cluster_name = MyCluster wsrep_node_name = MyNode1 wsrep_node_address = "IP_address_of_this_node" innodb_flush_log_at_trx_commit=0
- The first variable sets a name for the cluster. Use the same cluster name on every node in the cluster.
- The second variable sets a name for an individual node.
- The third variable sets the IP address for an individual node.
- The last line ensures that the InnoDB log buffer is written to file once per second, rather than on each transaction commit, to improve performance. Note that if all cluster nodes goes down at the same time, the last second of transaction will be lost because of this line. If the cluster nodes are spread across different data centers, then no need to worry about this.
If you are running MariaDB 10.1 server, you also need to add the following line to disable XA transactions because it’s not supported by Galera.
innodb_support_xa = 0
If you run MariaDB 10.3, you should not add this line because it’s on by default and it can’t be disabled. It’s said to be fully supported in MariaDB 10.4 Galera cluster.
Note: Old version of Galera cluster doesn’t support query cache (query_cache_size). It’s supported by all current versions of MariaDB Galera.
And you must comment out the following line or change the bind address to 0.0.0.0 to make MariaDB server listen on the public IP address as well, so it can communicate with other nodes.
bind-address = 127.0.0.1
Save and close the file. Don’t restart MariaDB server now.
Step 2: Opening Network Ports in Firewall
Galera cluster requires constant communication between all the nodes due to the use of synchronous replication and they communicate with each other using the following TCP ports.
- 3306 (standard MariaDB port)
- 4444 (SST port)
- 4567 (Galera replication port)
- 4568 (IST port)
You need to configure firewall to allow traffic to these ports from the IP addresses of the cluster nodes. If you are using UFW, you can run the following commands on each node.
sudo ufw insert 1 allow in from IP_Address_of_node1 sudo ufw insert 1 allow in from IP_Address_of_node2 sudo ufw insert 1 allow in from IP_Address_of_node3
If you use iptables, then run the following commands.
sudo iptables -I INPUT -p tcp --source IP_address_of_node1 -j ACCEPT sudo iptables -I INPUT -p tcp --source IP_address_of_node2 -j ACCEPT sudo iptables -I INPUT -p tcp --source IP_address_of_node3 -j ACCEPT
Step 3: Configuring AppArmor for mysqld
AppArmor is enabled by default on Ubuntu and it can block communication on non-standard MariaDB ports, preventing Galera cluster from working, so we need to add AppArmor policy to allow MariaDB to open additional non-standard ports with the following commands.
cd /etc/apparmor.d/disable/ sudo ln -s /etc/apparmor.d/usr.sbin.mysqld sudo systemctl restart apparmor
Note that the MariaDB server package for Ubuntu now ships with an empty AppArmor profile (
/etc/apparmor.d/usr.sbin.mysqld), effectively disabling AppArmor for MariaDB, so you don’t need to run the above commands any more.
Step 4: Starting the Cluster
Now we need to start the cluster primary component on the first node. Choose a node that has the database as the first node and stop the MariaDB server on the first node.
sudo systemctl stop mariadb
Then run the following command to start the primary component on the first node. (Note: If you don’t stop MariaDB first, then the following command has no effect.)
Now you can log into MariaDB monitor.
mysql -u root -p
And check the cluster size.
show status like 'wsrep_cluster_size';
You will see that there’s only 1 node in the cluster.
To add other nodes to the cluster, simply restart MariaDB server on other nodes. (Note: if the other nodes have other databases, then those databases will be deleted. Only databases from the first node will exist.)
sudo systemctl restart mariadb
This command may take a while to complete, because when the new nodes join the cluster, they need to do a snapshot state transfer (SST), i.e. copy the databases from the first node, which can consume a lot of RAM and bandwidth. You can check the SST log with:
sudo journalctl -eu mariadb
After the other two nodes successfully joined the cluster, the cluster size changes to 3.
If you import a new database on any of the nodes now, this database will be replicated to other nodes. To check if data modifications has been synced, run the following statement at the MariaDB monitor.
show status like 'wsrep_local_state_comment';
You can check other Galera status with:
show status like 'wsrep%';
If any of the nodes, including the the first one, crashes and be kicked out of the cluster as a result, you just need to restart the MariaDB server and the crashed node will rejoin the cluster. You must not run the
sudo galera_new_cluster command again unless the cluster shuts down (All nodes in the cluster are offline).
Tip For WordPress Users
As mentioned before, Galera cluster requires every table in the database having a primary key. WordPress core tables all have primary key. However, some plugins may create tables without primary key in your WordPress database. Here’s what you should do to make sure all tables have primary key.
First, I recommend using the Plugins Garbage Collector to remove leftover tables in your WordPress database. Then you should dump your WordPress database and import it on one of the Gelera nodes. If all of the tables have primary key, the import will finish without error. If any table in the WordPress database doesn’t have a primary key, then the import will fail. This is because we added the
innodb_force_primary_key = 1 parameter in MariaDB Galera configuration.
Dropping a Node From MariaDB Galera Cluster
First, log into MariaDB monitor and run the following statement:
show status like 'wsrep_local_state_comment';
If the state is synced, you can safely dropping the node from cluster by stopping MariaDB server.
sudo systemctl stop mariadb
On the other two nodes, run the following statement at the MariaDB monitor.
show status like 'wsrep%';
wsrep_cluster_size changes to 2 and the IP address of the dropped nodes isn’t listed in
wsrep_incoming_address any more, which indicates the node has been successfully dropped.
To rejoin the cluster, simply restart MariaDB again.
sudo systemctl restart mariadb
If you don’t want a node to join the cluster again, then delete the Galera related settings in the main configuration file and restart MariaDB.
To drop a node without stopping MariaDB server, you need to lock the tables.
MariaDB [(none)]> flush tables with read lock;
This way you can create a backup using mysqldump. After that, unlock the tables for this node to rejoin the cluster.
MariaDB [(none)]> unlock tables;
Adding New Nodes to the Cluster
Galera cluster requires at least 3 nodes to be crash-safe and it’s recommended that you add more nodes to the cluster to make it more robust. To add new nodes to the cluster, you need to:
- Add the Galera configurations in the
50-server.conffile on the new nodes, open network port in firewall and update AppArmor policy.
- Add the IP addresses of new nodes in the
wsrep_cluster_addressvariable on each node.
- Restart MariaDB server on existing nodes in the cluster one by one. (Only restart the next MariaDB server after the previous one has finished restarting.)
- Restart MariaDB server on the new nodes so that they can join the cluster.
Hint: Always deploy an odd number of nodes to Galera cluster.
Shutting Down or Restarting MariaDB Galera Cluster
The cluster disappears when all nodes are offline at the same time. To shut down the cluster, you need to shut down all nodes. First, make sure that your application isn’t using the database and the
wsrep_local_state_comment is synced. Then shut down MariaDB server one by one.
To restart the Galera Cluster, run the following command on the last node to leave the cluster.
Then start MariaDB server on other nodes one by one.
sudo systemctl start mariadb
Galera Cluster Health
Sometimes there will be a network partition in the cluster due to network connectivity failure. For example, if one node lose network connectivity with the other two nodes, then this node will change from a primary component to non-primary component. The other two nodes can connect to each other and they will still be in the primary component.
You can check this status with the following statements at the MariaDB monitor.
show status like 'wsrep_cluster_status';
When a node is in non-primary component, both read and write queries will be disabled on that node. To rejoin it to the primary component, simply restart MariaDB server on the node in non-primary component.
sudo systemctl restart mariadb
I hope this tutorial helped you set up MariaDB Galera cluster on Ubuntu 18.04, 18.10 or Ubuntu 16.04, but this is a beginning in Galera cluster. In later articles, I will talk about encrypting replication traffic in Galera cluster and backup strategy. As always, if you found this post useful, then subscribe to our free newsletter to get more tips and tricks. Take care 🙂