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
datadir properties of MariaDB. To create the
datasets, run the following commands in a
- Create the
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
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
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
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.123using a subnet mask of
24bits on the
em0interface. 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
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:
Mount the datasets on the jail:
- Use the following command to stop the jail:
iocage stop mariadb
- Mount the
logdatasets 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
/var/db/mysql/my.cnf file, which should at least declare the
[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
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
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:
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:
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
files of your certificate to a folder in the jail.
Then, configure MariaDB to use the certificate by adding the following entries
[mysqld] section of the
[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
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
$ 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