Call us at 1-888-GTCOMM1
MySQL Galera is a plugin that transforms MySQL from a single-server database engine, into a clustered solution with synchronous replication and sophisticated conflict resolution. With Galera installed, MySQL can be scaled out like a more traditional app server. While MySQL clients work as if connected to a single database instance, they are in fact interacting with a cluster that spans multiple database nodes, providing high availability and transparent failover.
Getting Started
Confirm that you have the following before you follow this guide:
• 1 Node (Cloud Server or Dedicated Server) running Ubuntu 16.
• Root access to the node or one sudo non-root user
Tutorial
A Galera installation isn’t nearly as useful without multiple nodes, please be aware that you must always have an odd number of node to have a quorum for a production use. This example will simulate a cluster by running two distinct local instances on separate IPs. For production use, you’ll perform this installation on several separate servers that can communicate directly with each other
Begin by installing the software-properties package. This gives us a command for easily installing Ubuntu Personal Package Archives (PPAs), separate repositories which users and teams use for providing custom software or updates to existing packages.
apt-get install software-properties-common -y
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
We’ll then use a utility shipped as part of the software-properties package to add the MariaDB PPA. This repository contains the Galera plugin.
add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://ftp.utexas.edu/mariadb/repo/10.1/ubuntu xenial main'
The repository is installed, but you’ll need to update the list of available packages. You must also install the MariaDB server package.
apt-get update
apt-get install mariadb-server rsync -y
Galera needs a configuration file. Let’s create that.
nano /etc/mysql/conf.d/galera.cnf
[mysqld]
#mysql settings
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
#galera settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="test_cluster"
wsrep_cluster_address=gcomm://10.0.0.119,10.0.0.120
wsrep_sst_method=rsync
In this example, we’ll use the IPs 10.0.0.119 and 10.0.0.120.
Before Galera can start, we need to ensure that MySQL is stopped on both nodes.
systemctl stop mysql.service
On the first node, start the Galera cluster.
galera_new_cluster
Having done this, we need to ensure that the cluster is in fact running so our second node has something to which to connect.
mysql -u root -p -e "show status like 'wsrep_cluster_size'"
If things are working, you should see this:
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 1 |
+--------------------+-------+
On the second node, launch the MySQL daemon.
systemctl start mysql.service
The second node should have automatically linked to the cluster. We’ll verify that here.
mysql -u root -p -e "show status like 'wsrep_cluster_size'"
If everything is running, you should see this:
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+
Great. Let’s create a database and see if it propagates across the Galera cluster. Run this command on your first node:
mysql -u root -p
create database galera_test;
show databases like 'galera_test';
+------------------------+
| Database (galera_test) |
+------------------------+
| galera_test |
+------------------------+
1 row in set (0.00 sec)
Now we verify that the change has appeared on the second node.
mysql -u root -p
show databases like 'galera_test';
+------------------------+
| Database (galera_test) |
+------------------------+
| galera_test |
+------------------------+
1 row in set (0.00 sec)
Conclusion
While this is only an example cluster, what you’ve learned here can easily be scaled up to several, or even dozens, of distinct nodes. If you enjoyed this article, be sure to share it with your friends so they too can build highly-available MySQL clusters!
