how to change mysql data directory

Question

I use FreeBSD 8.2, mysql 5.1.58. I want change mysql default directory. I config datadir= /path OR my-data-dir= /path on my.cnf but not done. Note: i permission for /path is 755 and owner mysql:mysql. By another way, i set in /etc/rc.conf but not done

This is error, when restart mysql services.

usr/local/etc/rc.d/mysql-server restart mysql not running? (check /var/db/mysql/bsd1.abc.com.pid).

Help me.

Answer 1

I assume your current MySQL data directory is the default /var/db/mysql.

1. Stop MySQL (service mysql-server stop)
2. Change the datadir in rc.conf (mysql_dbdir="/another/directory")
3. mv /var/db/mysql /another/directory
4. Start MySQL again (service mysql-server start)

If there are still problems, ensure that the directory itself and all directories inside belong to user and group mysql. (chown -R mysql:mysql /another/directory; chmod 600 /another/directory)

Answer 2

another way is:

move and 'ln -s' /var/db/mysql somewhere.

Answer 3

Stop MySQL

service mysql stop

Make new directory for MySQL data

mkdir /new/dir/for/mysql

Set ownership of new directory to match existing one

chown --reference=/var/lib/mysql /new/dir/for/mysql

Set permissions on new directory to match existing one

chmod --reference=/var/lib/mysql /new/dir/for/mysql

Copy all files in default directory, to new one, retaining perms (-p)

cp -rp /var/lib/mysql/* /new/dir/for/mysql/

Edit the /etc/my.cnf file, and under [mysqld] add this line:

datadir=/new/dir/for/mysql/

Start MySQL

service mysql start