I don’t write blog posts anymore. I write notes. 🙂

Preface

Prebuilt packages of mainline MariaDB 10.1 are only available for x86 and x86_64 architectures. For other architectures, you need to build it from source.

https://downloads.mariadb.org/

Main repository contents, find the override file for your specific distro: http://ports.ubuntu.com/indices/

To check your distro, do this:

MariaDB 10.0 was added to the universe repository in Vivid (Ubuntu 15.04). It lacks Galera patches. No backport is available.

A broken Galera 3 was added to the universe repository in Wily (Ubuntu 15.10). FWIW there is also a Percona Server Galera-3 install, but this is specifically for Percona. There is however a working copy of Galera-3 in the Ubuntu 16.04 pre-release folders.

Building MariaDB and Galera Debian packages from source

Reference: http://askubuntu.com/a/28373/364657

If successful, the packages *can* be installed with this…

Unfortunately, that doesn’t help us when it comes to dependencies. It can be done with many “dpkg -i ” and “apt-get install -f” calls, but it’s ugly.

Instead, you should move the files to a folder, and set-up a Debian Packages repository.

A simple is just a folder with a Packages list. Here’s how we generate that list:

Now, add the repository to your sources.list:

Finally, do an update, and you can install the package as expected.

Reference: Building Galera: http://galeracluster.com/documentation-webpages/installmariadbsrc.html

Reference: Debian Repositories: http://askubuntu.com/a/532/364657

https://www.debian.org/doc/manuals/repository-howto/repository-howto#using-a-repository

https://www.debian.org/doc/manuals/repository-howto/repository-howto#id3032359

Configuring MariaDB

On Ubuntu, MariaDB settings go in /etc/mysql/my.cnf

By default, MariaDB only listens on Localhost (127.0.0.1). To make it listen on all interfaces, comment-out the bind-address line. To explicitly listen to all, make it “0.0.0.0“.

This is insecure, but it means the Firewall is now responsible for blocking unwanted traffic.

Reference: how-to-configure-mysql-and-mariadb-to-accept-remote-connections.html

Configuring Galera Cluster

Also inside /etc/mysql/my.cnf:

Starting the Primary Server in a Galera Cluster

Start the primary server like so:

You can permanently set it to restart by editing “/etc/init.d/mysql”.

Change the call to mysql_safe as follows:

Source: http://stackoverflow.com/a/25827488

The node that calls –wsrep-new-cluster is the master. The clients are started normally without it.

The clients will fail if there’s no server. Even the main server, if you don’t run –wsrep-new-cluster, it’ll think it’s a client.

The list of servers a client will attempt to connect to is the wsrep_cluster_address line in your config.

Allowing Clients of your Galera Server

You need to unblock some ports on the primary server’s firewall.

  • 3306 – MySQL port, TCP. Used for mysqldump SST (State Snapshot Transfer, i.e. Init via mysqldump)
  • 4567 – TCP & UDP. Used for Galera Cluster replication traffic (??)
  • 4568 – TCP. Used for IST (Incremental State Transfer)
  • 4444 – TCP. Used for SST (State Snapshot Transfer, everything but mysqldump)

SST is how a client node is fully initialized. IST is how clients get their incremental updates.

SST methods have varying degrees of blocking. RSYNC (default) blocks writes, MySQLDump blocks everything (ugh), XtraBackup is non-blocking (see below).

Port Reference: http://galeracluster.com/documentation-webpages/firewallsettings.html

SST Reference: http://galeracluster.com/2015/07/node-can-not-join-the-cluster-how-to-debug-issues-with-sst/

Starting Clients

Once you’ve correctly configured the clients, you can simply start mysql. No changes are required. It should just work.

All tables will by synchronized with the original.

Most importantly, that includes the user table. Login credentials will be the same no matter which node you connect to.

This can be a problem for Debian and Ubuntu installs, thanks to a worker user named debian-sys-maint. This is used by ‘service mysql’ (start, stop, etc). Its password is stored in /etc/mysql/debian.cnf

You’ll want to look at this file on the Primary server, and copy the passwords to all the clients. That seems to be the easiest way to handle this.

Reference: https://blog.mariadb.org/installing-mariadb-galera-cluster-on-debian-ubuntu/

Viewing Galera Cluster Status

TODO: http://galeracluster.com/documentation-webpages/monitoringthecluster.html

Reference: https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluster/

Further Galera Configuration

TODO

http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html#wsrep-sst-method

https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_sst_receive_address

Listing Users

Reference: http://dev.mysql.com/doc/refman/5.7/en/show-grants.html

Creating Users

Prefer CREATE USER. Inserting directly in to the mysql.user table will not replicate correctly across a Galera cluster.

Reference: http://galeracluster.com/documentation-webpages/userchanges.html

Resetting (and setting) MySQL Passwords

If you ever find yourself in a situation where you can’t log in, you can disable authentication and log in to the server locally.

NOTE: If this is a client node of a Galera cluster, your passwords were likely taken from the Primary node (both your users, and debian-sys-maint). See the Starting Clients section above.

Reference: https://www.debian-administration.org/article/442/Resetting_a_forgotten_MySQL_root_password

Building XtraBackup Debian packages from source

Reference: https://www.percona.com/doc/percona-xtrabackup/2.3/installation/apt_repo.html

Using XtraBackup

TODO

Reference: digitalocean.com/…/percona-xtrabackup-on-ubuntu-14-04

https://www.percona.com/doc/percona-xtrabackup/2.3/xtrabackup_bin/xtrabackup_binary.html

https://www.percona.com/doc/percona-xtrabackup/2.3/innobackupex/privileges.html

Configuration Optimization

Reference: https://www.percona.com/blog/2014/11/17/typical-misconceptions-on-galera-for-mysql/

I did some benchmarks. My read speed for small queries was around 4ms, and write speeds were around 80ms. That seemed a bit much.

Making the following changes improved it.

You can achieve better performance by setting [innodb_flush_log_at_trx_commit] different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions.

Reference: http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

With these changes, my write speed dropped from ~80ms to ~5ms.