Separating MySQL/MariaDB Data & Logs for ZFS

[SeaWolfX]

I am setting up a MySQL/MariaDB instance on my server using Docker and the data will be stored on a ZFS pool.

Several guides/blogs mentions that its best practice to separate data and log files to separate datasets with different properties. So in my docker-compose I create two volumes for mapping a data- and log directory under /var/lib/mysql.

Code:

  - type: volume
    source: cloud-database-data
    target: /var/lib/mysql/data

  - type: volume
    source: cloud-database-logs
    target: /var/lib/mysql/log

(normally, when not using ZFS I would have just mapped the whole /var/lib/mysql directory to a single volume)

I also map to a configuration override file which adds the following to the my.cnf so that logs and data are stored in different directories and not directly under /var/lib/mysql which is the default.

Code:

[mysqld]

datadir = /var/lib/mysql/data
innodb_log_group_home_dir = /var/lib/mysql/log
innodb_data_home_dir = /var/lib/mysql/data
slow_query_log_file = /var/lib/mysql/log/slow.log
log_error = /var/lib/mysql/log/error.log
aria-log-dir-path = /var/lib/mysql/log

When I create the container and navigate to the /var/lib/mysql directory (inside the container) I see that the data/ and and log/ directory has been created and contains data and log-files.

Code:

drwxr-xr-x 7 mysql mysql  9 Apr 18 08:03 ./
drwxr-xr-x 8 root  root   8 Apr  6 00:10 ../
drwxr-xr-x 2 mysql mysql  5 Apr 18 08:03 data/
drwxr-xr-x 2 mysql mysql  6 Apr 18 08:03 log/
-rw-rw---- 1 mysql mysql  0 Apr 18 08:03 multi-master.info
drwx------ 2 mysql mysql 90 Apr 18 08:03 mysql/
-rw-r--r-- 1 mysql mysql 15 Apr 18 08:03 mysql_upgrade_info
drwx------ 2 mysql mysql  3 Apr 18 08:03 nextcloud/
drwx------ 2 mysql mysql  3 Apr 18 08:03 performance_schema/

However, there are still some additional files and folders (I guess one for each database, they only contain a db.opt file) in the /var/lib/mysql directory. I assume that these are file that also need to be persisted (as typical practice is to persist the whole mysql directory) so that they are not lost if recreating the container for instance. Do I need to create a third volume that maps to the root directory or are these files/folders not important to persist?

Is this the correct approach for separating logs and data files when using MySQL/MariaDB Docker image?

 

[sretalla]

Is this the correct approach for separating logs and data files when using MySQL/MariaDB Docker image?

Not specific to docker, but this is what the guys who maintain ZFS say:

Workload Tuning — OpenZFS documentation

openzfs.github.io

Mostly seems aligned with what you mention, maybe a couple of other subtle points.

 

[SeaWolfX]

Not specific to docker, but this is what the guys who maintain ZFS say:

Workload Tuning — OpenZFS documentation

openzfs.github.io

Mostly seems aligned with what you mention, maybe a couple of other subtle points.

Thanks for your input.

Yes, my question is not really to do with Docker or ZFS, but rather with MySQL and how to actually split the data and logs property so that I can map those directories to individual datasets.