How to Change MySql Data Directory In Linux

How to Change Mysql Data Directory

Let’s say your second HDD is mounted as /home2

1. The first step is to stop MySQL so that all your data gets copied correctly.
$ /etc/rc.d/init.d/mysql stop
- or -
$ service mysql* stop

2. Create the new database directory in the second HDD and for this let’s say it’s named as mysqldata
$ mkdir /home2/mysqldata

3. Copy the database files from the first HDD to the second HDD
$ cp -R /var/lib/mysql/ /home2/mysqldata

4. Set the correct owner and group, permissions of the new database directory on the 2nd HDD
$ chown -R mysql.mysql /home2/mysqldata/

5. Rename your old database directory
$ mv /var/lib/mysql/ /var/lib/mysql_old

6. Create a symbolic link from the old database directory to the new one for any programs that rely on the default location
$ ln -s /home2/mysqldata/ /var/lib/mysql

7. Set the correct owner and group on the symbolic link
$ chown mysql.mysql /var/lib/mysql

8. Edit the configuration file (/etc/my.cnf) to update the changes
Comment out the old settings and add a line for the new one as you can see below
[mysqld]
#datadir=/var/lib/mysql
datadir=/home2/mysqldata
#socket=/var/lib/mysql/mysql.sock
socket=/home2/mysqldata/mysql.sock
#basedir=/var/lib
basedir=/home2
save my.cnf and exit your text editor
Note that for MySQL version 5 you have to remove the line basedir. The basedir line is only for those who are using MySQL version 4.

9. Restart MySQL
$ /etc/rc.d/init.d/mysql start
- or -
$ service mysql* start

10. If MySQL refuses to start look in /var/log/mysqld.log for the reason

What is Mysql Data Directory?

Mysql data directory is important part where all the mysql databases storage location.By default MySQL data default directory located in /var/lib/mysql.If you are running out of space in /var partition you need to move this to some other location.

Note:- This is only for advanced users and before moving default directory make a backup of your mysal databases.

Procedure to follow
Open the terminal
First you need to Stop MySQL using the following command

/etc/init.d/mysql stop

Now Copy the existing data directory (default located in /var/lib/mysql) using the following command

cp -R -p /var/lib/mysql /path/to/new/datadir

All you need are the data files, so delete the others with the command

rm /path/to/new/datadir

Note:- You will get a message about not being able to delete some directories, but that’s what you want.
Now edit the MySQL configuration file with the following command

Vim /etc/mysql/my.cnf

Look for the entry for “datadir”, and change the path (which should be “/var/lib/mysql”) to the new data directory.

you’ll never be able to restart MySQL with the new datadir location.
In the terminal, enter the command

vim /etc/apparmor.d/usr.sbin.mysqld

Copy the lines beginning with “/var/lib/mysql”, comment out the originals with hash marks (“#”), and paste the lines below the originals.
Now change “/var/lib/mysql” in the two new lines with “/path/to/new/datadir”. Save and close the file.
Restart the AppArmor profiles with the command

 /etc/init.d/apparmor reload

Restart MySQL with the command

 /etc/init.d/mysql restart

Now MySQL should start with no errors, and your data will be stored in the new data directory location.