What should the data directory be for mysql?

I could successfully alter the data directory of MySQL or MariaDB, and resolve all related issues on Fedora 30. I think the following steps will work on other distributions.

Note: Users of Debian-based distributions like Ubuntu should search how to disable and edit AppArmor and then follow the following 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/eloy/applications/mysql-datadir/

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

chown --reference=/var/lib/mysql   /home/eloy/applications/mysql-datadir/
chmod --reference=/var/lib/mysql   /home/eloy/applications/mysql-datadir/

Copy all files from the default directory to the new one

cp -rp /var/lib/mysql/*   /home/eloy/applications/mysql-datadir/

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

[mysqld]
datadir=/home/eloy/applications/mysql-datadir/

Now you can start your MySQL service via the following command

systemctl start mysqld.service

But if the data directory is created under /home, MySQL won't start and you would see the following errors and warnings after journalctl -xe:

Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: 2021-10-05 10:22:03 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32190) 
Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: 2021-10-05 10:22:03 0 [Warning] Can't create test file /home/eloy/applications/mysql-datadir/eloy-fedora-laptop.lower-> 
Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: [113B blob data] 
Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: 2021-10-05 10:22:03 0 [ERROR] Aborting

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/eloy/applications/mysql-datadir
chmod +x /home/eloy/applications
chmod +x /home/eloy/
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/eloy/applications/mysql-datadir(/.*)?"

restorecon -Rv /home/eloy/applications/mysql-datadir

Now you can run MySQL. Cheers ;-)

After installing MySQL database for a production server, we may want to change the default data directory of MySQL to a different directory. This is the case when such directory is expected to grow due to high usage. Otherwise, the filesystem where /var is stored may collapse at one point causing the entire system to fail. Another scenario where changing the default directory is when we have a dedicated network share that we want to use to store our actual data. MySQL uses /var/lib/mysql directory as default data directory for Linux based systems.

In order to change the default directory, we need to check the available storage. We can use the df command to discover drive space on Linux. The output of df -Hwill report how much space is used, available, the percentage used, and the mount point of every disk attached to your system.

We are going to assume that our new data directory is /mnt/mysql-data. It is important to note that this directory should be owned by mysql:mysql.

mkdir -p /home/mysql-data

For simplicity, I’ve divided the procedure into 4 simple steps.

Step 1: Identify Current MySQL Data Directory

To identify the current data directory use the following command.

mysql -u username -p -e “SELECT @@datadir”

We need to identify the current MySQL data directory as it can be changed in the past. Let’s assume the current data directory is /var/lib/mysql

Step 2: Copy MySQL Data Directory to the desired location

To avoid data corruption, stop the service if it is currently running before proceeding and check the status.

service mysqld stop
service mysqld status

Then copy recursively the contents of /var/lib/mysql to /mnt/mysql-datapreserving original permissions and timestamps:

cp -rap /var/lib/mysql/* /mnt/mysql-data

Change the permission of the directory as its owner should be mysql:mysql. We can use the following command to change the ownership of the directory:

chown -R mysql:mysql /mnt/mysql-data

Step 3: Configure the new MySQL Data Directory

Edit the MySQL default configuration file /etc/my.cnf and update values of mysqld and client.

# Change From:[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock# Change To:[mysqld]
datadir=/mnt/mysql-data/mysql
socket=/mnt/mysql-data/mysql/mysql.sock

If there is no client variable then add, or else, update it to:

[client]
port=3306
socket=/mnt/mysql-data/mysql.sock

Step 4: Enable the MySQL Service and confirm the directory change

Restart the MySQL service using the following command:

service mysqld start

Now, use the same command to verify the location change of the new data directory:

mysql -u username -p -e “SELECT @@datadir”

If you face any issue during MySQL startup check MySQL log file /var/log/mysqld.log for any errors.

That’s it. Hope this helps.

If you enjoyed this post, I’d be very grateful if you’d help it spread by emailing it to a friend or sharing it on Twitter or Facebook. Thank you!

What is the MySQL data directory?

How do I find MySQL data directory?

How do I initialize a MySQL data directory?

What is data directory in database?