Changing MySQL DB Data Directory Gracefully

Recently I have come across a an error stating "insufficient space to process...." error in mysql. The available solution is to change the default MySQL data directory. Due to the space unavailability in the root(/) partition some times we will come across such situation. By doing  a blind location change through the configuration file may cause issues.

The default MySQL directory is /var/lib/mysql .  few things need to be noticed before changing the MySQL directory. The MySQL data directory has been mapped to many of its configuration. So keen consideration need to be done before changing he default location to the costume one. Below are the steps need to be followed for changing the default location Gracefully.

Step1:
Switch user to root. You need root privilege to do this operation.

[anu@c3 ~]$ su -
Password: <ENTER ROOT PASSWORD>
[root@c3 ~]#

Step2:
Sop the mysql server. be sure no service is accessing mysql server during the migration process. Execute the following commands inn terminal.

[root@c3 ~]# service mysqld stop
Stopping mysqld:                        [  OK  ]
[root@c3 ~]#

Step3:
create new data directory for mysql and give proper ownership to mysql user.

[root@c3 ~]#mkdir /home/mysql
[root@c3 ~]#chown -R mysql.mysql /home/mysql
[root@c3 ~]#

Step4:
Move the entire data directory to new location(Here in my case there is enough  space in the /home. So I am taking the /home as the location for the mysql data directory). This step will consume some time, because the entire database has to be moved to the new location.

[root@c3 ~]#mv /var/lib/mysql/* /home/mysql
[root@c3 ~]#

Step5:
Remove the original mysql folder from the location /var/lib.

[root@c3 ~]#rm -rf /var/lib/mysql
[root@c3 ~]#

Step6:
Edit the mysql configuration file and map the new data location. In the mysqld part change the location fro default to new location. the details below make sense.

[root@c3 ~]# vim /etc/my.cnf

    [mysqld]
    #datadir=/var/lib/mysql
    #socket=/var/lib/mysql/mysql.sock
    #user=mysql
Change to
    datadir=/home/mysql
    socket=/home/mysql/mysql.sock
    user=mysql

Step7:
Create symbolic link to the original location so that the configuration  mismatch error can be avoided.

[root@c3 ~]#ln -s /home/mysql /var/lib
[root@c3 ~]# ls -la /var/lib/mysql
lrwxrwxrwx. 1 root root 12 Oct 21 20:09 /var/lib/mysql -> /home/mysql/
[root@c3 ~]#

Step7:
Let's Start mysql service

[root@c3 ~]# service mysqld start
Starting mysqld:                           [  OK  ]
[root@c3 ~]#

Step8:
Now, try to access the mysql shell

[root@c3 ~]# mysql -u root -p
Enter password: <ENTER MySQL PASSWORD>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

mysql> 

Step9:
Consider changing the tmpdir of mysql as well. You should edit your my.cnf

[root@c3 ~]# vim /etc/my.cnf
    [mysqld]    datadir=/home/mysql
    socket=/home/mysql/mysql.sock
    user=mysql
    tmpdir = /new/tmp/location

Step10:
 Cheers!..