How to Move MySQL Datadir – cPanel

Introduction

Running out of disk space 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 server with cPanel.

Please note that 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 task is 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

Make a full mysqldump backup of all databases.
```
mysqldump -A --opt > /home/mysqldump.sql
```
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
```
Stop MySQL.
```
systemctl stop mysqld
```
Create or edit the file /etc/systemd/system/mysqld.service.d/override.conf to ensure that the following settings are present.
```
[Service]
ProtectHome=false
ProtectSystem=off
```
Reload with systemctl after the changes.
```
systemctl daemon-reload
```

5. Make the directory for MySQL in /home, and move it.

mkdir /home/var_mysql
mv /var/lib/mysql /home/var_mysql
chown -R mysql:mysql /home/var_mysql/mysql

Create an empty /var/lib/mysql directory where the socket file will be located.
```
mkdir /var/lib/mysql/; chown mysql:mysql /var/lib/mysql/
```
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
```
Please note that the socket file path may already exist; you can omit this change if the value exists.

The datadir path may already exist as well. You must change the existing configuration option or add a comment before the entry to disable it and then add the new datadir value. The configuration file must only have one socket and one datadir value.
Start MySQL.
```
systemctl start mysqld
```

Enable MySQL monitoring.

whmapi1 configureservice service=mysql enabled=1 monitored=1

On Ubuntu systems, the AppArmor profile needs updating; this can be easily configured in the /etc/apparmor.d/local/usr.sbin.MySQL file. Update the end of this file with a configuration like the following. In this example,/home/mysql was my new MySQL directory:

/home/mysql/ r,
/home/mysql/** rwk,
/home/mysql/plugin/ r,
/home/mysql/plugin/*.so* mr,

After the steps have been performed successfully, MySQL will run with the new datadir. The below command will help verify this was successful.

lsof -p `ps -fU mysql|awk '{print $2}' |grep -v PID`

Please also note that if these details are followed on CloudLinux servers, additional steps may be required if websites return 500 errors or cannot connect to the databases. Please take a look below for more details.

On CloudLinux servers, sites show database-connection errors after move of MySQL data directory

Introduction

Procedure

Related articles