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/configLocate 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.serviceMake 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:
Now you can start your MySQL service via the following command
systemctl start mysqld.serviceBut 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] AbortingResolving /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 /homeAs 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.confcopy the following lines to the new centreon.conf file and save it
[Service] ProtectHome=false #ProtectSystem=offApply the changes by running the following command
systemctl daemon-reloadNow you can run the MySQL service:
systemctl start mysqld.serviceEnabling 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.
getenforcethe SELinux context uses mysqld_db_t and if it is not set correctly mysqld process will be aborted, so you need to update it:
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-dataFor 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 stopservice mysqld status
Then copy recursively the contents of /var/lib/mysql to /mnt/mysql-datapreserving original permissions and timestamps:
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-dataStep 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 startNow, 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!