UnixServerAdmin

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
———————————————————————
[mysqld]
datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time=1
wait_timeout=300
connect_timeout=60
interactive_timeout=300

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

[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
———————————————————————-

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

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: