Running MySQL on an external hard drive

I recently found a need (fine, a want) to put the Stack Overflow data dump in a MySQL database. The posts table is very, very big, and my poor closet-laptop-server just doesn’t have enough room. I rummaged around and found a nice 1.5TB external hard drive to use.

One should be able to just point MySQL to any place one wants, right? In my case, I want the database to be stored in /media/bigdrive, where the big drive resides. Simple, right?

No.

After much Googling, I found this on DBA.SE:

  1. Shutdown mysql.
  2. Move all the files in your current data directory to the new location (check out the location in step 3 – datadir parameter).
  3. Locate my.ini file (it is in the mysql installation directory). Change datadir parameter value to point to the new location.
  4. Start mysql.

One would think these are really easy instructions to follow, but I somehow managed to completely destroy my main installation with them. Don’t ask how. It was a dev server, though, so nothing really important was in it.

Here’s my hopefully-foolproof method for doing this:

What I started with:

  • A fresh MySQL installation
  • Ubuntu Server 12.04
  • An intense desire to make this work

How I did it:

I decided to try to follow the above instructions again. After all, they have to work…

It’s a good idea to sudo su your way into root, because typing sudo is a lot of work and all programmers are lazy.

Shutting down MySQL is easy:

# service mysql stop
mysql stop/waiting

Next, we have to find the location of all those data files, which was helpfully not specified in the answer. A quick Googling found this question (from someone trying to do exactly what I’m doing), which says that the data is at /var/lib/mysql. That path might be different on your setup, consult Google.

A quick ls of the directory shows that it looks right:

# ls /var/lib/mysql
debian-5.5.flag  ib_logfile0  mysql       performance_schema
ibdata1      ib_logfile1  mysql_upgrade_info  test

The database I have on the server is named test, so I bet that this is the directory that holds all the MySQLy goodness.

We’ll just use cp to move the files to the 1.5TB drive:

# cp -r /var/lib/mysql /media/bigdrive/

It looks like the files made it across:

root@server1:/media/bigdrive/mysql# ls
debian-5.5.flag  ib_logfile0  mysql               performance_schema
ibdata1          ib_logfile1  mysql_upgrade_info  test

Next up is finding that my.ini file. Again, Google is your friend. It looks like no mention of a my.ini file was found, and instead it’s all my.cnf. Let’s go along with it and see what happens. This page says that the file is located at /etc/mysql/my.cnf (again, your milage may vary.)

We’re supposed to edit the datadir parameter in the file to our new directory. Opening the file up in vim (or whatever your favorite editor is) shows this, around line 40:

port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql // <- line 40
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql

I edited the datadir parameter to this:

datadir         = /media/bigdrive/mysql

And saved the file. Now, we need to restart MySQL:

root@server1:/etc/mysql# service mysql start
start: Job failed to start

I kind of expected something bad to happen, of course. I remembered something about AppArmor getting in the way from my previous readings, so I Googled it and found this answer on Ubuntu.SE (excerpt here):

If you open the file /etc/apparmor.d/usr.sbin.mysqld you will among the rules find these two lines.

/var/lib/mysql/ r,
/var/lib/mysql/** rwk,

Assuming our example above, they will have to be replaced or (probably preferable) complemented by these two lines.

/home/data/mysql/ r,
/home/data/mysql/** rwk,

Before we can startup our MySQL server, with its new datadir, we will also have to explicitly reload our new apparmor profile.

$ sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

Following those instructions now:

# vim /etc/apparmor.d/usr.sbin.mysqld

For me, the lines were 32 & 33:

/var/lib/mysql/ r,
/var/lib/mysql/** rwk,

Which I edited to:

/media/bigdrive/mysql/ r,
/media/bigdrive/mysql/** rwk,

I saved the file and ran this command:

# apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

After which….

# service mysql start
mysql start/running, process 10600

I can create tables and databases and insert and delete and everything.

Mission accomplished.