SysAdmin Notes: Enabling SSL Cluster replication – Let’s Encrypt – FreeBSD, MariaDB 10.2, and Galera Cluster

From, you’ll get :


FreeBSD layout – copy these to /etc/ssl (well, where you want).

concatenate the ca.cer with symlink of cert.pem from the package “security/ca_root_nss”.

sudo sh -c 'cat ca.cer cert.pem > cert2.pem'

Galera Cluster doesn’t like chained certificates, and does both a client and server Certificate verification – so wsrep_provider_options look like this :


Now, because you have to restart the whole cluster … Let’s Encrypt’s certificates every 60-90 days … may not be optimal. And I’m unclear what the impact is for doing “–wsrep-new-cluster” all the time … because once you change the certificates, you apparently can’t re-join the same cluster.

HOWEVER – the self-signed certificates, as directed here : – didn’t work out of the box. For whatever reason, the server cert needs to be the full chain. To create that … one more step:

sudo sh -c 'cat server-cert.pem ca-cert.pem > serverchain.pem '

And use socket.ssl_cert=serverchain.pem

Oh yeah … you need different self signed certs on each Galera node. The same server cert doesn’t work for all nodes.

Well, that is only partly true …and only worked for 1/2 the connection … openssl s_client worked, but mysql/Galera would not validate the cert. In fact, you can have the same cert on everything – if you DON’T set the ssl_ca. Yeah, really. Seemly, you don’t even need to sign it … just create the certificate/key pair for the CA, and use those as key/cert, and don’t set the CA.

SysAdmin Notes: FreeBSD, MariaDB 10.2, and Galera Cluster


Installing and getting MariaDB 10.2, and Galera Cluster going on FreeBSD (from ports/packages) was not straight forward. The getting started were all geared for linux packages which seem to have different helpers. I couldn’t find any how to’s for freebsd.

These are more notes, not a how-to. Please, someone write up a better how-to or add in helper scripts to ports.

Packages / Ports

As things do and always change from version to version, custom poudriere builds, however all default options.


pkg install mariadb102-server mariadb102-client galera

db1/db2 – bhyve’s

  • 16G RAM
  • 8 vCPUs/cores
  • 20G zroot
  • 500G spare-zvol, no compression, db volume
  • 11.1-stable – hypervisor and VMs
Setup ZPOOL “db”

zpool create -o autoexpand=on db /dev/vtbd1
zfs create db/mysql
zfs create db/mysql/innodb
zfs create db/mysql/logs
zfs set primarycache=metadata db/mysql
zfs set atime=off db/mysql
zfs set recordsize=16k db/mysql
zfs set recordsize=16k db/mysql/innodb
zfs set recordsize=128k db/mysql/logs
zfs set zfs:zfs_nocacheflush=1 db/mysql
zfs set sync=disabled db/mysql
zfs set compression=gzip db/mysql
chown -R mysql:mysql /db

Actual Setup / Config

See Appendix A for the my.cnf … this was a merge and hack up of /usr/local/share/mysql/my-innodb-heavy-4G.cnf and /usr/local/share/mysql/wsrep.cnf included with the mariadb102 package.

Skim/read the official getting started first :

A few observed notes that drove me crazy trying to get this to work.

– galera cluster does not seem to be launch-able via “mysqld_safe” which is in /usr/local/etc/rc.d/mysql-server.
– Seriously, don’t stop the first instance created with ” –wsrep-new-cluster”
– These freebsd builds won’t obey wsrep_on and wsrep_cluster_address from the my.cnf. I don’t know why.
when finally running, /db/mysql/db2.err isn’t getting populated.
–log-error=${hostname}.err in the mysql_args reproduced the functionality in mysqld_safe wrapper script.

DB1 – first master

Breaking the FreeBSD-ism, DO NOT use /usr/local/etc/rc.d/mysql-server, so …

First, with wsrep_on and wsrep_cluster_address commented out, initialize the db:

/usr/local/bin/mysql_install_db --basedir=/usr/local

Now, start the first Cluster Master ( I can’t remember now, but when starting new, you might have to set the cluster address to itself ). MUST run as MySQL.

sudo -u mysql /usr/local/libexec/mysqld --wsrep-on --wsrep-new-cluster --wsrep_cluster_address=gcomm://db1

If galera/WSREP loads correctly … you will see notes about loading the library. If you don’t see that, it didn’t work.

> sudo -u mysql /usr/local/libexec/mysqld --wsrep-new-cluster --wsrep_cluster_address=gcomm://db1
2017-11-06 21:20:01 34424840192 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2017-11-06 21:20:01 34424840192 [Note] /usr/local/libexec/mysqld (mysqld 10.2.9-MariaDB-log) starting as process 6785 ...
2017-11-06 21:20:01 34424840192 [Note] WSREP: Read nil XID from storage engines, skipping position init
2017-11-06 21:20:01 34424840192 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/lib/'
2017-11-06 21:20:01 34424840192 [Note] WSREP: wsrep_load(): Galera 3.21(rrelease_25.3.21) by Codership Oy loaded successfully.
2017-11-06 21:20:01 34424840192 [Note] WSREP: CRC-32C: using hardware acceleration.

DO NOT STOP THIS MASTER ! It’s in some sort of staged setup … and doesn’t actually work until the second note is registered. Doesn’t have to be up, but needs to be configured.

DB2 – second master

Again with wsrep_on and wsrep_cluster_address commented out, initialize the db:

/usr/local/bin/mysql_install_db --basedir=/usr/local

join the cluster :
sudo -u mysql /usr/local/libexec/mysqld --wsrep-on --wsrep_cluster_address=gcomm://db1

Look again for “[Note] WSREP: wsrep_load(): Galera […] loaded successfully.”

DB2 – test

Log into the databases, with root, (no password at this point if you followed my directions). May want to run mysql_secure_installation – nice script I often forget about. I did, root credentials didn’t seem to replicate.

DB2 – Boot Configure

Control-C didn’t work, will have to ‘kill -TERM $pid’ of the child process to the sudo, will get a clean shutdown.

Add to rc.conf :

mysql_args="--wsrep-on --wsrep_cluster_address=gcomm://db1,db2 --log-error=${hostname}.err"

Edit /usr/local/etc/rc.d/mysql-server, change command_args from using /usr/local/bin/mysqld_safe to /usr/local/libexec/mysqld.

/usr/local/etc/rc.d/mysql-server start

Over on db1, you should still have mysqld running in the foreground (if not, you messed up) – and you’ll see db2 join.

Login into either:

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
| Variable_name | Value |
| wsrep_cluster_size | 2 |
1 row in set (0.00 sec)

DB1 – Boot Configure

‘kill -TERM $pid’ of the child process to the sudo, will get a clean shutdown.

Add to rc.conf:

mysql_args="--wsrep-on --wsrep_cluster_address=gcomm://db1,db2 --log-error=${hostname}.err"

Edit /usr/local/etc/rc.d/mysql-server, change command_args from using /usr/local/bin/mysqld_safe to /usr/local/libexec/mysqld.

/usr/local/etc/rc.d/mysql-server start


Outstanding issues

– Why does “mysqld_safe” not work.
– Why can’t one init a single node cluster ? makes sense, but for automatic provisioning, a self running cluster of one would be nice.
– wsrep_on, wsrep_cluster_address – why are these not in being respected from the my.cnf ?
without mysqld_safe, db2.err isn’t getting populated.
–log-error=${hostname}.err in the mysql_args reproduced the functionality in mysqld_safe wrapper script.

Appendix A: my.cnf

# The MariaDB server
port = 3306
socket = /tmp/mysql.sock

# back_log is the number of connections the operating system can keep in
# the listen queue, before the MariaDB connection manager thread has
back_log = 200
max_connections = 300
max_connect_errors = 1000
table_open_cache = 2048
max_allowed_packet = 32M
binlog_cache_size = 8M
max_heap_table_size = 64M
read_buffer_size = 16M
read_rnd_buffer_size = 64M
sort_buffer_size = 128M
join_buffer_size = 128M
thread_cache_size = 8
thread_concurrency = 24
query_cache_type = OFF
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 4
thread_stack = 240K

transaction_isolation = REPEATABLE-READ
tmp_table_size = 256M



long_query_time = 2
tmpdir = /db/tmp

# *** Replication related settings
server-id = 1
#server-id = 2
#master-host =
#master-user =
#master-password = #master-port = #read_only

#*** MyISAM Specific options
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

# *** INNODB Specific options ***

# and speed up some things.

# innodb_additional_mem_pool_size = 32M # unknown option
innodb_buffer_pool_size = 10G
innodb_data_file_path = ibdata1:128M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
# zfs tunning
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 2560M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

# Options that need to be customized:
# - wsrep_provider
# - wsrep_cluster_address
# - wsrep_sst_auth
# The rest of defaults should work out of the box.

## WSREP options

# Full path to wsrep provider library or 'none'
# Logical cluster name. Should be the same for all nodes.

# retry autoinc insert, which failed for duplicate key error

# State Snapshot Transfer method
# wsrep_protocol_version=