A MariaDB Howto authored by: Erkan Yanar.
This is a Howto about installing MariaDB Galera Cluster on Debian/Ubuntu. Because a lot of people were having problems installing MariaDB Galera Cluster, elenst from
#maria on freenode forced me to write this Howto 🙂
Installing MariaDB Galera Cluster is in fact quite easy and actually kind of boring in the end. This Howto is written for (and tested on) on Debian 7.1 (Wheezy) and Ubuntu 12.04 (Precise).
What we need
In our setup we assume 3 nodes (node01, node02, node03) with one interface each. We assume following IP addresses: 172.16.8.5, 172.16.8.6, and 172.16.8.4. We need three packages installed on all nodes:
As Galera does not ship with the distribution repositories, go for the repo configurator and follow the instructions to include the repository fitting your system. Keep in mind to Choose “5.5” in Step 3 (Choose a Version). Doing this you can jump directly to Install Packages
Adding the Repository
Alternatively you can just take following steps.
apt-get install python-software-properties apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db add-apt-repository 'deb http://mirror3.layerjet.com/mariadb/repo/5.5/debian wheezy main' apt-get update
apt-get install python-software-properties apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db add-apt-repository 'deb http://mirror3.layerjet.com/mariadb/repo/5.5/ubuntu precise main' apt-get update
Yes, they are nearly identical. 🙂
(Just another shortcut for the impatient)
DEBIAN_FRONTEND=noninteractive apt-get install -y rsync galera mariadb-galera-server
After installing the packages you will have a running MariaDB server on each node. But none of them will be configured to run as a node in a MariaDB Galera Cluster.
So we have to do some configuration next. There is a MariaDB configuration part and one part to configure Galera (starting with
wsrep_). As we do the most basic and simple installation in this Howto, it is sufficient you just change the IP’s (Remember: 172.16.8.5, 172.16.8.6, 172.16.8.4) with your IP’s.
This will be needed to define the
wsrep_cluster_address Variable (the list of nodes a starting mysqld contacts to join the cluster).
The following configuration file has to be distributed on all nodes. We use a separate configuration file
/etc/mysql/conf.d/galera.cnf with the following settings:
[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://172.16.8.5,172.16.8.6,172.16.8.4" wsrep_sst_method=rsync
FYI: The shared library for
wsrep_provider is provided by the installed galera package.
We could also change the cluster name by changing the value of
wserp_cluster_name to fit our style. This setting also works as a shared secret to control the access to the cluster. With
wsrep_cluster_address you see the IP addresses of our setup. The
wsrep_sst_method tells what method to use to synchronise the nodes. While there are also
xtrabackup available, I prefer rsync because it is easy to configure (i.e. it does not need any credentials set on the nodes). If you are considering using the xtrabackup method, don’t forget to install xtrabackup.
Starting the Galera Cluster
First we stop mysqld on all nodes.
node01# service mysql stop node02# service mysql stop node03# service mysql stop
The configuration file (
galera.cnf) is already distributed to all nodes, so we next start the first mysqld. This node initializes/starts the cluster (creates a GTID).
node01# service mysql start --wsrep-new-cluster
To have a look and see if everything really worked we’ll check the cluster size status variable.
node01# mysql -u root -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
+--------------+ | cluster size | +--------------+ | 1 | +--------------+
If you see the above, great! That’s what we would expect. Now that the Cluster already exists, we let the next nodes just start and join the cluster.
node2# service mysql start [ ok ] Starting MariaDB database server: mysqld . . . . . . . . . .. [info] Checking for corrupt, not cleanly closed and upgrade needing tables.. node01:/home/debian# ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
We can ignore the above error for now. This node is still starting fine.
Let’s pause here and do a quick check. As we are running a cluster it is not important if we execute the following on
mysql -u root -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
+--------------+ | cluster size | +--------------+ | 2 | +--------------+
If you see the above, very nice! Now let’s start the third node:
node3# service mysql start [ ok ] Starting MariaDB database server: mysqld . . . . . . . . . .. [info] Checking for corrupt, not cleanly closed and upgrade needing tables.. node03:/home/debian# ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES) node03# mysql -u root -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
+--------------+ | cluster size | +--------------+ | 3 | +--------------+
Ok we are finished. We have a running MariaDB Galera Cluster o/
Having fun with Debian/Ubuntu init scripts
But we’ve got to fix some things because of some Debian/Ubuntu oddities.
Remember the error we saw when starting node02 and node03? What happened? Well, Debian/Ubuntu uses a special user (
'debian-sys-maint'@'localhost') in their init script and the credentials for that user are stored in
/etc/mysql/debian.cnf. This user is used to make some checks starting MySQL. Checks I don’t think belong into a service script anyway.
We could simply ignore it, but the user user is also used to shutdown mysqld. This is also not required, as a SIGTERM is sufficient to shutdown the mysqld :/
As we copied the data from
node01 to all other nodes, the credentials in
/etc/mysql/debian.cnf don’t match on
node03. So we will not be able to shutdown mysqld on either of these nodes.
node02# service mysql stop [FAIL] Stopping MariaDB database server: mysqld failed!
So we’ve got to fix it, by copying /etc/mysql/debian.cnf from the first node (
node01) to all other nodes. So the data and configuration files have the same data again.
After that we are able to shutdown the daemon:
node02# service mysql stop [ ok ] Stopping MariaDB database server: mysqld.
So if we would have a proper init script the Howto would have been even shorter 😉
Follow the Bug 🙂
Enjoy your MariaDB Galera Cluster and have fun!— Erkan Yanar
Thx to teuto.net for providing me an OpenStack tenant, so I could run the tests for this Howto.