Bidhan Khatri

Bidhan Khatri

Linux guy

Change MySQL folder in cPanel/WHM Server.

Due to low space in /var partition, I need to move MySQL data to another partition /mysqlData.
But before beginning the process let’s first create the full mysqldump first.

Take MySQL Dump first.

mysqldump --all-databases | gzip > /home/alldatabases.sql.gz

Uncheck monitor in WHM > Service Manager for Mysql and save the area. Now your MySQL service may have already stopped. If not then

/etc/init.d/mysql stop

Make the directory for MySQL in /mysqlData, move it and symlink it.
Also, look for the trailing slash / character at the end of the directory name.

mkdir /mysqlData
mv /var/lib/mysql /mysqlData
chown -R mysql:mysql /mysqlData/mysql
ln -s /mysqlData/mysql /var/lib/mysql
/etc/init.d/mysql start

Re-check monitor in WHM > Service Manager for MySQL and save the area.

Though MySQL service is up and running you may still face the database connection error partially from some websites if your cPanel/WHM host is in CloudLinux Server. I still remember that hectic night thinking whether to restore the backed up database or take time and search for the solution because provided maintenance windows time was already exceeded.

service mysql status 
  SUCCESS! MySQL running (338051)
 
netstat -pln | grep mysql
  tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 338051/mysqld 
  unix 2 [ ACC ] STREAM LISTENING 656797141 338051/mysqld /var/lib/mysql/mysql.sock

## Here you can see Unix socket and TCP/IP both are on listening state.

If the database connection is through localhost then you might get error like below.
If it is 127.0.0.1 then you will not have an issue.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

And the solution for the above error is:

cagefsctl --remount-all

Finally, we’ve successfully migrated our MySQL location to a different location. Now add entry to /etc/fstab file.

cat /etc/fstab


UUID=169e72a1-ea17-6347-8d37-7a03da7e67e5       /mysqlData      ext4    usrjquota=quota.user,jqfmt=vfsv0        1       2

Also, If anything goes wrong, you still have the full mysqldump backup and go for the below step.

gunzip < /home/alldatabases.sql.gz | mysql

Comments

You May Also Enjoy