MySQL CNID Backend
MySQL CNID Backend for Netatalk
MySQL (or MariaDB) can provide an alternative to BerkeleyDB as the database backend for Netatalk’s CNID daemon.
Below follows an example for a production setup on FreeBSD and ZFS pool with NVMe Special Device/Vdev;
This guide was originally written by @andylemin
Create a ZFS Vol for MySQL data dir;
zfs create -o atime=off -o recordsize=32k -o special_small_blocks=16k -o compression=lz4 -o xattr=sa -o dnodesize=auto -o checksum=blake3 -o redundant_metadata=most -o sync=disabled -o mountpoint=/var/db/mysql tank/cnid
If you don’t have a special device vdev, remove special_small_blocks=16k, and set recordsize=16k instead.
sync=disabled because it does not matter if the volume is lost/corrupted, as it takes just minutes to recreate (if you have a box with plenty of RAM and a warm ARC cache), so speed is more important.
Install MySQL
I personally recommend not changing the default MySQL data dir, as changing the default location can be painful, instead just mount the ZFS volume over the top of /var/db/mysql as above with mountpoint=/var/db/mysql.
pkg search mysql
pkg install mysql84-server mysql84-client
sudo service mysql-server enable
sudo service mysql-server start
Run insecure setup script and set root password
sudo mysql_secure_installation
Connect to MySQL and create cnid DB.
mysql -u root -p
CREATE DATABASE cnid;
Not sure if this is actually needed (it might be created automatically now) but I always do this out of habit.
Configure Netatalk to use MySQL
Arguably the hardest part about this is the afp.conf config;
Configuration - says very little, and does not mention anything about how Netatalk does the hard work for you of creating the DB Schema etc.
afp.conf - lists the 4 Global options under Miscellaneous Options, but does not give you any hints that you also need an associated setting for each of the volumes.. (this caught me out for ages). Ie, to search for cnid scheme in a different part of the docs. Having a complete example config (like below) in one place in the docs would help.
/usr/local/etc/afp.conf
[Global]
cnid mysql host = localhost
cnid mysql user = root
cnid mysql pw = <PASSWORD>
cnid mysql db = cnid
[share1]
cnid scheme = mysql
Start/Restart Netatalk
/usr/local/etc/rc.d/netatalk restart
Netatalk will create the required MySQL tables and schema automatically :)
Rebuild/populate the CNID DB on MySQL
dbd -f -F /usr/local/etc/afpd.conf
Ref. dbd manual page
With the default CNID backend, this takes several hours for a share with a few million objects. With MySQL CNID backend, this takes 10-20 minutes for me (around 10x faster in this setup).
Regarding “We still arguably end up with a “difficult” solution since setting up and administering a MySQL database is non-trivial for most users.” - I don’t see this, if a user can setup a Linux file server with Netatalk they could also setup MySQL as above. As long as the user is not running MySQL for other applications on the same box, it is mostly just setup and forget.. Sure there are a few steps here, but it is almost zero maintenance after installed. If you have any issues, you can just destroy and recreate the DB.
For example, the default tuning values for MySQL 8+ allow for a Netatalk share with several million files, long before you have to increase any memory limits, or do any fancy tuning. And even if you do, there are tools like mysqltuner.pl etc.
Worst case you might have to rebuild the CNID DB with dbd occasionally.
To be fair, you might have to fiddle with the boot scripts to ensure that the ZFS Vol is mounted before MySQL starts, and that Netatalk starts last after MySQL. And users should probably have MySQL listen on 127.0.0.1 only. Otherwise it is very little effort for a stable and significant speed boost.
Footnotes
This is a mirror of the Netatalk GitHub Wiki. Please visit the original page if you want to correct an error or contribute new contents.
Last updated 2025-04-18
