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

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: