After installing MySQL database for a production server, we may want to change the default data directory of MySQL to a different directory. This is the case when such directory is expected to grow due to high usage. Otherwise, the filesystem where /var is stored may collapse at one point causing the entire system to fail. Another scenario where changing the default directory is when we have a dedicated network share that we want to use to store our actual data. MySQL uses /var/lib/mysql directory as default data directory for Linux based systems.

In order to change the default directory, we need to check the available storage. We can use the df command to discover drive space on Linux. The output of df -H will report how much space is used, available, the percentage used, and the mount point of every disk attached to your system.

We are going to assume that our new data directory is /mnt/mysql-data. It is important to note that this directory should be owned by mysql:mysql.

mkdir -p /home/mysql-data

For simplicity, I’ve divided the procedure into 4 simple steps.

Step 1: Identify Current MySQL Data Directory

To identify the current data directory use the following command.

mysql -u username -p -e “SELECT @@datadir”

We need to identify the current MySQL data directory as it can be changed in the past. Let’s assume the current data directory is /var/lib/mysql

Step 2: Copy MySQL Data Directory to the desired location

To avoid data corruption, stop the service if it is currently running before proceeding and check the status.

service mysqld stop
service mysqld status

Then copy recursively the contents of /var/lib/mysql to /mnt/mysql-datapreserving original permissions and timestamps:

cp -rap /var/lib/mysql/* /mnt/mysql-data

Change the permission of the directory as its owner should be mysql:mysql. We can use the following command to change the ownership of the directory:

chown -R mysql:mysql /mnt/mysql-data

Step 3: Configure the new MySQL Data Directory

Edit the MySQL default configuration file /etc/my.cnf and update values of mysqld and client.

# Change From:[mysqld]datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock# Change To:[mysqld]
datadir=/mnt/mysql-data/mysql
socket=/mnt/mysql-data/mysql/mysql.sock

If there is no client variable then add, or else, update it to:

[client]
port=3306
socket=/mnt/mysql-data/mysql.sock

Step 4: Enable the MySQL Service and confirm the directory change

Restart the MySQL service using the following command:

service mysqld start

Now, use the same command to verify the location change of the new data directory:

mysql -u username -p -e “SELECT @@datadir”

If you face any issue during MySQL startup check MySQL log file /var/log/mysqld.log for any errors.

That’s it. Hope this helps.