Installing MySQL in a jail with several ZFS datasets of different recordsize

[ser22]

Hi all,

I would like to check with you whether the following steps are the best way to make available inside a jail, different ZFS datasets for a MySQL installation, keeping a recordsize=128K for the logs, and a recordsize=16K for the data for the MySQL and the InnoDB.

FreeBSD 13.0-RELEASE-p11 amd64
zfs-2.0.0-FreeBSD_gf11b09dec
mysql  Ver 8.0.28 for FreeBSD13.0 on amd64 (Source distribution)

NLTR;

[System] [MY-013169] [Server] /usr/local/libexec/mysqld (mysqld 8.0.28) initializing of server in progress as process 81757
2022-05-02T09:38:01.579530Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2022-05-02T09:38:01.579539Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/db/mysql/ is unusable. You can remove all files that the server added to it.
2022-05-02T09:38:01.581015Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-05-02T09:38:01.925439Z 0 [System] [MY-010910] [Server] /usr/local/libexec/mysqld: Shutdown complete (mysqld 8.0.28)  Source distribution.
/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql

I first created the ZFS datasets on the Host

zfs create -p \
    -o recordsize=128k -o atime=off -o compression=zstd \
    tank/mysqljail/db/

zfs create -p \
   -o mountpoint=/var/db/mysql tank/mysqljail/db/mysql

zfs create -p \
   -o mountpoint=/var/db/innodb/log tank/mysqljail/db/innodb/log

zfs create -p \
    -o recordsize=16k \
   -o mountpoint=/var/db/innodb/data tank/mysqljail/db/innodb/data

Then

zfs jail mysqljail tank/mysqljail/db/mysql
zfs jail mysqljail tank/mysqljail/db/innodb/data
zfs jail mysqljail tank/mysqljail/db/innodb/log

From inside the jail I could see the mountpoints

# zfs list
NAME                               USED  AVAIL     REFER  MOUNTPOINT
tank                              4.21G   188G     32.0K  /tank
tank/mysqljail                     276K   188G     30.6K  /tank/mysqljail
tank/mysqljail/db                  153K   188G     30.6K  /tank/mysqljail/db
tank/mysqljail/db/mysql           30.6K   188G     30.6K  /var/db/mysql
tank/mysqljail/db/innodb          91.9K   188G     30.6K  /tank/mysqljail/db/innodb
tank/mysqljail/db/innodb/data     30.6K   188G     30.6K  /var/db/innodb/data
tank/mysqljail/db/innodb/log      30.6K   188G     30.6K  /var/db/innodb/log

And finally corrected the values in /usr/local/etc/mysql/my.cnf

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
slave-load-tmpdir               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
log-bin                         = mysql-bin
innodb_data_home_dir            = /var/db/innodb/data
innodb_log_group_home_dir       = /var/db/innodb/log
...

When I try to start the MySQL service, I get the following error

# mysql-server start
...
The designated data directory /var/db/mysql/ is unusable. You can remove all files that the server added to it.
...

Any hints and comments will be appreciated,
Ser

 

[SirDice]

In order for initdb to do its thing the datadir has to be completely empty. Run it once without all the various subdirectories so the initdb can do its thing. Then move stuff around to separate datasets. Annoying but initdb just doesn't like it when there's already something in that directory.