MySQL tables on external hard drive

Question

I have a large amount of text data I need to import into MySQL. I'm doing this on a MacBook and don't have enough space for it so I want to store it in an external hard drive (I'm not really concerned about speed at this point - this is just for testing).

What's the best way to do it?

Install MySQL on the external hard drive (is this possible on a Mac?)
Install MySQL on the laptop's hard drive and have the tables on the external (how?)

score 5 · Accepted Answer · answered May 27 '11 at 10:10

5

One simple hack is to create an symbolic link replacing your current mysql database file location pointing to the external disk. Google symbolic link.

sample usage would be after you shutdown mysql, change the old mysql db folder name to something else, and create the symbolic link using the ln command like below

ln -s [EXTERNAL DRIVE PATH] [MYSQL DB FOLDER PATH]

Then move all the previous content of the mysql db folder to the new location.

answered May 27 '11 at 10:10

user658991

566
3
7

This is half the procedure. You will need to add the following lines to "/etc/apparmor.d/usr.sbin.mysqld"\n /external/drive/path/ r, /external/drive/path/** rwk,\n else you get:\n 121210 8:36:18 [Warning] Can't create test file /external/drive/path/hostname.lower-test 121210 8:36:18 [Warning] Can't create test file /external/drive/path/hostname.lower-test mysqld: Can't change dir to '/external/drive/path/' (Errcode: 13) 121210 8:36:18 [ERROR] Aborting – tver3305 Dec 10 '12 at 06:02

score 4 · Answer 2 · answered Mar 17 '19 at 12:58

Open /etc/mysql/my.cnf and find the value of the datadir. Alternatively, you can find this out in the mysql monitor with

mysql>   select @@datadir;

Stop mysql

sudo systemctl stop mysql

Copy the data from there to your external drive

sudo rsync -av /var/lib/mysql /mnt/myHDD/somedir/mysql

Modify the location of the datadir in my.cnf.
Start mysql again

sudo systemctl start mysql

Verify that everything is still fine and remove the original data dir.

This page contains a more extensive guide but all the additional issues it warns about were not relevant for me on my raspberry PI. I.e. I skipped them and it worked.

score 1 · Answer 3 · answered Dec 10 '12 at 07:46

1

User user658991 answer is halfway there.

After adding the soft link, you will need to add the following line to /etc/apparmor.d/usr.sbin.mysqld beneath the 2 lines to the old mysql folder.

/path/to/mysql/folder/on/the/external/ r
/path/to/mysql/folder/on/the/external/ ** rwk

Without these 2 lines, MySQL fails to start complaining of:

Can't create test file /path/to/mysql/folder/on/the/external/hostname.lower-test
Can't create test file /path/to/mysql/folder/on/the/external/hostname.lower-test
mysqld: Can't change dir to '/path/to/mysql/folder/on/the/external/' (Errcode: 13)

Restart apparmor for the changes to take effect.

sudo invoke-rc.d apparmor restart

With this, MySQL starts normally.

answered Dec 10 '12 at 07:46

tver3305

8,224
2
21
23

Can this be done while maintaining all my databases on the HD and adding a new one on and external HD? – awm Jul 25 '13 at 16:56
@aresnelupin yes, all your databases will be maintained. – tver3305 Sep 07 '13 at 12:41
This was not required for me with MySQL Version 5.5.60-0+deb8u1. – Private_GER Jul 01 '18 at 14:32

score 1 · Answer 4 · answered May 27 '11 at 10:07

1

For the second option, a tablespace might do the trick:

http://dev.mysql.com/doc/refman/5.1/en/create-tablespace.html

answered May 27 '11 at 10:07

Denis de Bernardy

75,850
13
131
154

MySQL tables on external hard drive

4 Answers4

Linked