How to change the mysql database location

Identify the database files you wish to migrate into the new data directory

You can use the following command to list the current databases in mysql

mysqlshow -u root -p

Keep this list available as you will reference it in a later step

Shutdown the MySQL database, if it is running mysqladmin -u root -p shutdown

Locate the MySQL configuration file. This is usually located in the directory $OSS_HOME/var/mysql

Note: You should make a backup copy of each file you're editing before modifying the contents

The property you need to change is named datadir. The default value is $OSS_HOME/var/mysql, which places the database files in the same directory as the log files and other runtime generated files. This property is usually located in the [mysqld_safe] section in the configuration file

You need to change:

[mysqld_safe]

datadir = /opt/oss/var/mysql

to

[mysqld_safe]

datadir = /opt/oss/var/mysql/data

Save these changes

Create the directory you specified in the datadir property mkdir -p /optoss/var/mysql/data

Check to make sure the user assigned to execute mysql has read/write privileges on this directory

You may need to modify the directory settings using chown and chmod

Move the databases listed in the first step to the new data directory mv test /opt/oss/var/mysql/data

Note: You can use the copy (cp) command instead if you prefer. Remember to remove the copied files once the migration is complete

You will need to move/copy each database into the new data directory

After all the databases have been migrated to the new data directory, you should start MySQL openpkg rc mysql start

The database files will now be managed under the new data directory. If you encounter any problems during startup, you check the hostname.err file located in the data directory.

以上文章載自http://developer.spikesource.com/wiki/index.php/How_to_change_the_mysql_database_location