Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am using CentOS with cPanel. On my server, all MySQL databases save at /var/lib/mysql. Now /var is 100% full and MySQL has stopped working. How can I move the databases to a new directory like /home/mysql especially considering that this server is managed with cPanel?

share|improve this question

3 Answers 3

I can't confirm this from cpanel, as I don't have access, but this is an example from the console connected by ssh and sudo to root. There are arguments for using a bind entry in /etc/fstab instead of a symlink, but this works for me.

My normal procedure is to stop mysql, move the directory contents, link the original, and restart mysqld.

[tomh@workstation001 ~]$ sudo su -
[root@workstation001 ~]# 

[root@workstation001 ~]# service mysqld stop
Stopping mysqld (via systemctl):  
                                                           [  OK  ]

[root@workstation001 ~]# mv /var/lib/mysql/ /opt/

[root@workstation001 ~]# ln -s /opt/mysql /var/lib/


[root@workstation001 ~]# ls -la /var/lib/mysql
lrwxrwxrwx 1 root root 10 Feb 26 23:02 /var/lib/mysql -> /opt/mysql

[root@workstation001 ~]# service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]


[root@workstation001 ~]# mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.18-log MySQL Community Server (GPL)

mysql> show databases;
...
| Database              |
+-----------------------+
| information_schema    |
| mysql                 |
| performance_schema    |
| test   

I just noticed a similar question, of which this is close to a duplicate of, which mentions some issues of doing that above with selinux enabled; http://crashmag.net/change-the-default-mysql-data-directory-with-selinux-enabled

So if you have selinux there are some additional steps.

share|improve this answer
    
After follow these steps i try to start ten i got this error root@ns1 [/var/lib]# service mysqld start mysqld: unrecognized service When i replace mysqld by mysql then output is as follow root@ns1 [/var/lib]# service mysql start Starting MySQL. ERROR! Manager of pid-file quit without updating file. –  user111175 Feb 27 '12 at 8:02
    
What version of centos and mysql-server do you have and where did you get it? the base repos package for centos 5 and 6 call the service mysqld... –  Tom H Feb 27 '12 at 9:30
    
update your question with the output of ls -lh /etc/init.d and ls -lh /var/lib/mysql and ls -lh /var/lib/mysql –  Tom H Feb 27 '12 at 9:39

if you want to change mysql data directory, you should do edit the mysql config file and change datadir value.

datadir = /home/user/data

Your new data directory MUST be owned by mysql and has proper security context.

chown -R mysql.mysql /home/user/data
chcon -R -t mysqld_db_t /home/user/data

restart mysql server

/etc/init.d/mysqld restart
share|improve this answer

Stopping the Default Install/Instance

service mysqld stop

Clear Current Config

rm /etc/my.cnf

Uninstal the Default Install/Instance

yum remove mysql mysql-server -y

Clear Current Datadir

test -d /var/lib/mysql/ && rm -rf /var/lib/mysql/

Clear the 'New' Datadir

test -d /mysql/mysql/ && rm -rf /mysql/mysql/

Install it again

yum install mysql mysql-server -y

Check the service status

service mysqld status

Start it - just to create a first/default structure

service mysqld start

Check the service status

service mysqld status

Interrupt the current MySQL server installation

service mysqld stop

Ensure that you don´t have anymore instance/service running

ps axu | grep mysql

Move the mysql data directory to '/mysql' partition and create the symbolic link

test -d /var/lib/mysql/ && mv /var/lib/mysql/ /mysql/ && ln -s /mysql/mysql /var/lib/

Check symbolic link and the real path

ls -lrth /var/lib/ | grep mysql 

Set permission on new Datadir

chown -R mysql:mysql /mysql/mysql

Start it

service mysqld start

Try to connect (keep in mind that the default install of MySQL doesn´t set a 'pwd' for 'root' user and then you should connect with 'blank password'

mysql -u root -p --host 127.0.0.1

Once connected to MySQL, create a new db just to test if it´s working and where MySQL will create folder/file structure

create database DBTesteNew;
exit

Check if the new db is on the 'new datadir'

ls /mysql/mysql

Make sure the mysqld is set to start on boot time

chkconfig mysqld on

restart

reboot
share|improve this answer
    
Link - only answers are frowned upon. Please provide content here and link elsewhere for details if needed. –  EEAA Oct 4 at 2:12
    
Getting there....perhaps try another whack at formatting things properly? –  EEAA Oct 4 at 2:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.