How I moved my MySQL database on FreeBSD

This page details how I moved my MySQL database data directory from /var/db/mysql to /usr/local/var/db/mysql on a FreeBSD operating system.

I have a database server running in a FreeBSD jail called (confusingly) mysql, and one of the databases is very large. I made a dump using

mysqldump -u nobita -p -h mysql nobita2 > nobita2-dump

Here the user name (-u) is nobita and the database name is nobita2, because "nobita" is my code name for my handwriting recognition project, and the host name (-h) is mysql. The size of the SQL dump is 2,558,344,226 bytes:

$ ll nobita2-dump 
-rw-r--r--  1 ben  ben  2558344226 Nov 12 09:13 nobita2-dump

This file contains every little thing anyone has drawn on that 300x300 square at the above web site since 14 November 2008, so it's a fairly large file.

I need to do some work on the database and want to make a back up before doing the work, in case something goes wrong. In order to test the restore part of the backup, I tried to insert the above huge database file into the non-jail database server. Unfortunately it hung up. I wondered why it hung up, and then found that the filesystem on /var was full.

So I needed to move the MySQL database directory away from the /var partition. I googled for some advice, and from what I saw on the web, it looked like it would be possible to just move all the files under /var/db/mysql to the new directory and restart the database server, and everything would be OK. (But you can't believe everything you read, can you?)

The first thing I did was to edit /etc/rc.conf (manual page: rc.conf) to

mysql_dbdir="/usr/local/var/db/mysql"

Then I tried to stop the mysql server with

/usr/local/etc/rc.d/mysql-server stop

However, this didn't work; it complained that a file called /usr/local/var/db/mysql/mysql.pid didn't exist. So I removed the above line from /etc/rc.conf and tried again. This time the server stopped OK. Then I re-did the above edit of /etc/rc.conf. Then I made a directory, and changed the owner of the directory

mkdir -p /usr/local/var/db/mysql/
chown mysql !$
chgrp mysql !$

Then I copied all the files from the /var/db/mysql to the new directory:

cp -R /var/db/mysql/ /usr/local/var/db/mysql/

Then I started the database server again:

/usr/local/etc/rc.d/mysql-server start

Unfortunately this hung for a second or two and then stopped. There was nothing in the error log! Oh yes, I forgot one vital stage:

cd /usr/local/var/db/mysql/
chown -R mysql *

Without doing that, all the files that I'd copied with cp -R were still owned by "root", so poor old mysql didn't even have permission to write in its own error log, and so there was nothing it could do except stop.

Now I tried to connect to the database with the usual command:

/usr/local/etc/rc.d/mysql-server start
mysql -u root -p

I connected and found that everything seemed to be in perfect working order. So as far as I can tell, it is OK to use cp -R to copy all the database files. However, I'm not an expert on MySQL, so please don't rely just on the above story.