MariaDB failed to start after change of datadir

Question

Background Info:

I let the CentOS 7 installer to partition automatically.

Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-root   50G   43G  7.9G  85% /
devtmpfs                  32G     0   32G   0% /dev
tmpfs                     32G     0   32G   0% /dev/shm
tmpfs                     32G  9.3M   32G   1% /run
tmpfs                     32G     0   32G   0% /sys/fs/cgroup
/dev/sda1                492M  123M  369M  25% /boot
/dev/mapper/centos-home  476G   54G  422G  12% /home
tmpfs                    6.3G     0  6.3G   0% /run/user/1000
tmpfs                    6.3G     0  6.3G   0% /run/user/0

But I didn't expect not most of the disk space goes to the / root directory, and MariaDB is now 35GB in /var/lib/mysql running out of space soon.

So I changed the datadir, by commands below, but then failed to start

systemctl stop mariadb
cp -fR /var/lib/mysql /home/
chown -R mysql:mysql /home/mysql

vi /etc/my.cnf.d/server.cnf and added the following line

[mysqld] 
datadir=/home/mysql

systemctl start mariadb

but it failed and compliant about "Can't create test file"

Jul 06 22:32:26 ging2 systemd[1]: Starting MariaDB database server...
Jul 06 22:32:26 ging2 mysqld[9450]: 2017-07-06 22:32:26 140114825693312 [Note] options --log-slow-admin-statements, --log-queries-not-us...s not set
Jul 06 22:32:26 ging2 mysqld[9450]: 2017-07-06 22:32:26 140114825693312 [Note] /usr/sbin/mysqld (mysqld 10.2.5-MariaDB) starting as process 9450 ...
Jul 06 22:32:26 ging2 mysqld[9450]: 2017-07-06 22:32:26 140114825693312 [Warning] Can't create test file /home/mysql/ging2.lower-test
Jul 06 22:32:26 ging2 mysqld[9450]: [87B blob data]
Jul 06 22:32:26 ging2 mysqld[9450]: 2017-07-06 22:32:26 140114825693312 [ERROR] Aborting

I have no idea why it doesn't work and I think the permissions and ownership are the same as the old datadir.

I think some of you guys know why. So could anyone help please?

Thanks!!

Answer 1

This just hit me on a Debian 8 -> 9 upgrade. I could not make MariaDB start for my datadir under /home, no matter what I tried.

All the answers I could find referenced changing AppArmor or SELinux, but neither of those was installed.

The problem is actually systemd , which has a security directive in the MariaDB startup file preventing it from using /home!

The systemd service file (/lib/systemd/system/mariadb.service on Debian) contains the setting: ProtectHome=true . This setting will prevent MySQL from writing to any folder under /home and thus it will throw ErrNo 13: Permission Denied errors on startup.

The fix is to either move the datadir out of /home, or otherwise to turn off this security feature in the systemd service file:

I fixed it as follows:

1: sudo cp /lib/systemd/system/mariadb.service /etc/systemd/system/ # make a local override of the service file

2: Edit /etc/systemd/system/mariadb.service and change ProtectHome to be false (ProtectHome=false). Save the file

3: sudo systemctl daemon-reload # refresh the systemd services so it sees your override file

4: sudo systemctl start mysql

...and it should finally start!!!

You will also need to deal with AppArmor and SELinux if those are installed, both will also prevent a change to the MySQL data dir.

Answer 2

ِYou are trying to change MySQL's datadir to the directory which is located under /home, and usually it is not acceptable by MySQL. First you need to change the default configuration of MySQL, then give some system permission to the new datadir directory and its parents, and finally tweak the SELinux Context.

Although you have passed many of steps correctly, I like to demonstrate the complete steps:

Disabling SELinux

First of all, let me mention that RedHat-based Linux Distributions (RHELs) like Fedora, CentOS, etc. use SELinux that enforces mandatory access control policies. So it's better to disable it during the following steps and later enable it with some tweaks.

Open the SELinux configuration file

nano /etc/selinux/config

Locate the line that contains SELINUX=enforcing and change its value to SELINUX=disabled, save the file and reboot your system.

Changing the datadir of MySQL

Stop the MySQL services

systemctl stop mysqld.service

Make a new directory for MySQl's data directory. Due to some reasons which are out of the scope of this solution, it's highly recommended to not create a data directory under the /home directory, but maybe some of you like me prefer it (it costs more steps).

mkdir /home/mysql

Set ownership and permissions of the new directory to the default MySQL's data directory(/var/lib/mysql):

chown --reference=/var/lib/mysql   /home/mysql
chmod --reference=/var/lib/mysql   /home/mysql

Copy all files from the default directory to the new one

cp -rp /var/lib/mysql/*   /home/mysql

Edit the /etc/my.cnf file, add add the following line under [mysqld] section:

[mysqld]
datadir=/home/mysql

Resolving /home issues

Make sure that all the parent directories of the new datadir upwards have x (execute) permissions for all (user, group, and other). I prefer to not use a recursive script so:

chmod +x /home/mysql
chmod +x /home

As it is mentioned creating datadir under /home directory is tricky because by default MySQL does not allow it. Create a file under /etc/systemd/system/mariadb.service.d and put the following lines in:

#open an editor to create a file
nano /etc/systemd/system/mariadb.service.d/centreon.conf 

copy the following lines to the new centreon.conf file and save it

[Service]
ProtectHome=false 
#ProtectSystem=off

Apply the changes by running the following command

systemctl daemon-reload

Now you can run the MySQL service:

systemctl start mysqld.service

Enabling SELinux

Again edit the /etc/selinux/config file, and change the line of SELINUX=disabled to SELINUX=enforcing. Save the file and reboot your system.

To query the current status of SELinux use the following commands, it should print enforcing as an output.

getenforce

the SELinux context uses mysqld_db_t and if it is not set correctly mysqld process will be aborted, so you need to update it:

semanage fcontext -a -t mysqld_db_t "/home/mysql(/.*)?"

restorecon -Rv /home/mysql