I have a FreeBSD 10.0 server which currently only runs Percona MySQL server 5.6 backed by ZFS. The SQL server doesn’t have a high enough load to justify dedicated hardware, but I also don’t want to run it as a virtual machine as I want to use local ZFS storage, and because of virtualization overhead. The server is dual-homed (DMZ and LAN).
The solution is to convert the server into a jail host, and run MySQL inside a jail. The overhead should be minimal to non-existing as I won’t be using VNET.
Current Configuration
I’m using a ZFS layout as follows for the MySQL data:
sys/mysql (...) none sys/mysql/data (...) /var/db/mysql (... one dataset for each database ...) sys/mysql/log (...) /var/db/mysql_log
Relevant directives from my.cnf:
datadir = /var/db/mysql/ innodb-data-home-dir=/var/db/mysql_log/ innodb-log-group-home_dir=/var/db/mysql_log/
Preparations
Warning: Although this is designed to be a non-destructive procedure, please ensure you have a fresh backup, and that it works.
Since my server is dual-homed (LAN and DMZ), the first step is to enable and configure multiple routing tables, or FIBs, according to FreeBSD jail host with multiple local networks. If yours isn’t, you may skip this step.
The second step is to set up the system as a jail host. You may do so any way you prefer, but I’ll assume you have followed the steps described in FreeBSD jail server with ZFS clone and jail.conf.
The tird step is to add a new administrative user to MySQL which will be used instead of the @localhost (or equivalent) administrative user, once the jail is up and running.
Creating the MySQL jail
zfs clone sys/jail/.base10x64@p0 sys/jail/mysql
# file: /etc/jail.conf
## At the bottom of the file.
mysql {
# Replace with your interface
interface = "V_DMZ";
# Only needed if you use multiple routing tables,
# as described in step 1.
exec.fib = 1;
# Replace with a proper IP address
ip4.addr = 192.0.2.1;
}
I then start the jail and install the percona package:
# On the host # If you don't use multiple routing tables, # remove 'setfib 1' in the following statements. service jail start mysql setfib 1 jexec mysql pkg install percona56-server sysrc -j mysql mysql_enable="YES" sysrc -j mysql mysql_limits="YES" # Copy the MySQL configuration file to the jail cp /usr/local/etc/my.cnf /usr/jail/mysql/usr/local/etc/
It’s now time to stop the MySQL server which runs on the host, and move the data into the jail. I want the host to control the datasets, so I alter their mountpoints. If you want the jail to control the datasets, see further down.
zfs set mountpoint=/usr/jail/mysql/var/db/mysql sys/mysql/data zfs set mountpoint=/usr/jail/mysql/var/db/mysql_log sys/mysql/log
Once that’s done, it’s time to start MySQL inside the jail:
# Skip 'setfib 1' if you're not using multiple routing tables # as described in step 1. setfib 1 jexec mysql service mysql-server start
You should now have a jailed MySQL server, running a-ok. At least mine did!
PS: You may also want to restart the jail to verify that everything starts up as it should. It’s better to find such issues now, than upon next reboot of the host. :)
Jail-Controlled ZFS Datasets
Note: Although the following instructions should work, I have not actually tested them for this scenario.
If you’d rather let the jail control the datasets, you don’t have to change the mountpoints of the datasets. You do however have to set the property ‘jailed’ to ‘on’:
zfs set jailed=on sys/mysql
And you’ll have to let the jail manage the ZFS dataset:
# file: /etc/jail.conf
mysql {
interface = "V_DMZ";
exec.fib = 1;
ip4.addr = 192.0.2.1;
allow.mount;
allow.mount.zfs;
enforce_statfs = 1;
exec.start = "zfs jail $name sys/mysql";
exec.start += "/bin/sh /etc/rc";
exec.stop += "zfs unjail $name sys/mysql";
}
Savagedlight's Road 