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-dumpHere 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-dumpThis 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 stopHowever, 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 startUnfortunately 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 -pI 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.