How to change the MySQL database location

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

2) Shutdown the MySQL database, if it is running

mysqladmin -u root -p shutdown

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

4) Save these changes

5) Create the directory you specified in the datadir property

mkdir -p /optoss/var/mysql/data

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

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

8) 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.