Unable to restart mariadb after update

RedGiant asked:

I have just updated mariadb to the latest version in arch-linux. I have the database folder in /home/mysql. Out of nowhere, I can’t get it restart with systemctl restart mysqld. Here’s the error log:

-- Unit mariadb.service has begun starting up.
Jul 28 00:25:20 tao mysqld[8523]: 2016-07-28  0:25:20 140554735999040 [Note] /usr/sbin/mysqld (mysqld 10.1.16-MariaDB)
Jul 28 00:25:20 tao mysqld[8523]: 2016-07-28  0:25:20 140554735999040 [Warning] Can't create test file /home/mysql/tao
Jul 28 00:25:20 tao mysqld[8523]: [87B blob data]
Jul 28 00:25:20 tao mysqld[8523]: 2016-07-28  0:25:20 140554735999040 [ERROR] Aborting
Jul 28 00:25:20 tao systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Jul 28 00:25:20 tao systemd[1]: Failed to start MariaDB database server.
-- Subject: Unit mariadb.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mariadb.service has failed.
-- 
-- The result is failed.
Jul 28 00:25:20 tao systemd[1]: mariadb.service: Unit entered failed state.
Jul 28 00:25:20 tao systemd[1]: mariadb.service: Failed with result 'exit-code'.
Jul 28 00:25:20 tao sudo[8405]: pam_unix(sudo:session): session closed for user root

This is probably the fatal error :

`Can't create test file /home/mysql/tao`

Does it have anything to do with my db folder being in the home directory? I wonder what has been changed that prevents mariadb from accessing /home/mysql.

/etc/systemd/system/mariadb.service file:

#
# /etc/systemd/system/mariadb.service
#
# This file is free software; you can redistribute it and/or modify it
# under the terms of the GNU Lesser General Public License as published by
# the Free Software Foundation; either version 2.1 of the License, or
# (at your option) any later version.
#
# Thanks to:
# Daniel Black
# Erkan Yanar
# David Strauss
# and probably others

[Unit]
Description=MariaDB database server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target


[Service]

##############################################################################
## Core requirements
##

Type=notify

# Setting this to true can break replication and the Type=notify settings
# See also bind-address mysqld option.
PrivateNetwork=false

##############################################################################
## Package maintainers
##

User=mysql
Group=mysql

# To allow memlock to be used as non-root user if set in configuration
CapabilityBoundingSet=CAP_IPC_LOCK

# Prevent writes to /usr, /boot, and /etc
ProtectSystem=full

NoNewPrivileges=true

PrivateDevices=true

# Prevent accessing /home, /root and /run/user
ProtectHome=true

# Execute pre and post scripts as root, otherwise it does it as User=
PermissionsStartOnly=true

# Perform automatic wsrep recovery. When server is started without wsrep,
# galera_recovery simply returns an empty string. In any case, however,
# the script is not expected to return with a non-zero status.
# It is always safe to unset _WSREP_START_POSITION environment variable.
ExecStartPre=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
ExecStartPre=/bin/sh -c "VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && 
 systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1"

# Needed to create system tables etc.
# ExecStartPre=/usr/bin/mysql_install_db -u mysql

# Start main service
# MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
# Use the [service] section and Environment="MYSQLD_OPTS=...".
# This isn't a replacement for my.cnf.
# _WSREP_NEW_CLUSTER is for the exclusive use of the script galera_new_cluster

ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION


# Unset _WSREP_START_POSITION environment variable.
ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"

KillMode=process
KillSignal=SIGTERM

# Don't want to see an automated SIGKILL ever
SendSIGKILL=no

# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
Restart=on-abort
RestartSec=5s

UMask=007

##############################################################################
## USERs can override
##
##
## by creating a file in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
## and adding/setting the following will override this file's settings.

# Useful options not previously available in [mysqld_safe]

# Kernels like killing mysqld when out of memory because its big.
# Lets temper that preference a little.
# OOMScoreAdjust=-600

# Explicitly start with high IO priority
# BlockIOWeight=1000

# If you don't use the /tmp directory for SELECT ... OUTFILE and
# LOAD DATA INFILE you can enable PrivateTmp=true for a little more security.
PrivateTmp=true

##
## Options previously available to be set via [mysqld_safe]
## that now needs to be set by systemd config files as mysqld_safe
## isn't executed.
##

# Number of files limit. previously [mysqld_safe] open-file-limit
LimitNOFILE=16364

# Maximium core size. previously [mysqld_safe] core-file-size
# LimitCore=

# Nice priority. previously [mysqld_safe] nice
# Nice=-5

# Timezone. previously [mysqld_safe] timezone
# Environment="TZ=UTC"

# Library substitutions. previously [mysqld_safe] malloc-lib with explict paths
# (in LD_LIBRARY_PATH) and library name (in LD_PRELOAD).
# Environment="LD_LIBRARY_PATH=/path1 /path2" "LD_PRELOAD=

# Flush caches. previously [mysqld_safe] flush-caches=1
# ExecStartPre=sync
# ExecStartPre=sysctl -q -w vm.drop_caches=3

# numa-interleave=1 equalivant
# Change ExecStart=numactl --interleave=all /usr/sbin/mysqld......

# crash-script equalivent
# FailureAction=

/home.mysql folder permission

drwxr-xr-x  6 mysql       mysql 12288 Jul 27 19:34 mysql

my.cf

[client]
port        = 3306
socket      = /run/mysqld/mysqld.sock

[mysqld]
init_connect                = 'SET collation_connection = utf8_general_ci,NAMES utf8'
collation_server            = utf8_general_ci
#character_set_client        = utf8
skip-character-set-client-handshake
character_set_server        = utf8
port        = 3306
socket      = /run/mysqld/mysqld.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

bind-address = 127.0.0.1

log-bin=mysql-bin

binlog_format=mixed

server-id   = 1
datadir = /home/mysql
innodb_data_home_dir = /home/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

OS detail:

Linux version 4.6.4-1-ARCH (builduser@tobias) (gcc version 6.1.1 20160707 (GCC) ) #1 SMP PREEMPT Mon Jul 11 19:12:32 CEST 2016

I don’t think I have SELinux installed. You can check my package here:

acpid 2.0.27-1
adobe-source-han-sans-otc-fonts 1.004-1
alsa-utils 1.1.1-1
android-studio 2.1.2.0-1
anki 2.0.36-2
apache 2.4.23-1
autoconf 2.69-3
automake 1.15-1
bash 4.3.046-1
binutils 2.26.1-1
bison 3.0.4-2
bzip2 1.0.6-5
chromium 52.0.2743.82-1
chromium-pepper-flash 1:22.0.0.192-1
chrpath 0.16-1
composer 1.2.0-1
coreutils 8.25-2
cronie 1.5.1-1
cryptsetup 1.7.2-1
device-mapper 2.02.161-1
dhcpcd 6.11.1-1
dialog 1:1.3_20160424-1
diffutils 3.3-3
e2fsprogs 1.43.1-1
exo 0.10.7-2
fakeroot 1.21-1
fcitx 4.2.9.1-2
fcitx-configtool 0.4.8-1
fcitx-gtk2 4.2.9.1-2
fcitx-gtk3 4.2.9.1-2
fcitx-qt4 4.2.9.1-2
fcitx-qt5 1.0.5-4
fcitx-rime 0.3.1-3
file 5.28-1
filesystem 2015.09-1
findutils 4.6.0-1
firefox 47.0.1-1
flashplugin 11.2.202.632-1
flex 2.6.0-3
gamin 0.1.10-8
garcon 0.4.0-1
gawk 4.1.3-1
gcc 6.1.1-3
gcc-libs 6.1.1-3
gettext 0.19.8.1-2
glibc 2.23-5
grep 2.25-2
groff 1.22.3-6
gtk-xfce-engine 2.10.1-1
gvfs 1.28.2-1
gvfs-mtp 1.28.2-1
gzip 1.8-2
haproxy 1.6.7-1
inetutils 1.9.4-2
iproute2 4.5.0-2
iputils 20160308.0db72a4-1
iw 4.7-1
jdk 8u92-1
jfsutils 1.1.15-4
less 481-2
lib32-mpg123 1.23.5-1
libtool 2.4.6-5
licenses 20140629-1
linux 4.6.4-1
logrotate 3.9.2-1
lsof 4.89-1
lvm2 2.02.161-1
m4 1.4.17-2
make 4.2.1-1
man-db 2.7.5-3
man-pages 4.07-1
mariadb 10.1.16-1
mdadm 3.4-1
mlocate 0.26-5
mousepad 0.4.0-2
nano 2.6.1-1
netctl 1.12-2
nodejs 6.3.1-1
noto-fonts 20160613-1
npm 3.10.5-1
orage 4.12.1-3
p7zip 16.02-1
pacman 5.0.1-4
patch 2.7.5-1
pavucontrol 3.0-4
pciutils 3.5.1-1
pcmciautils 018-7
perl 5.24.0-1
php 7.0.9-1
php-apache 7.0.9-1
php-fpm 7.0.9-1
php-gd 7.0.9-1
php-pear 1:1.10.1-1
php-redis-git 2.2.7.r396.gad3c116-1
php-solr 2.4.0-1
phpstorm 2016.2-1
pipelight 1:0.2.8.2-2
pkg-config 0.29.1-2
procps-ng 3.3.12-1
psmisc 22.21-3
redis 3.2.1-1
redshift 1.11-3
reiserfsprogs 3.6.25-1
ristretto 0.8.0-2
rsync 3.1.2-1
ruby-sass 3.4.22-1
s-nail 14.8.9-1
s3cmd 1.6.1-2
sed 4.2.2-4
shadow 4.2.1-3
slim 1.3.6-6
smartmontools 6.5-1
sshfs 2.7-1
sudo 1.8.17.p1-1
sysfsutils 2.1.0-9
systemd-sysvcompat 230-7
tar 1.29-1
texinfo 6.1-4
thunar 1.6.10-3
thunar-archive-plugin 0.3.1-6
thunar-media-tags-plugin 0.2.1-2
thunar-volman 0.8.1-2
ttf-liberation 2.00.1-6
tumbler 0.1.31-1
usbutils 008-1
util-linux 2.28-1
vi 1:070224-2
vlc 2.2.4-2
vokoscreen 2.5.0-1
which 2.21-2
winetricks 20160724-1
wpa_supplicant 1:2.5-3
xdebug 2.4.0-1
xdotool 3.20150503.1-1
xfburn 0.5.4-3
xfce4-appfinder 4.12.0-4
xfce4-artwork 0.1.1a_git20110420-4
xfce4-battery-plugin 1.0.5-5
xfce4-clipman-plugin 1.2.6-3
xfce4-cpufreq-plugin 1.1.3-1
xfce4-cpugraph-plugin 1.0.5-4
xfce4-datetime-plugin 0.6.2-4
xfce4-dict 0.7.2-1
xfce4-diskperf-plugin 2.5.5-1
xfce4-eyes-plugin 4.4.5-1
xfce4-fsguard-plugin 1.0.2-5
xfce4-genmon-plugin 3.4.0-3
xfce4-mailwatch-plugin 1.2.0-7
xfce4-mixer 4.11.0-3
xfce4-mount-plugin 0.6.7-4
xfce4-mpc-plugin 0.4.5-1
xfce4-netload-plugin 1.2.4-3
xfce4-notes-plugin 1.8.1-2
xfce4-notifyd 0.2.4-3
xfce4-panel 4.12.0-2
xfce4-power-manager 1.4.4-2
xfce4-screenshooter 1.8.2-3
xfce4-sensors-plugin 1.2.6-3
xfce4-session 4.12.1-4
xfce4-settings 4.12.0-4
xfce4-smartbookmark-plugin 0.4.6-1
xfce4-systemload-plugin 1.1.2-2
xfce4-taskmanager 1.1.0-1
xfce4-terminal 0.6.3-3
xfce4-time-out-plugin 1.0.2-2
xfce4-timer-plugin 1.6.0-4
xfce4-verve-plugin 1.1.0-1
xfce4-wavelan-plugin 0.5.12-1
xfce4-weather-plugin 0.8.7-1
xfce4-whiskermenu-plugin 1.5.3-2
xfce4-xkb-plugin 0.7.1-2
xfconf 4.12.0-4
xfdesktop 4.12.3-2
xfsprogs 4.5.0-1
xfwm4 4.12.3-2
xfwm4-themes 4.10.0-2
xorg-server 1.18.4-1
xorg-server-utils 7.6-4
xorg-twm 1.0.9-1
xorg-xclock 1.0.7-1
xorg-xinit 1.3.4-4
xterm 325-1
yaourt 1.8.1-1

My answer:


You are using a wrong directory for datadir. You should not store MySQL/MariaDB’s data under /home. For security reasons the database is not allowed to access user home directories.

The “standard” place for the datadir is /var/lib/mysql. Change the datadir to the correct value and try again.


View the full question and answer on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.