Due to the space issue in /var partition, I need to move the MySQL data to another partition. But before beginning the process let’s first create the full mysqldump first.
Create the dump file.
# 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, you’ve successfully migrated your MySQL location to a different folder.
# 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