I’ve been working on a new project at work getting to setup a new environment from scratch. It’s amazing.
For our database, I decided to use MariaDB with Galera. I haven’t managed traditional Master/Master replication, but I’ve always heard that it’s incredibly brittle. I didn’t want to have to worry about resyncing boxes should one of the boxes go down. That led me to Galera and that gave me the excuse I had been looking for to check out MariaDB.
Everything is going on top of Debian Wheezy, which has presented some interesting challenges. MariaDB works fine, but the Galera package in the MariaDB repos is compiled against libssl-0.9.8 and Wheezy comes with libssl-1.0.0. That meant I had to rebuild Galera against the newer version of libssl. Their sources have tooling to build a deb, but I had to do some jiggery (that’s a technical term) to get it to work and I’ve slept a few times since I did it, so I’ll just give you this link to the package I built. In the environment, I just put the package in a local apt repo that I have pinned to a higher priority than the MariaDB repo.
So, let’s skip installing it. You can get started with Monty’s help. Now, let me fill in some gaps.
Here’s my config:
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_address = gcomm://mysql-n01,mysql-n02
wsrep_cluster_name = galera
wsrep_node_address = 172.16.1.10
wsrep_slave_threads = 16
wsrep_sst_auth = galera-sst:password
wsrep_sst_method = xtrabackup
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
innodb_locks_unsafe_for_binlog = 1
innodb_flush_log_at_trx_commit = 2
I put this config in /etc/mysql/conf.d/galera.cnf. Debian will include the configs in /etc/mysql/conf.d/, so it just makes sense to keep all of the Galera configs together in one place.
Monty and the Codership wiki both have good docs on the minimum, mandatory settings I have in there. I won’t cover all of them since they’re treated fairly well in the linked documentation, but I’ll clarify some of the ones I had some confusion over.
For the first node, you’ll need to set wsrep_cluster_address to “gcomm://” to initialize the cluster on the first node. Once the cluster is established on the first node, you can set it to a comma separated list of all of the nodes in the cluster. This will make it so when a node comes back up, it knows who it can talk to in order to get synced back up.
This is the address for which you want the node to communicate to other cluster members. I’m using a private network that is on eth2, so to ensure everything stays on the private network, I set this to the address on eth2 on each of the cluster hosts.
This one got me hard because it’s easy to overlook and Monty doesn’t even mention it. If you’re using the mysqldump, the default for Galera under MariaDB, or xtrabackup state transfer method, you’ll need to supply this. Note that the user must exist on the DONOR and it will be ‘sst-user’@’localhost’.
I was seeing errors like the following when I had my grant for ‘galera-sst’@’172.16.1.%’:
WSREP: 1 (mysql-n01): State transfer to 0 (mysql-n02) failed: -1 (Operation not permitted)
Changing it to ‘galera-sst’@’localhost’ fixed the problem.
The other problem I ran into with using xtrabackup as the state transfer method had to do with the default version of netcat distributed with Wheezy. I was getting the following error:
Aug 22 20:54:56 mysql-n02 mysqld: nc: invalid option -- 'd'
Apparently, the xtrabackup SST opens port 4444 on the receiver and the donor connects to it to stream the tarball produced by xtrabackup. Since netcat didn’t behave the way the SST script expected, the port wasn’t getting opened and the node couldn’t complete the state transfer. The solution was to install netcat-openbsd, which behaves the way the xtrabackup SST script expects. I’m not sure if this is Wheezy specific or if it applies to Squeeze and/or the Ubuntu flavors.
I wanted to get all of this put together in one place because I spent the better part of this afternoon figuring out the last two points above, getting extremely frustrated with the sparse clues I was finding. I’ll be posting another article on the way I am using haproxy and garbd with the setup shortly and that should put all of the pieces together.