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

January 7, 2010 | In: Centos, Cpanel, Linux, Mysql

Hi guys,

you might have faced the issue of /var partition gettting full regularly due to database directory. If you have a larger partition with free space, then it is possible to move the database directory to the larger partition.

Here are the steps

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

Suggestions, questions are welcome.

mm
Hi All… I have been working the IT Industry since last ten years… Specialized in Linux, Cloud Infrastructure and Solution Design.
On the hobby friend, I do research on IOT, Micro-controllers, Electronics etc.
Music is my passion..Connect me on..

2 Responses to How to change Mysql database directory to another partition in cPanel. /var patition full

Avatar

Sergio

May 23rd, 2014 at 10:03 am

Does the symlink could work to move only one folder from inside /mysql instead of the complete /mysql ?

I mean, if inside /var/lib/mysql is one directory that is consuming 14GB of space and that is tne one that I want to move, How you would do it?

Thanks for your great page.

Avatar

Presoon John

October 9th, 2014 at 9:20 am

I just tried out moving just a single database from /var/lib/mysql to another folder.

/etc/rc.d/ini.d/mysql stop

Assume the destination partition of the database as /home

mv /var/lib/mysql/testdatabase /home/
ln -s /home/testdatabase /var/lib/mysql/testdatabase
chown -h mysql.mysql /var/lib/mysql/testdatabase

Start the service

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

It should be fine, if it starts well.

Comment Form

*

Authors

mm
Presoon John

Hi All... I have been working the IT Industry since last ten years... Specialized in Linux, Cloud Infrastructure and Solution Design.
On the hobby friend, I do research on IOT, Micro-controllers, Electronics etc.
Music is my passion..Connect me on..

Access this post via Mobile

Scan the QR Code
QR Code Generator

Categories