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
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 stopNow Copy the existing data directory (default located in /var/lib/mysql) using the following command
cp -R -p /var/lib/mysql /path/to/new/datadirAll you need are the data files, so delete the others with the command
rm /path/to/new/datadirNote:- 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.cnfLook 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.mysqldCopy 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 reloadRestart MySQL with the command
/etc/init.d/mysql restartNow MySQL should start with no errors, and your data will be stored in the new data directory location.