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.
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).
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
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 .
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
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.
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.