Installing MYSQL in a FreeNAS Jail

I’ve received a couple of emails asking me if I’d also had experience of installing MySQL in a FreeNAS jail.  I have installed MariaDB, a drop in replacement for MySQL written by some the same developers who gave us MySQL.

Reading through the documentation that comes with MariaDB, I can’t see any reason why the process here would be any different for MySQL, but if you do follow these instructions substituting MySQL for MariaDB, you will need to make sure you understand your own system before starting.

A comparison of the features of MariaDB and MySQL can be found at mariadb.com

Having said that, the process for placing MariaDB on a FreeNAS system is very straightforward.

You will see that even though I’ve installed MariaDB, the user, directory and file names are all MySQL (or a variation on the “my” name).  MariaDB is designed as a drop-in replacement for MySQL, so it looks as though all  MySQL experience can be directly transferred.

The installation is very straightforward.  No special settings were required for my jail to work and for the database to be available on the network. Most of the heavy work is in the actual configuration, testing and securing the MySQL server itself, not in the installation of the jail.

A quick word about my configuration

I’ve described my system previously, but I’ll just give you an outline of the configuration before we start.

The system is built on a standard tower case with an ASUS M3N78-VM motherboard with 8MB memory and a dual core AMD Athlon.

There are 5 disks installed.

• Set 1: three 1TB disks arranged in a ZFS RAID-5 unit
• Set 2: two 160GB drives mirrored

The reason for this is because originally the system was only performing file storage functions. The three 1TB disks were installed and these are the data partition.  After the jails functionality was added I put the extra drives in (after checking the capacity of the PSU of course) to hold the jails.  I didn’t want to risk messing up anything on the data partition, so by adding these extra drives I could keep my data safe whilst exploring the jails functionality.

I now install the jails on the mirrored pair and hold data on the main RAID-5 array.  The data directories are then mounted into the jail as required.

Space Required

The jail holding the MariaDB server and it’s dependencies is under 2GB, excluding the data directories.

Create the jail and install MariaDB

Before you start

There are a few things to decide and note down before you start to install MySQL/MariaDB.

• The jail name: something easy to remember
• IP Address. Reserve an address for the server. If you are running a DHCP server (often in a router, or maybe using dnsmasq), make sure you pick an address that is outside the range of DHCP addresses.
• Router/Gateway: IP address of this
• Subnet Mask:
• Disk Space: Do you have enough? This isn’t always a silly question
• Data Directory Location. Will the data reside inside the jail, or will it be placed outside the jail and the storage added into the jail during installation.
• Shell Access. Do you have shell access to the FreeNAS server

Create the Jail

In the FreeNAS GUI, create the jail and give it a free IP address on your network (the one you noted down before).

Jail Settings

Create the jail with the following settings:

• Name: <your chosen name >
• IP address <your IP address>
• default gateway <your gateway/router address>
• Type: Standard jail
• Vanilla Option un-ticked
• Vimage ticked

You will probably also want to tick autostart as well, so that your database server will be available whenever the FreeNAS is restarted. My FreeNAS is only rebooted when there is a firmware update, so this doesn’t happen very often

The installation will take a few minutes depending on your system speed/load.

Install Maria DB

Log in to the jail. You can use the command console from the GUI.  I prefer to SSH into the FreeNAS server and then use jexec to get into the jail.  I don’t run the SSH demon on the different jails.

freenas# jls
JID  IP Address      Hostname                      Path
    3  -               database_m                    /mnt/volume1/jails/database_m

freenas# jexec 3 /bin/csh

If you’ve created the jail correctly, you should now be in it.

The commands you need to enter are in bold face.

root@database_m:/ # pkg search mariadb
mariadb-client-5.3.12
mariadb-client-5.5.31
mariadb-scripts-5.3.12
mariadb-server-5.3.12
mariadb-server-5.5.31

You can see that there are several different versions of MariaDB packaged. I answered n so I could explicitly choose the latest version. Unless you have a dependency on a particular version (some systems are only verified against specific releases of their back-end servers), I’d recommend using the latest version.  If you planning on using this as the database server for another application, check if there are any version constraints before proceeding. (The versions you see may be different from these as packages get updated.)

Repeating the command just for the server shows:

root@database_m:/ # pkg install mariadb-server
Updating repository catalogue
The following 5 packages will be installed:
        Installing libevent: 1.4.14b_2
        Installing mariadb-client: 5.3.12
        Installing mariadb-client: 5.5.31
        Installing mariadb-server: 5.3.12
        Installing mariadb-server: 5.5.31
The installation will require 224 MB more space
23 MB to be downloaded
Proceed with installing packages [y/N]: n

As I want the latest version, this requires:

root@database_m:/ # pkg install mariadb-server-5.5.31
Updating repository catalogue
The following 2 packages will be installed:
        Installing mariadb-client: 5.5.31
        Installing mariadb-server: 5.5.31
The installation will require 142 MB more space
12 MB to be downloaded
Proceed with installing packages [y/N]: y
mariadb-client-5.5.31.txz                                                                                                                                              100% 1554KB   1.5MB/s   1.5MB/s   00:01 
mariadb-server-5.5.31.txz                                                                                                                                              100%   11MB   5.4MB/s   4.9MB/s   00:02  
Checking integrity... done
[1/2] Installing mariadb-client-5.5.31... done
[2/2] Installing mariadb-server-5.5.31...===> Creating users and/or groups.
Creating group 'mysql' with gid '88'.
Creating user 'mysql' with uid '88'.
done
************************************************************************
Remember to run mysql_upgrade (with the optional --datadir= flag)
the first time you start the MySQL server after an upgrade from an
earlier version.
************************************************************************
root@database_m:/ #

This was a fresh installation for me,  so I didn’t need to run the upgrade process.

To make sure that the service will start when the jail starts, edit /etc/rc.conf and add the  line

mysql_enable="YES"

Changing the default data directory

The default data directory is /var/db/mysql. This can be altered by adding this line to /etc/rc.conf .

mysql_dbdir="/usr/local/mysqldata/"

I am using the /var/db/mysql location for my data.  If you change this, then use your location whenever you see /var/db/mysql.

Create the data directory

The next step depends on your configuration.  If you are planning on storing the database completely within the jail, then you can continue with the installation  (jump to creating the mysql user and group section).

If you are placing the data outside the jail itself, then you will need to create the data directory and mount it into the jail (using the add storage) option in the GUI.

You can set up a ZFS dataset dedicated to the database directory.  An advantage of doing this is if in the unlikely event you get a rogue application creating huge databases the server will fail when it reaches the quota you’ve set up. You’ll probably have to investigate what’s happened, but the rest of the file sharing duties will be unaffected.

Another advantage of this is that the data directory can remain hidden to the rest of the FreeNAS connected users, because unless you share the directory out, the only way to access it is via the database server, or via a shell login.

I created a dataset  on the raid-5 set called db with a 25GB quota.

From outside the jail I created a subdirectory within the dataset to hold the database.

freenas# mkdir /mnt/Data/db/mysql

Add the storage

Using the GUI , add the storage to the jail.   The source is the directory or dataset  you created in the main FreeNAS storage area.  The destination is /var/db/mysql or the location you specified before.

Create the mysql user and mysql group in FreeNAS

As part of the installation, a user and group with the name “mysql” and the UID and GID of 88 was created.  Add this group and user to the main FreeNAS user list.

The Disable password login and Lock user options can both be ticked. This means when you view the files from a console sessions, you will see the MySQL user as the owner, rather than just “88”. The home directory can be set to the directory you setup above ( /mnt/Data/db/mysql)

The install files are now sitting where MySQL expects them, and also are on the storage outside the jail.

Create the /var/run directory (optional)

The default server is socket is /tmp/mysql.sock.  I’ve placed mine in /var/run/mysql, so I need to create the directory for this.

I created this manually and set the permissions to the mysql user.  From within the jail issue these commands.

root@database_m:/ # mkdir /var/run/mysql
root@database_m:/ # chown mysql:mysql /var/run/mysql/

The location of this directory can be used  in the my.conf file in the lines that say “socket=”.

Run the MySQL server to create the data directory

The mysql_install_db script will be automatically run the first time. If you would prefer to run this manually, instructions are at Installing system tables (mysql_install_db) on the MariaDB site.

database_m# service mysql-server start 
database_m# service mysql-server status
mysql is running as pid 7574.
database_m# service mysql-server stop
Stopping mysql.
Waiting for PIDS: 7574

If you look at your data directory you will see a basic configuration has been created.

root@database_mt:/var/db # ls mysql
aria_log.00000001       aria_log_control        dbtest.err              ib_logfile0             ib_logfile1             ibdata1                 mysql                   performance_schema      test

Create the my.cnf file

The configuration file is located in the root of the data directory.  If you have kept to the default install, this will be /var/db/mysql.

In the /usr/local/share/mysql/ directory are a number of template configuration files.

root@database_m:/ # ls /usr/local/share/mysql/
binary-configure                dutch                           greek                           my-innodb-heavy-4G.cnf          mysql_system_tables.sql         polish                          swedish
charsets                        english                         hungarian                       my-large.cnf                    mysql_system_tables_data.sql    portuguese                      ukrainian
config.huge.ini                 errmsg-utf8.txt                 italian                         my-medium.cnf                   mysql_test_data_timezone.sql    romanian
config.medium.ini               estonian                        japanese                        my-small.cnf                    mysqld_multi.server             russian
config.small.ini                fill_help_tables.sql            korean                          mysql-log-rotate                ndb-config-2-node.ini           serbian
czech                           french                          magic                           mysql.server                    norwegian                       slovak
danish                          german                          my-huge.cnf                     mysql_performance_tables.sql    norwegian-ny                    spanish

Copy the my-small.cnf to the data directory.  (If you have set a different data directory, substitute you new location for /var/db/mysql.)

root@database_m:/ #  cp /usr/local/share/mysql/my-small.cnf /var/db/mysql/my.cnf

If you have created the /var/run/mysql directory, edit the file and change the socket= line in the [client] and [server] sections to

socket          = /var/run/mysql/mysql.sock

The default location /tmp/mysql.sock. You could leave it as this.

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

user=mysql
... <snip> ....
socket          = /var/run/mysql/mysql.sock

[client]
... <snip> ....
socket          = /var/run/mysql/mysql.sock

I’ve left all the other settings as their default.  If you are transferring the database from another server, or are using it to support another application, look at the existing settings and transfer these across or compare these defaults against the recommended settings for your application.

Start the service

If everything has gone smoothly, you should be able to start the system now. Issue the command to start the server

database_m# service mysql-server start 
database_m# service mysql-server status
mysql is running as pid 7606.

Configuring MySQL/MariaDB

The rest of the installation is not specific to FreeNAS, and I’d recommend that you follow instructions on the MariaDB or MySQL sites

On the MySQL website, the  2.11. Postinstallation Setup and Testing section of the manual will walk you through the process of testing the installation and securing the tables.

If you have trouble connecting to the database, you could try explicitly referencing the my.cnf file created earlier.

database_m# mysql --defaults-extra-file=/var/db/mysql/my.cnf -u root

Once you’ve installed the system I highly recommend using the phpMyAdmin tool (phpmyadmin site to manage your database.