Tutorial

How To Configure a Galera Cluster with MySQL 5.6 on Ubuntu 16.04

Published on September 2, 2016
How To Configure a Galera Cluster with MySQL 5.6 on Ubuntu 16.04
Not using Ubuntu 16.04?Choose a different version or distribution.
Ubuntu 16.04

Introduction

Clustering adds high availability to your database by distributing changes across different servers. In the event that one of the instances fails, others are already available to continue serving.

Clusters come in two general configurations, active-passive and active-active. In active-passive clusters, all writes are done on a single active server and then copied to one or more passive servers that are poised to take over only in the event of an active server failure. Some active-passive clusters also allow SELECT operations on passive nodes. In an active-active cluster, every node is read-write and a change made to one is replicated to all.

In this guide, we will configure an active-active MySQL Galera cluster. For demonstration purposes, we will configure and test three nodes, the smallest configurable cluster.

Prerequisites

To follow along, you will need three Ubuntu 16.04 servers, each with:

Once all of these prerequisites are in place, we’re ready to install the software.

Step 1 — Adding the Galera Repository to All Servers

MySQL, patched to include Galera clustering, isn’t included in the default Ubuntu repositories, so we’ll start by adding the external Ubuntu repositories maintained by the Galera project to all three of our servers.

Note: Codership, the company behind Galera Cluster, maintains this repository, but be aware that not all external repositories are reliable. Be sure to install only from trusted sources.

On each server, add the repository key with the apt-key command, which apt will use to verify that the packages are authentic.

  1. sudo apt-key adv --keyserver keyserver.ubuntu.com --recv BC19DDBA

Once we have the trusted key in each server’s database, we can add the repositories. To do so, create a new file called galera.list within the /etc/apt/sources.list.d/ on each server:

  1. sudo nano /etc/apt/sources.list.d/galera.list

In the text editor, add the following lines which will make the appropriate repositories available to the APT package manager:

/etc/apt/sources.list.d/galera.list
deb http://releases.galeracluster.com/mysql-wsrep-5.6/ubuntu xenial main
deb http://releases.galeracluster.com/galera-3/ubuntu xenial main

Save and close the file (press CTRL + X, Y, then ENTER).

The Codership repositories are now available to all three of your servers. However, it’s important that you instruct apt to prefer Codership’s repositories over others to ensure that it installs the patched versions of the software needed to create a Galera cluster. To do this, create another new file called galera.pref within the /etc/apt/preferences.d/ directory:

  1. sudo nano /etc/apt/preferences.d/galera.pref

Add the following lines to the text editor:

/etc/apt/preferences.d/galera.pref
# Prefer Codership repository
Package: *
Pin: origin releases.galeracluster.com
Pin-Priority: 1001

Save and close that file, and then run sudo apt-get update in order to include package manifests from the new repositories:

  1. sudo apt-get update

You may see a warning that the signature uses weak digest algorithm (SHA1). There is an open issue on GitHub to address this. In the meantime, it’s okay to proceed.

Once the repositories are updated on all three servers, we’re ready to install MySQL and Galera.

Step 2 — Installing MySQL and Galera on All Servers

Run the following command on all three servers to install a version of MySQL patched to work with Galera, as well as Galera and several dependencies:

  1. sudo apt-get install galera-3 galera-arbitrator-3 mysql-wsrep-5.6

During the installation, you will be asked to set a password for the MySQL administrative user.

We should have all of the pieces necessary to begin configuring the cluster, but since we’ll be relying on rsync in later steps, let’s make sure it’s installed on all three, as well:

  1. sudo apt-get install rsync

This will confirm that the newest version of rsync is already available, prompt you to upgrade the version you have, or install it.

Once we have installed MySQL on each of the three servers, we can begin configuration.

Step 3 — Configuring the First Node

Each node in the cluster needs to have a nearly identical configuration. Because of this, we will do all of the configuration on our first machine, and then copy it to the other nodes.

By default, MySQL is configured to check the /etc/mysql/conf.d directory to get additional configuration settings from files ending in .cnf. We will create a file in this directory with all of our cluster-specific directives:

  1. sudo nano /etc/mysql/conf.d/galera.cnf

Add the following configuration into the file. You will need to change the settings highlighted in red. We’ll explain what each section means below.

/etc/mysql/conf.d/galera.cnf on the first node
[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 Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://first_ip,second_ip,third_ip"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="this_node_ip"
wsrep_node_name="this_node_name"
  • The first section modifies or re-asserts MySQL settings that will allow the cluster to function correctly. For example, Galera Cluster won’t work with MyISAM or similar non-transactional storage engines, and mysqld must not be bound to the IP address for localhost. You can learn about the settings in more detail on the Galera Cluster system configuration page.

  • The “Galera Provider Configuration” section configures the MySQL components that provide a write-set replication API. This means Galera in our case, since Galera is a wsrep (write-set replication) provider. We specify the general parameters to configure the initial replication environment. This doesn’t require any customization, but you can learn more about Galera configuration options.

  • The “Galera Cluster Configuration” section defines the cluster, identifying the cluster members by IP address or resolvable domain name, and creating a name for the cluster to ensure that members join the correct group. You can change the wsrep_cluster_name to something more meaningful than test_cluster or leave it as-is, but you must update wsrep_cluster_address with the addresses of your three servers. If your servers have private IP addresses, use them here.

  • The “Galera Synchronization Configuration” section defines how the cluster will communicate and synchronize data between members. This is used only for the state transfer that happens when a node comes online. For our initial setup, we are using rsync, because it’s commonly available and does what we need for now.

  • The “Galera Node Configuration” section clarifies the IP address and the name of the current server. This is helpful when trying to diagnose problems in logs and for referencing each server in multiple ways. The wsrep_node_address must match the address of the machine you’re on, but you can choose any name you want in order to help you identify the node in log files.

When you are satisfied with your cluster configuration file, copy the contents into your clipboard and then save and close the file.

Now that the first server is configured, we’ll move on to the next two nodes.

Step 4 — Configuring the Remaining Nodes

On each of the remaining nodes, open the configuration file:

  1. sudo nano /etc/mysql/conf.d/galera.cnf

Paste in the configuration you copied from the first node, then update the “Galera Node Configuration” to use the IP address or resolvable domain name for the specific node you’re setting up. Finally, update its name, which you can set to whatever helps you identify the node in your log files:

/etc/mysql/conf.d/galera.cnf
. . .
# Galera Node Configuration
wsrep_node_address="this_node_ip"
wsrep_node_name="this_node_name"
. . .

Save and exit the file on each server.

We’re almost ready to bring up the cluster, but before we do, we’ll want to make sure that the appropriate ports are open.

Step 5 — Opening the Firewall on Every Server

On every server, let’s check the status of the firewall:

  1. sudo ufw status

In this case, only SSH is allowed through:

Output
Status: active To Action From -- ------ ---- OpenSSH ALLOW Anywhere OpenSSH (v6) ALLOW Anywhere (v6)

You may have other rules in place or no firewall rules at all. Since only SSH traffic is permitted in this case, you’ll need to add rules for MySQL and Galera traffic.

Galera can make use of four ports:

  • 3306 is used for MySQL client connections and State Snapshot Transfer that use the mysqldump method.
  • 4567 is used by Galera Cluster for replication traffic, multicast replication uses both UDP transport and TCP on this port.
  • 4568 is used for Incremental State Transfer.
  • 4444 is used for all other State Snapshot Transfer.

In our example, we’ll open all four ports while we do our setup. Once we’ve confirmed that replication is working, we will close any ports we’re not actually using and restrict traffic to just servers in the cluster.

Open the ports with the following command:

  1. sudo ufw allow 3306,4567,4568,4444/tcp
  2. sudo ufw allow 4567/udp

Note: Depending on what else is running on your servers, you might want restrict access right away. The UFW Essentials: Common Firewall Rules and Commands guide can help with this.

Step 6 — Starting the Cluster

To begin, we need to stop the running MySQL service so that our cluster can be brought online.

Stop MySQL on all Three Servers:

Use the command below on all three servers to stop mysql so that we can bring them back up in a cluster:

  1. sudo systemctl stop mysql

systemctl doesn’t display the outcome of all service management commands, so to be sure we succeeded, run the following command:

  1. sudo systemctl status mysql

If the last line looks something like the following, the command was successful.

Output
. . . Sep 02 22:17:56 galera-02 systemd[1]: Stopped LSB: start and stop MySQL.

Once we’ve shut down mysql on all of the servers, we’re ready to proceed.

Bring up the First Node:

The way we’ve configured our cluster, each node that comes online tries to connect to at least one other node specified in its galera.cnf file to get its initial state. A normal systemctl start mysql would fail because there are no nodes running for the first node to connect with, so we need to pass the wsrep-new-cluster parameter to the first node we start. However, neither systemd nor service will properly accept the --wsrep-new-cluster argument at this time, so we’ll need to start the first node using the startup script in /etc/init.d. Once you’ve done this, you can start the remaining nodes with systemctl.

Note: If you prefer them all to be started with systemd, once you have another node up, you can kill the initial node. Since the second node is available, when you restart the first one with sudo systemctl start mysql it will be able to join the running cluster

  1. sudo /etc/init.d/mysql start --wsrep-new-cluster

When this script completes, the node is registered as part of the cluster, and we can see it with the following command:

  1. mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
Output
+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 1 | +--------------------+-------+

On the remaining nodes, we can start mysql normally. They will search for any member of the cluster list that is online, so when they find one, they will join the cluster.

Bring up the Second Node:

Start mysql:

  1. sudo systemctl start mysql

We should see our cluster size increase as each node comes online:

  1. mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
Output
+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 2 | +--------------------+-------+

Bring up the Third Node:

Start mysql:

  1. sudo systemctl start mysql

If everything is working well, the cluster size should be set to three:

  1. mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
Output
+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+

At this point, the entire cluster should be online and communicating. With that, we can test replication between each of the cluster’s nodes.

Step 7 — Testing Replication

We’ve gone through the steps up to this point so that our cluster can perform replication from any node to any other node, known as active-active replication. Let’s test to see if the replication is working as expected.

Write to the First Node:

We’ll start by making database changes on our first node. The following commands will create a database called playground and a table inside of this called equipment.

  1. mysql -u root -p -e 'CREATE DATABASE playground;
  2. CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));
  3. INSERT INTO playground.equipment (type, quant, color) VALUES ("slide", 2, "blue");'

We now have one value in our table.

Read and Write on the Second Node:

Next, we’ll look at the second node to verify that replication is working:

  1. mysql -u root -p -e 'SELECT * FROM playground.equipment;'

If replication is working, the data we entered on the first node will be visible here on the second:

Output
+----+-------+-------+-------+ | id | type | quant | color | +----+-------+-------+-------+ | 1 | slide | 2 | blue | +----+-------+-------+-------+

From this same node, we can write data to the cluster:

  1. mysql -u root -p -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("swing", 10, "yellow");'

Read and Write on the Third Node:

From the third node, we can read all of this data by querying the database again:

  1. mysql -u root -p -e 'SELECT * FROM playground.equipment;'
Output
+----+-------+-------+--------+ | id | type | quant | color | +----+-------+-------+--------+ | 1 | slide | 2 | blue | | 2 | swing | 10 | yellow | +----+-------+-------+--------+

Again, we can add another value from this node:

  1. mysql -u root -p -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("seesaw", 3, "green");'

Read on the First Node:

Back on the first node, we can verify that our data is available everywhere:

  1. mysql -u root -p -e 'SELECT * FROM playground.equipment;'
Output
+----+--------+-------+--------+ | id | type | quant | color | +----+--------+-------+--------+ | 1 | slide | 2 | blue | | 2 | swing | 10 | yellow | | 3 | seesaw | 3 | green | +----+--------+-------+--------+

We’ve tested we can write to all of the nodes and that replication is being performed properly.

Conclusion

At this point, you should have a working three-node Galera test cluster configured. If you plan on using a Galera cluster in a production situation, it’s recommended that you begin with no fewer than five nodes.

Before production use, you may want to take a look at some of the other state snapshot transfer (sst) agents like “xtrabackup" which allows you to set up new nodes very quickly and without large interruptions to your active nodes. This does not affect the actual replication, but is a concern when nodes are being initialized. Finally, to protect your data as it moves between servers, you should also set up SSL encryption.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

The repositories in this article are incorrect. Refer to the official documentation here:

http://galeracluster.com/documentation-webpages/installmysql.html

TL;DR use the following setting repositories:

apt-key adv --keyserver keyserver.ubuntu.com \
      --recv BC19DDBA # Should already be configured
deb http://releases.galeracluster.com/mysql-wsrep-5.6/ubuntu xenial main
deb http://releases.galeracluster.com/galera-3/ubuntu xenial main

WARNING

Galera 3 + MySQL 5.7 + Ubuntu 16.04 has some bugs and I couldn’t find a workaround https://github.com/codership/mysql-wsrep/issues/324

Hope this saves someone else the headache I just experienced.

Hi there,

I have a Problem: After every Reboot the System deletes the folder /var/run/mysqld Because of that it can’t start/find the socket. Can someone please help me?

Thanks

Thank you, got it working 64 bit.

Hi,

“query_cache_type=0”

Why do you disable query cache?

query cache usefully some web application(joomla, opencart etc.)

Can you tell us what are the know limitations for having active-active with Galera?

I have single node MySQL, and I want to convert it to a cluster for high availability, and I’m worried about any limitation when going with active/active or even active passive

This comment has been deleted

    I have just done an apt-get update and see this in the output:

    Err:14 http://releases.galeracluster.com/ubuntu xenial/main i386 Packages 404 Not Found Ign:15 http://releases.galeracluster.com/ubuntu xenial/main amd64 Packages Fetched 3,906 kB in 2s (1,462 kB/s) Reading package lists… Done W: The repository ‘http://releases.galeracluster.com/ubuntu xenial Release’ does not have a Release file. N: Data from such a repository can’t be authenticated and is therefore potentially dangerous to use. N: See apt-secure(8) manpage for repository creation and user configuration details. E: Failed to fetch http://releases.galeracluster.com/ubuntu/dists/xenial/main/binary-i386/Packages 404 Not Found E: Some index files failed to download. They have been ignored, or old ones used instead.

    I am on Ubuntu Xenial 64-bit.

    What is causing this and does it need to be addressed ?

    hello, i have 3 virtual machine with ubuntu 16.04 64bit, i follow the tutorial, i fine it work good, i have replication in three server, when i reboot the machine or shutdown and start again does not work replication can not start mysql server in any virtual machine, mysql server says: The server quit without updating PID file, help me help me help me

    could someone please tell me, how to implement it for mysql 7 .

    Thanks

    Try DigitalOcean for free

    Click below to sign up and get $200 of credit to try our products over 60 days!

    Sign up

    Join the Tech Talk
    Success! Thank you! Please check your email for further details.

    Please complete your information!

    Get our biweekly newsletter

    Sign up for Infrastructure as a Newsletter.

    Hollie's Hub for Good

    Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

    Become a contributor

    Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

    Welcome to the developer cloud

    DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

    Learn more
    DigitalOcean Cloud Control Panel