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
