Centos – How to change the MySQL database directory

centosMySQL

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?

Best Answer

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.

Related Topic