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?
After much Googling, I found this on DBA.SE:
- Shutdown mysql.
- Move all the files in your current data directory to the new location (check out the location in step 3 – datadir parameter).
- Locate my.ini file (it is in the mysql installation directory). Change datadir parameter value to point to the new location.
- 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.
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
# service mysql start mysql start/running, process 10600
I can create tables and databases and insert and delete and everything.