Installing a MariaDB server on FreeNAS

Learn how to install a MariaDB server on FreeNAS. Configure ZFS datasets to store the data and log files and optimize the throuput of your databases. You can configure the server to accept connections locally or from remote hosts over the network.

Creating ZFS datasets

You can store the database files in specific ZFS datasets on your FreeNAS server, which can provide some performance benefits. For example, you can create a dataset with a record size of 16 kilobytes, which matches the default page size used in MariaDB.

In this guide, you create datasets for the corresponding innodb_data_home_dir, innodb_log_group_home_dir, and datadir properties of MariaDB. To create the datasets, run the following commands in a FreeNAS shell:

Create the tank/innodb_data dataset:

zfs create tank/innodb_data
zfs set atime=off tank/innodb_data
zfs set compression=off tank/innodb_data
zfs set primarycache=metadata tank/innodb_data
zfs set recordsize=16K tank/innodb_data

Create the tank/innodb_log dataset:

zfs create tank/innodb_log
zfs set atime=off tank/innodb_log
zfs set compression=off tank/innodb_log
zfs set primarycache=metadata tank/innodb_log

Create the tank/datadir dataset:

zfs create tank/datadir
zfs set atime=off tank/datadir
zfs set compression=off tank/datadir
zfs set primarycache=metadata tank/datadir
zfs set recordsize=16K tank/datadir

Note that these are the settings that we recommend for good balance of performance improvement and minimize the risk of data corruption. You should evaluate the right settings for your workloads. To further improve performance, consider moving the ZFS Intent Log (ZIL) to a fast device, such as a low-latency SSD.

Preparing the jail

The instructions in this post host the app server in a jail on FreeBSD. To learn why we use jails for this purpose, check the Application server section of our self-hosted architecture post.

In this section, you’ll perform the following tasks:

Create a jail.
Configure networking on the jail.
Install the prerequisite packages.

Run the commands from a session in your FreeBSD host.

To create a jail:

Fetch or update the release version of FreeBSD for jail usage:
```
iocage fetch --release 11.3-RELEASE
```

Create a jail named mariadb:

iocage create --name mariadb --release 11.3-RELEASE

To configure networking on the jail:

Configure the IP address. The following example sets the IP address to 192.168.1.123 using a subnet mask of 24 bits on the em0 interface. The command uses the CIDR notation.
```
iocage set ip4_addr="em0|192.168.1.123/24" mariadb
```
Configure the default router. The following example sets the default router to 192.168.1.1:
```
iocage set defaultrouter=192.168.1.1 mariadb
```

Start the jail and open a session to complete the rest of the tasks in this section:

iocage start mariadb
iocage console mariadb

Install the mariadb104-server package:

pkg update
pkg install --yes mariadb104-server

Create folders in the jail where you are going to mount the datasets. Assign the mysql user as the owner:

mkdir -p /var/db/mysql/innodb_data
mkdir -p /var/db/mysql/innodb_log
mkdir -p /var/db/mysql/datadir

chown -R mysql:mysql /var/db/mysql/innodb_data
chown -R mysql:mysql /var/db/mysql/innodb_log
chown -R mysql:mysql /var/db/mysql/datadir

Close the session in the jail so you can mount the datasets from your FreeNAS session:

exit

Mount the datasets on the jail:

Use the following command to stop the jail:
```
iocage stop mariadb
```

Mount the data and log datasets on the corresponding folders in the jail:

iocage fstab mariadb --add /tank/innodb_data /var/db/mysql/innodb_data nullfs rw 0 0
iocage fstab mariadb --add /tank/innodb_log  /var/db/mysql/innodb_log  nullfs rw 0 0
iocage fstab mariadb --add /tank/datadir     /var/db/mysql/datadir     nullfs rw 0 0

Restart the jail:
```
iocage start mariadb
```

Configure the service

Open a session on the jail:

iocage console mariadb

Create the /var/db/mysql/my.cnf file, which should at least declare the following options:

[mysqld]
# Uncomment the following line to enable access from remote hosts.
# bind-address    = 0.0.0.0
innodb_data_home_dir      = /var/db/mysql/innodb_data
innodb_log_group_home_dir = /var/db/mysql/innodb_log
datadir                   = /var/db/mysql/datadir
skip-innodb_doublewrite

Uncomment the bind-address option to enable access from other hosts in the network. Otherwise, connections are only accepted from the jail. If you decide to accept connections from other hosts, you should configure access over TLS.

Configure the service startup and start the service:

sysrc mysql_enable=yes
service mysql-server start

Run the script to improve the security of the installation:

mysql_secure_installation

Testing the installation

To test the installation, open a connection using the following command from within the jail:

mysql --user=root --password

After entering the password of the root user, you should see a message similar to the following:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.4.10-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

From the MariaDB prompt, you can list the existing databases:

show databases;

Configuring access over TLS

To configure access over TLS, you need an SSL certificate, such as the ones provided by Let’s Encrypt. Copy the crt and key files of your certificate to a folder in the jail.

Then, configure MariaDB to use the certificate by adding the following entries to the [mysqld] section of the /var/db/mysql/my.cnf file:

[mysqld]
···
ssl_cert        = /path/to/certificate.crt
ssl_key         = /path/to/certificate.key
tls_version     = TLSv1.2,TLSv1.3
···

MariaDB provides support for TLS version 1.1 by default. However, it’s recommended to use TLS version 1.2 and above according to the PCI Security Standards Council. The tls_version option specified in the previous example removes support for TLS version 1.1.

Move the ZIL to a low-latency device

For better write performance, consider moving the ZIL to a low-latency device, such as an NVMe drive. If you have a pair of devices, you can use the following command to add the devices to the tank pool as a mirrored log devices:

zpool add tank log mirror nvd0 nvd1

Where nvd0 and nvd1 are the low-latency devices.

If you only have one drive, you can add it as a log device with the following command:

zpool add tank log nvd0

To confirm that the pool is using the devices, run zpool status tank and check that the devices are listed in the logs section, as shown in the following example:

$ zpool status tank
  pool: tank
 state: ONLINE
  scan: scrub repaired 0 in 0 days ...
config:

        NAME        STATE     READ WRITE CKSUM
        tank        ONLINE       0     0     0
          mirror-0  ONLINE       0     0     0
            ada0    ONLINE       0     0     0
            ada1    ONLINE       0     0     0
        logs
          mirror-1  ONLINE       0     0     0
            nvd0    ONLINE       0     0     0
            nvd1    ONLINE       0     0     0

errors: No known data errors