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.
|
1 |
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
|
1 |
whmapi1 configureservice service=mysql enabled=1 monitored=0 |
Stop the MySQL server
|
1 |
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.
|
1 2 3 |
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
|
1 2 3 |
mv /var/lib/mysql /var/lib/mysql-backup mkdir /var/lib/mysql/ chown mysql:mysql /var/lib/mysql/ |
Update my.cnf file
|
1 |
vi /etc/my.cnf |
Add
|
1 2 |
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
|
1 |
vi /etc/systemd/system/mariadb.service.d/override.conf |
Add
|
1 2 3 |
[Service] ProtectHome=false ProtectSystem=off |
Since we edit systemd file, we need to reload
|
1 |
systemctl daemon-reload |
Start MySQL
|
1 |
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
|
1 |
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.
|
1 2 |
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