SysAdmin Notes: FreeBSD, MariaDB 10.2, and Galera Cluster

by David P. Discher
Posted on November 6, 2017November 6, 2017

Intro

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.
galera-25.3.21_2 mariadb102-client-10.2.9_1 mariadb102-server-10.2.9_1

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 : https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/

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/libgalera.so' 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_enable="YES" mysql_args="--wsrep-on --wsrep_cluster_address=gcomm://db1,db2 --log-error=${hostname}.err" mysql_dbdir="/db/mysql"

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

run:
/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.

DB1 – Boot Configure

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

Add to rc.conf:
mysql_enable="YES" mysql_args="--wsrep-on --wsrep_cluster_address=gcomm://db1,db2 --log-error=${hostname}.err" mysql_dbdir="/db/mysql"

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

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

Done.

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 [mysqld] bind-address=0.0.0.0 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

default-storage-engine=innodb

thread_stack = 240K

innodb_autoinc_lock_mode=2
transaction_isolation = REPEATABLE-READ

tmp_table_size = 256M
log-bin=mysql-bin

binlog_format=ROW
#log_slave_updates
slow_query_log

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

myisam_recover
# *** INNODB Specific options ***
# and speed up some things.

#skip-innodb
datadir=/db/mysql

innodb_data_home_dir=/db/mysql/innodb

innodb_log_group_home_dir=/db/mysql/logs

# 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_force_recovery=1

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_flush_method=O_DSYNC

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
#wsrep_on=on

# Full path to wsrep provider library or 'none'

wsrep_provider=/usr/local/lib/libgalera.so

#wsrep_provider_options=

# Logical cluster name. Should be the same for all nodes.

wsrep_cluster_name="ix_production"

#wsrep_cluster_address="gcomm://db1,db2"

wsrep_node_name=db2

wsrep_node_address=192.168.0.20[12]

#wsrep_node_incoming_address=
wsrep_slave_threads=1

#wsrep_dbug_option

wsrep_certify_nonPK=1

wsrep_max_ws_rows=131072

wsrep_max_ws_size=1073741824

wsrep_debug=0

wsrep_convert_LOCK_to_trx=0

wsrep_retry_autocommit=1

wsrep_auto_increment_control=1

# retry autoinc insert, which failed for duplicate key error

wsrep_drupal_282555_workaround=0

wsrep_causal_reads=0

wsrep_notify_cmd=
# State Snapshot Transfer method

wsrep_sst_method=rsync

#wsrep_sst_receive_address=

wsrep_sst_auth=root:

#wsrep_sst_donor=

#wsrep_sst_donor_rejects_queries=0

# wsrep_protocol_version=