How to change MySQL Datadir in Cpanel Server

If the disk partition that stores MySQL data directory runs out of disk space, you may need to consider moving the MySQL data directory to another partition. Before you do the MySQL data directory change, make sure you take a backup of all your databases using mysqldump.

mysqldump --events --routines --triggers --all-databases | gzip -9 > "$(date +%F-%H%m%S)"-mysql-backup.sql.gz

First, disable MySQL monitoring, so MySQL won’t auto start while we do the data directory migration

whmapi1 configureservice service=mysql enabled=1 monitored=0

Stop the MySQL server

systemctl stop mysqld

In this case, we have free disk space in /home partition so we will move the MySQL data directory to this partition. Create a directory to store MySQL data, and copy the MySQL files to it.

mkdir /home/mysql-data
rsync -avzP /var/lib/mysql/ /home/mysql-data/
chown -R mysql:mysql /home/mysql-data/

Rename /var/lib/mysql directory, create an empty directory for MySQL socket

mv /var/lib/mysql /var/lib/mysql-backup
mkdir /var/lib/mysql/
chown mysql:mysql /var/lib/mysql/

Update my.cnf file

vi /etc/my.cnf

Add

socket=/var/lib/mysql/mysql.sock
datadir=/home/mysql-data/

MariaDB 10.1 or newer does not allow MySQL data in /home directory. To make datadir work from /home, edit file

vi /etc/systemd/system/mariadb.service.d/override.conf

Add

[Service]
ProtectHome=false
ProtectSystem=off

Since we edit systemd file, we need to reload

systemctl daemon-reload

Start MySQL

systemctl start mysqld

At this point, MySQL will work from the new data directory. Verify sites are working properly.

If all is good, enable cpanel service monitoring for MySQL

whmapi1 configureservice service=mysql enabled=1 monitored=1

If you are using CloudLinux CageFS, sites won’t work. You need to run these 2 commands to get MySQL working for websites.

cagefsctl --disable-all
cagefsctl --enable-all

Remove old MySQL datadir

We have renamed original MySQL data directory as /var/lib/mysql-backup, you can take a backup of this directory and delete it to free up disk space.

Back to MySQL