Introduction
Running out of disk is typical for the primary filesystem or partition on a server. For example, if the /root or / is low on disk space, moving /var/lib/mysql to /home will free up some storage at this location. This guide will assist with moving the directory on a Centos 6 or Centos 7 server with cPanel.
Note: This procedure requires a certified administrator to prevent critical failures. The details in this article should not be used for MariaDB. Moving MariaDB to "/home" causes upgrades to be blocked for MariaDB.
- As of MariaDB 10.1.16, the data directory cannot reside in /home, /usr, /etc, /boot, or /root directories on systemd equipped systems.
It is possible to disable the ProtectHome feature and use the whmapi1 API to perform the upgrade process, however this is a task intended for administrators experienced with MySQL/MariaDB.
MySQL version 5.7 was in use for this process. Please be sure to set aside downtime to perform the below steps.
Procedure
1. Make a full mysqldump backup of all databases.
mysqldump -A --opt > /home/mysqldump.sql
2. Disable WHM MySQL monitoring to prevent restarts during the process. Restarts could cause issues during this procedure.
whmapi1 configureservice service=mysql enabled=1 monitored=0
3. Stop MySQL.
systemctl stop mysqld
4. Ensure that the following settings are present.
Edit file /etc/systemd/system/mysqld.service.d/override.conf
- You can also use the command "systemctl edit mysqld" to edit the file to add the below settings.
[Service]
ProtectHome=false
ProtectSystem=off
Reload with systemctl after the changes.
systemctl daemon-reload
5. Make the directory for MySQL in /home, move it.
mkdir /home/var_mysql
mv /var/lib/mysql /home/var_mysql
chown -R mysql:mysql /home/var_mysql/mysql
6. Create an empty /var/lib/mysql directory where the socket file will be located.
mkdir /var/lib/mysql/
chown mysql:mysql /var/lib/mysql/
7. Edit /etc/my.cnf file to ensure it contains these directives under the [mysqld] section.
socket=/var/lib/mysql/mysql.sock
datadir=/home/var_mysql/mysql
Note: The socket file path may already exist; you can omit this change if the value is already there.
The datadir path may already exist as well. You can change the existing configuration option or add a comment, then add the new datadir value. You must only have one socket and one datadir value in the configuration file.
8. Start MySQL.
systemctl start mysqld
9. Enable MySQL monitoring.
whmapi1 configureservice service=mysql enabled=1 monitored=1
After the steps have been performed successfully, MySQL will be running with the new datadir.
The below command will help verify.
lsof -p `ps -fU mysql|awk '{print $2}' |grep -v PID`
