Moving MySQL database

[Peter Risdon]

Matthew Seaman wrote:
> 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.


Might a cp -p be slightly more cautious/paranoid until step vi has been 
completed?

Peter.

> 
>    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
>