Set Up MariaDB Clusters with Galera Debian and Ubuntu
Updated by James Stewart
How to Configure Galera Clusters for MariaDB Replication
MariaDB replication with Galera adds redundancy for a site’s database. With database replication, multiple servers act as a database cluster. Database clustering is particularly useful for high availability website configurations. This guide uses three separate Linodes to configure database replication, each with private IPv4 addresses on Debian and Ubuntu.
NoteThis guide assumes that your Linodes are each configured with a Private IP Address.
Install Required Packages
To install the required packages, first add the keys for the Galera repository by running:
sudo apt-get install python-software-properties sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
Add the repository for your distribution:
sudo add-apt-repository ‘deb http://mirror3.layerjet.com/mariadb/repo/5.5/debian wheezy main’
add-apt-repository ‘deb http://mirror3.layerjet.com/mariadb/repo/5.5/ubuntu trusty main’
Install MariaDB, Galera, and Rsync:
sudo apt-get update && sudo apt-get install -y rsync galera mariadb-galera-server
Create the file
/etc/mysql/conf.d/galera.cnfon each of the Linodes with the following content. Replace the IP addresses in the
wsrep_cluster_addresssection with the private IP addresses of each of the Linodes:
1 2 3 4 5 6 7 8 910111213
[mysqld] #mysql settings binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 query_cache_size=0 query_cache_type=0 bind-address=0.0.0.0 #galera settings wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_name="my_wsrep_cluster" wsrep_cluster_address="gcomm://192.168.1.1,192.168.1.2,192.168.1.3" wsrep_sst_method=rsync
Reboot both of your non-primary servers in the cluster to enable the new
Stop the MariaDB service on each of your Linodes:
sudo service mysql stop
Restart the MariaDB service on the primary Linode, with the
sudo service mysql start --wsrep-new-cluster
Confirm that the cluster has started:
mysql -u root -p -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
You should receive an output of the current cluster size:
MariaDB [(none)]> SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"; +--------------+ | cluster size | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)
Start the MariaDB service on the other two Linodes. Re-run the command from step 5 to ensure that each system has joined the cluster:
sudo service mysql start
To prevent repeated errors on startup, copy the
/etc/mysql/debian.cnffile from your primary Linode in the cluster to each of your other Linodes, overwriting the existing copies.
Reboot both of your secondary Linodes to apply the new
Testing database replication
Log in to MariaDB on each of the Linodes:
mysql -u root -p
To test, create a database and insert a row on your primary Linode:
create database test; create table test.flowers (`id` varchar(10));
From each of the other servers, list the tables in your test database:
show tables in test;
You should receive an output of the database and row that you created in the previous step:
MariaDB [(none)]> show tables in test; +----------------+ | Tables_in_test | +----------------+ | flowers | +----------------+ 1 row in set (0.00 sec)
Congratulations, you have now configured a MariaDB cluster with Galera.
You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.
- How to Set Up MariaDB on Debian 9
- Use One-Time Passwords for Two-Factor Authentication with SSH on Ubuntu 16.04 and Debian 8
- Obtain a Commercially Signed SSL Certificate on Debian & Ubuntu
- How to Configure a Firewall with UFW
- LAMP on Debian 8 (Jessie)
This guide is published under a CC BY-ND 4.0 license.