Server Administration & Management

How to change MySQL database directory to another partition as /var patition full

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 :-

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

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

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

# mkdir /home/mysql

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

# cp -prdf /var/lib/mysql  /home/

# mv /var/lib/mysql /var/lib/mysql-bk

4. 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. Take a backup of /etc/my.cnf

# cp /etc/my.cnf  /etc/my.cnf.bak

5. Now edit /etc/my.cnf

# vi /etc/my.cnf
log_slow_queries = /var/log/mysql/mysql-slow.log

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).


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

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

# cp /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php  /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php.bak

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

# vi /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php

3. 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’;

4. Now start the database server.

# /etc/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

August 31, 2011 Posted by | cPanel, MySQL | , , | Leave a comment