How to move MySQL directory to /home

By default all MySQL database on a server is saved in /var/lib/mysql directory. If you don’t have enough space left in /var directory or if its filling up quickly, you can move your MySQL directory to /home partition.

Assuming you have enough space in /home partition, follow below steps to move MySQL to a new location in /home/var_mysql directory.

Login in server as root via SSH and run following command:

mysqldump --all-databases | gzip > /home/alldatabases.sql.gz
/etc/init.d/mysql stop
mkdir /home/var_mysql
mv /var/lib/mysql /home/var_mysql
chown -R mysql:mysql /home/var_mysql/mysql
ln -s /home/var_mysql/mysql /var/lib/mysql
/etc/init.d/mysql start

Done!!!
You should now have your MySQL at a new location /home/var_mysql

Explanation of above commands

mysqldump --all-databases | gzip > /home/alldatabases.sql.gz

This will create a full backup of all MySQL database. If anything goes wrong, you can restore all MySQL database using this backup file. Name of this file will be alldatabases.sql.gz and it will be saved in /home directory.

/etc/init.d/mysql stop

This will stop mySQL.

mkdir /home/var_mysql

This will create a new directory var_mysql in /home

mv /var/lib/mysql /home/var_mysql

This will move /var/lib/mysql to /home/var_mysql

chown -R mysql:mysql /home/var_mysql/mysql

This will give ownership of /home/var_mysql/mysql directory to user mysql.

ln -s /home/var_mysql/mysql /var/lib/mysql

This will symlinking the old /var/lib/mysql to the new location.

/etc/init.d/mysql start

This will start the MySQL.

What if anything goes wrong

We have create a full MySQL backup in our first step. If anything goes wrong, you will always have the full mysql backup to restore all database. Please refer to below tutorial to restore your databases using the full MySQL backup file.
http://www.lophost.com/tutorials/ssh/how-to-create-and-restore-mysql-backup-of-all-accounts/

Our Recommended Dedicated Server

Leave us a Reply

Your email address will not be published. Required fields are marked *

* *