Moving MySQL database

[Matthew Seaman]

On Fri, Sep 03, 2004 at 09:42:36AM -0400, Bart Silverstrim wrote:
> I have a server that is rapidly filling the var partition with a MySQL 
> database.  I'd like to move it to a subdirectory somewhere under /usr.
> 
> Is there a document that would outline a "best practices" approach to 
> doing this?  My first instinct was to stop the mysqld, do a mv on 
> /var/db to /var/db2 to rename it, copy the data to a /usr/local/db 
> folder and alter permissions on it to match /var/db, then make a 
> softlink between /usr/local/db and /var/db and restart mysqld so mysqld 
> wouldn't need any reconfiguring and everything, I would *think*, should 
> keep working...only now it will be working off a far more spacious 
> partition.

I'd do it this way:

    i) Stop mysql

   ii) Remove (pkg_delete) the mysql-server package

  iii) Move the database files to their new location, taking care to
       preserve ownership, permissions, timestamps etc.

   iv) Reinstall the mysql-server port setting a different DB_DIR on
       the make command line:

        # make DB_DIR=/usr/local/db install

    v) Make sure that the home directory of the mysql user account is
       the same as you set DB_DIR to.  The port should take care of
       that, but it won't hurt to double check.

        % grep mysql /etc/passwd  
        mysql:*:88:88:MySQL Daemon:/usr/local/db:/sbin/nologin

   vi) Restart the mysql server.  Verify that your data survived the
       move.

And that's it: you're done.

	Cheers,

	Matthew

-- 
Dr Matthew J Seaman MA, D.Phil.                       26 The Paddocks
                                                      Savill Way
PGP: http://www.infracaninophile.co.uk/pgpkey         Marlow
Tel: +44 1628 476614                                  Bucks., SL7 1TH UK
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 187 bytes
Desc: not available
Url : http://lists.freebsd.org/pipermail/freebsd-questions/attachments/20040903/4b07e779/attachment.bin