Vince Rants

MariaDB Galera Cluster on FreeBSD

Important Note: This article is written for FreeNAS 9.10, although most of it will apply to FreeBSD in general. FreeNAS 10 is right around the corner, which no longer supports the jails system in their new web based UI. The FreeNAS team has come out and said jails will still exist in the new FreeNAS release, however there will be no web based UI by default. When FreeNAS 10 is released, this article will be updated with instructions on how to create the initial jail from command line on it.

Introduction

Recently Codership released precompiled binaries for Galera Cluster to run on FreeBSD. This greatly simplifies MySQL and MariaDB deployments on FreeBSD. Starting with MariaDB 10.1, the Galera Multi-Master Clustering support is now available by default rather than being a separate custom build. This means it is now possible to deploy a MariaDB Galera Cluster on FreeBSD without compiling any software whatsoever.

The entire deploy process is quite simple and straight forward at this point. This document assumes you are comfortable with a FreeBSD command line. This document also assumes that a Galera cluster is already up and running. In my case, I'm connecting to an existing MariaDB 10.1 Galera Cluster running on TurnKey Linux. The only difference between starting a new cluster and joining an existing cluster is the bootstrapping process (starting the MariaDB service), otherwise the rest of this article still applies to both situations.

 

Enable Jails Support

If you have not done so already, set up Jails support within your FreeNAS installation. Go to the [Jails] button in the main toolbar, and select the [Configuration] tab. You'll need to specify a root path for Jails to be stored in.

FreeNAS Jails Configuration

Creating The Jail

Return to the [Jails] tab, and click on the [Add Jail] button.

FreeNAS Jails List

Configuring The Jail

You'll need to specify a [Jail Name] and [IPv4 Address] for the jail.

Warning: By default, FreeNAS will NOT check your existing network for overlapping IP Addresses, so the default address provided in this box may need to be updated to an address not already in use.

Adding a FreeNAS Jail

Optional Configuration

By default, FreeBSD will use the same MAC Address for the Jail as the host network adapter's MAC Address, adding an IP Address Alias to that MAC Address. This means multiple IP Addresses will be listed for a single MAC Address in the ARP table. On my network, I have management tools in place that needs each IP address to have a unique MAC Address. I have manually assigned a custom unique MAC Address to each jail.

Optional FreeNAS Jail Settings

Log into the Host and Jail

Access the host's console or SSH server. The jls command lists all of the jails on the system. Find the JID (Jail ID) in the first column next to the jail. Use this JID to start a shell session on the jail with jexec # csh.

[root@freenas] ~# jls JID IP Address Hostname Path 4 - galera_63 /mnt/datastore/jails/galera_63 [root@freenas] ~# jexec 4 csh

 

Upgrade Packages

Once in the jail, you'll need to upgrade all of the existing packages to their latest versions with pkg update && pkg upgrade -y

root@galera_63:/ # pkg update && pkg upgrade -y Updating FreeBSD repository catalogue... Repository FreeBSD has a wrong packagesite, need to re-create database [galera_63] Fetching meta.txz: 100% 944 B 0.9kB/s 00:01 [galera_63] Fetching packagesite.txz: 100% 6 MiB 1.5MB/s 00:04 Processing entries: 100% FreeBSD repository update completed. 26175 packages processed. Updating FreeBSD repository catalogue... FreeBSD repository is up-to-date. All repositories are up-to-date. New version of pkg detected; it needs to be installed first. The following 1 package(s) will be affected (of 0 checked): Installed packages to be UPGRADED: pkg: 1.6.4_1 -> 1.10.0_2 The process will require 551 KiB more space. 3 MiB to be downloaded. [galera_63] Fetching pkg-1.10.0_2.txz: 100% 3 MiB 2.6MB/s 00:01 Checking integrity... done (0 conflicting) [galera_63] [1/1] Upgrading pkg from 1.6.4_1 to 1.10.0_2... [galera_63] [1/1] Extracting pkg-1.10.0_2: 100% Updating FreeBSD repository catalogue... Repo "FreeBSD" upgrade schema 2012 to 2013: Add vital field Repo "FreeBSD" upgrade schema 2013 to 2014: DROP TABLE pkg_search; FreeBSD repository is up to date. All repositories are up to date. Checking for upgrades (25 candidates): 100% Processing candidates (25 candidates): 100% The following 27 package(s) will be affected (of 0 checked): New packages to be INSTALLED: pcre: 8.40 ... Installed packages to be UPGRADED: subversion: 1.9.3_3 -> 1.9.5 ... Installed packages to be REINSTALLED: p5-Mozilla-CA-20160104 (direct dependency changed: perl5) ... Number of packages to be installed: 2 Number of packages to be upgraded: 20 Number of packages to be reinstalled: 5

 

Install Database Packages and Dependencies

We'll want to install the mariadb101-server package. Additionally, Galera has a number of dependencies which currently are not listed within its own package. These dependencies include boost-libs, rsync, socat, bash, and lsof. This process may take a few minutes to complete.

root@galera_63:/ # pkg install -y mariadb101-server boost-libs rsync socat bash lsof Updating FreeBSD repository catalogue... FreeBSD repository is up to date. All repositories are up to date. The following 10 package(s) will be affected (of 0 checked): New packages to be INSTALLED: mariadb101-server: 10.1.21 boost-libs: 1.63.0_1 rsync: 3.1.2_6 socat: 1.7.3.2 bash: 4.4.12_1 lsof: 4.90.i,8 libxml2: 2.9.4 mariadb101-client: 10.1.21 icu: 58.2,1 libiconv: 1.14_10 Number of packages to be installed: 10

 

Install Galera Package

As of this writing, the Galera binary package is not in the main FreeBSD package list yet. Codership however, is hosting the binary package on the Galera Cluster web site. Note that the pack in the command below will most likely be different as new versions become available. You can check for the latest version at: http://releases.galeracluster.com/freebsd/

root@galera_63:/ # pkg add http://releases.galeracluster.com/freebsd/10.3/galera-25.3.20_1.txz [galera_63] galera-25.3.20_1.txz 100% of 638 kB 308 kBps 00m02s [galera_63] Installing galera-25.3.20_1... [galera_63] Extracting galera-25.3.20_1: 100%

 

Enable MariaDB Service

By default, the service for MySQL or MariaDB is not enabled. Use this simple command to enable the service. Note that this does not actually start the service, it only enables it to be started later.

root@galera_63:/ # echo 'mysql_enable="YES"' >> /etc/rc.conf

 

Create the my.cnf Configuration File

Unlike Debian and other Linux distributions, the MariaDB package on FreeBSD does not have a default my.cnf file installed. Even if it did, the configuration file would need to be updated to enable and customize the Galera Cluster settings. Grab your text editor of choice to create the file /usr/local/etc/my.cnf, I personally use nano because of my Debian familiarity. You can add this command to FreeBSD by install the package for it with pkg install -i nano.

Note: This is just a sample configuration my.cnf file. You'll need to update the wsret_provider_options, wsrep_cluster_name, and wsrep_cluster_address to match your existing Galera Cluster configuration. Additional MySQL options may need to be updated for your particular system configuration, specifcally innodb_buffer_pool_size.

root@galera_63:/ # nano /usr/local/etc/my.cnf

[client] port = 3306 socket = /var/db/mysql/mysqld.sock [galera] wsrep_on=ON wsrep_provider = /usr/local/lib/libgalera_smm.so wsrep_provider_options = "gmcast.segment=1;pc.weight=1" wsrep_cluster_name = "mycluster" wsrep_cluster_address = "gcomm://172.16.12.61,172.16.12.62" wsrep_sst_method = rsync [mysqldump] quick quote-names max_allowed_packet = 16M [mysqld_safe] datadir = /var/db/mysql socket = /var/db/mysql/mysqld.sock lc_messages_dir = /usr/local/share/mysql lc_messages = en_US nice = 0 [mysqld] port = 3306 bind-address = 0.0.0.0 user = mysql basedir = /usr tmpdir = /tmp datadir = /var/db/mysql pid-file = /var/db/mysql/mysqld.pid socket = /var/db/mysql/mysqld.sock log_bin = /var/db/mysql/mariadb-bin log_bin_index = /var/db/mysql/mariadb-bin.index lc_messages_dir = /usr/local/share/mysql lc_messages = en_US ##### FINE TUNING ##### max_connections = 5000 connect_timeout = 5 wait_timeout = 600 max_allowed_packet = 16M thread_cache_size = 128 sort_buffer_size = 4M bulk_insert_buffer_size = 16M tmp_table_size = 32M max_heap_table_size = 32M ##### INNODB ##### default_storage_engine = InnoDB innodb_buffer_pool_size = 2048M innodb_buffer_pool_size = 1536M innodb_log_buffer_size = 8M innodb_file_per_table = 1 innodb_open_files = 400 innodb_io_capacity = 400 innodb_flush_method = O_DIRECT innodb_autoinc_lock_mode = 2 ##### CACHING ##### query_cache_limit = 128K query_cache_size = 64M ##### LOGGING ##### binlog_format = ROW expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 ##### EXTRA CONFIG ##### skip-external-locking skip-name-resolve

 

Start the Service

All that is left to do is start the service. This may take some time before the service responds to requests while it clones the database from another node within the cluster. The length of time this takes depends on a number of factors, including network bandwidth between nodes and disk read/write speeds.

root@galera_63:/ # service mysql-server start