Read MySQL data from an external drive

Photo by Art Wall - Kittenprint on Unsplash

Of recent, I faced a challenge to read a MySql data from an external disk drive and because I couldn’t find a resource which could guide step by step I found it necessary to spare some time to write it here after spending hours to figure it out.

I can imagine, one would be thinking that I should have simply imported the data into my existing database server on my machine!!; To keep my story short this would not be possible because the data was over 6TB and yet my computer storage was under 500GB.

The steps in this guide are for one using Debian Linux distribution, therefore I can’t guarantee that replicating these steps for other distribution will give you the desired outcome.

Ubuntu can natively access to a NTFS partition. However, you may not be able to set permissions on it using ‘chmod’ or ‘chown’. we need to unmount the drive so that we can remount it with permissions.

Identify the drive/partition

To identify the partition, use the ‘blkid’ command:

$ sudo blkid

Locate your drive from the displayed list, in my case its /dev/sdc2

Unmount the drive/partition

As I said earlier we need to unmount and remount it so that we can control the data with chown & chmod

$ sudo umount /dev/sdc2

Mount the drive/partition

First, create a mount point in a terminal using ‘mkdir’. Then, type the following line to mount the partition with options ‘permissions’:

$ sudo mkdir /media/disk_data
$ sudo mount /dev/sdc2 -t ntfs-3g -o permissions /media/disk_data
$ sudo mount /media/diska_data

Allow data access to /media/disk_data

Edit the apparmor config file with the new path to allow data access

$ sudo nano /etc/apparmor.d/usr.sbin.mysqld

Below the # Allow data dir access add the directory path

# Allow data dir access
/media/disk_data r,
/media/disk_data/** rw,

Now run $ sudo systemctl restart apparmor to restart the apparmor service.

Update the data source for Mysql

Now we need to update the datadir attribute for MySQL server to locate where data is located.

#Locate the mysqld.cnf in /etc/mysql/mysql.conf.d and update datadir
datadir =  /media/disk_data

Assign data ownership to MySQL

Run $ sudo chown mysql:mysql /media/disk_data

Now run $ sudo systemctl restart mysql.service to restart the MySQL service. In case your MySQL server fails to restart run$ journalctl -xe to get the details of why it's failing and so you can be able to resolve.

Otherwise, if your MySQL server has restarted successfully. Congratulations, you are now able to access your database from the external drive.