Adjust filesystem options for MariaDB (MySQL)

The filesystem where MariaDB (MySQL) datadir is stored (“/var/lib/mysql” by default) should be mounted with theses options:

• noatime : disable the update of files access time. Limit I/O load generated by MySQL disk accesses.
• nodiratime : disable the update of folders access time.
• barrier=1 : Reduce risks of data loss in case of crash.
• data=ordered : Write data to the disk before updating the filesystem log. Reduce risks of data loss in case of crash.

This howto is tested on:

• Debian 10.0 Buster

Requirements

This howto require:

• a MySQL or MariaDB server, as described by Install MariaDB (MySQL) on Debian.

Environment setup

Detect if sudo is available (“command” is used if not):

cmdProxy='command'
command type -f 'sudo' &>'/dev/null' && cmdProxy='sudo'

Detect MySQL datadir path:

datadirPath="$(${cmdProxy} mysqld --verbose --help 2>'/dev/null' \
    | command grep '^datadir' \
    | command sed 's/^datadir[\t ]*//')"

Detect datadir device and mount point:

datadirMountDevice="$(${cmdProxy} df --output='target' "${datadirPath}" \
  | command tail -n '+2')"
datadirMountPoint="$(${cmdProxy} df --output='target' "${datadirPath}" \
  | command tail -n '+2')"

Get datadir mount point fstab options:

currentMountOptions="$(command grep -e "[\t ]${datadirMountPoint}[\t ]" '/etc/fstab' \
  | command tr --squeeze-repeats "[:blank:]" ' ' \
  | command cut --delimiter=' ' --fields=4)"

Split options in a array:

IFS=', ' command read -r -a editedOptions <<< "${currentMountOptions}"

Configuration

Add recommended options to the list:

editedOptions+=( 'noatime' 'nodiratime' 'barrier=1' 'data=ordered' )

Make sure each option is only present once:

editedOptions=($(command printf "%s\n" "${editedOptions[@]}" \
  | command sort -u))

Convert the option array to a comma separed string:

editedOptionsString="$(printf '%s,' "${editedOptions[@]}" | sed 's/,$//')"

Remount the mount point with the new options:

${cmdProxy} mount -o "remount,${editedOptionsString}" "${datadirMountPoint}"

Update fstab:

${cmdProxy} sed  -i \
    -e "s|^\([^ \t]*[ \t]*${datadirMountPoint}[ \t]*[^ \t]*[ \t]*\)${currentMountOptions}\([ \t].*\)|\1${editedOptionsString}\2|" \
  '/etc/fstab'

The ‘noatime’ and ‘nodiratime’ options may cause problems for some software (mutt is frequently named, but has been fixed since). These options can be replaced by ‘relatime’ and ‘reldiratime’ if a problem crop up.

Thanks

• Thanks to the authors of What is the best Linux filesystem for MariaDB (en).