Steps to move MySQL directory to new location on Linux

Move Mysql directory to another directory

Overview

Before we start with move mysql directory. Let's first understand why or when we need to move mysql directory from one location to another?
The database is getting grow every time and sometimes it will grow out of space. when database size increases out of memory then database service will stop working. It will result into website or application will be down. In such scenario, we need to provide or add extra storage or space to the database. when you come to such situation then the best solution is to move your database directory to other location where it gets the more storage to save the extra amount of data.

In such scenario, we need to provide or add extra storage or space to the database. when you come to such situation then the best solution is to move mysql directory to other location where it gets the more storage to save the extra amount of data.
This article will guide you to relocate or move mysql directory to another location.

By default MySQL databases stored under the "/var/lib/mysql" directory. This basically comes under root partition. We are not using much or big storage for the root partition.

In that situation, you may want to move or relocate mysql directory from root partition to some other partition.

To change or move mysql directory location you need to follow below simple steps. But before doing this task make sure you have taken backup of your complete database.

You need to perform tasks one-by-one

  • Backup of Mysql Database
  • Copy Mysql directory to another location
  •  Check Permission & Size of MySQL files
  •  Change directory in my.cnf file
  •  Restart MySQL service

This article demonstrates the details on how to perform above mentioned steps to change Mysql data directory to a different location.

1. Backup of Mysql Database

Whenever you are performing any task on production server make sure you have a backup of all the data.

Here we first take the backup of MySQL all available database in a single .sql file using mysqldump command.

mysqldump -u username -p --all-databases > alldatabase.sql

Read more about the useful MySQL commands from below link.

2. Copy MySQL directory to another directory

Once backup completed, copy mysql directory "/var/lib/mysql" to other location where you have enough space to store larger database. Here I have move "mysql" directory to /data directory.

Use below copy command,

cp -r /var/lib/mysql  /data  [It will take time to copy complete MySQL directory]

3. check permission & Size of directory

After copying the mysql directory to /data. check the /data/mysql directory and file permissions are exactly same as "/var/lib/mysql" directory.

$ ls -l /var/lib/mysql
-rw-rw---- 1 mysql mysql 15344861184 Aug 29 18:36 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Sep 14 12:16 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Sep 14 12:16 ib_logfile1
-drwx------ 2 mysql mysql 4096 Dec 31 2015 mysql
-srwxrwxrwx 1 mysql mysql 0 Sep 17 19:03 mysql.sock

$ du -sh /var/lib/mysql
- 20GB

$ ls -l /data/mysql
-rw-rw---- 1 mysql mysql 15344861184 Aug 29 18:36 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Sep 14 12:16 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Sep 14 12:16 ib_logfile1
-drwx------ 2 mysql mysql 4096 Dec 31 2015 mysql
-srwxrwxrwx 1 mysql mysql 0 Sep 17 19:03 mysql.sock

$ du -sh /data/mysql
- 20GB

4. Change directory path in my.cnf file.

my.cnf file is main configuration file of mysql. This file resides under "/etc/mysql" directory. Open "my.cnf" file and replace "datadir" path with newer mysql directory path.

vim /etc/mysql/my.cnf
datadir=/data/mysql

5. Restart Mysql Service

once the configuration changes done then restart or start|stop mysql service which will help to reflect the configuration changes.

$ /etc/init.d/mysql stop
$ /etc/init.d/mysql start

6. Test MySQL

After the restart, the MySQL service, check with creating a database in MySQL. You are new database will be created under the /data/mysql directory. It means mysql directory successfully moved to the new directory.

I hope you like the article if you find any difficulties in moving MySQL directory then please do comment your queries or problem via the comment section, till then stay tuned to techthings.org for more such valuable articles.

Latest Comments
  1. Ulf November 19, 2016
    • Prashant Thorat November 19, 2016

Leave a Reply

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