/var patition full, How to change Mysql database directory to another partition in cPanel

Switch off the database server while we are moving the databases.

/etc/rc.d/init.d/mysql stop

I am considering that I have enough space in /home partition. Here goes my new database data directory as /home/mysql

Now it is better to copy the database first, rather than move.

cp -pr /var/lib/mysql /home
mv /var/lib/mysql /var/lib/mysql-bk

We are copying the database to the new location since it is better to revert back the settings with minimum downtime, if anything goes wrong.

move to /tmp

cd /tmp
unlink mysql.sock
ln -s /home/mysql/mysql.sock /tmp/mysql.sock

Take a backup of /etc/my.cnf

Now edit /etc/my.cnf

vi /etc/my.cnf

add the line
datadir=/home/mysql

If the socket file is specified, comment it out.

Now move to /var/lib/mysql and create a symblink

ln -s /home/mysql /var/lib/mysql

(Please note that you don’t specify the socket file location in my.cnf since it causes issues with phpMyadmin)

For, cPanel server, edit the phpMyadmin configuration
take a backup of “/usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php”

edit this file /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php
vi /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php

add the following lines. If they already exist, edit as below.
(the connect_type usually exist at “tcp” change it to “socket”)

$cfg['Servers'][$i]['socket'] = ‘/home/mysql/mysql.sock’;
$cfg['Servers'][$i]['connect_type'] = ’socket’;

Now start the database server.

/etc/rc.d/ini.d/mysql start

If it starts fine, you are done. Check the database connections of your site.
You can now remove the directory /var/lib/mysql-bk