"Marc S. Bragg" wrote:
> How does one redirect mysql to a data directory location different than
> the default. . . . and if possible, only for a particular database.
>
>
You can find the 'official' notes on how to relocate tables or entire
databases at:
http://www.mysql.com/doc/S/y/Symbolic_links.html
But, here's my few notes on it....
Since MySQL 'presents' actual directories as "databases" and the files that
make up each Isam (or MyIsam) structure as a 'table', you can 'fool' MySQL as
to their name or location via "symbolic links" (assuming you're using a
Unix/Linux setup, though I think there's a method for Windoze systems as
well. see: http://www.mysql.com/doc/W/i/Windows_symbolic_links.html ).
You can have your entire MySQL data 'instance' (including the "mysql" control
tables and you're own databases) in another location by using a 'symbolic'
link to point at the real physical location of the "var" subdirectory, or
have each of your own databases located on different devices, or individual
files (tables).
THOUGH, as of version 3.23.35 (and especially 3.23.36, but not tested by
myself for version 3.23.37 yet) I can't seem to have the 'index' portion
(*.MYI) of the files (tables) located (via symbolic links) somewhere different
than the 'data' portion (*.MYD). If I attempt to do this (so that accesses
to the index and data portions can occur simultaneously from separate disk
devices) I get the error "cannot access record info for table 'blah blah
blah'".
My setup looks like this (I removed some of the stuff 'ls' displays, like the
date/time of each file/directory):
ls -l /usr/local/mysql/var
drwxrws--- mysql mysql mysql
lrwxrwxrwx mysql mysql my_database ->
/array/mysqldata/my_database
lrwxrwxrwx mysql mysql schmengies_stuff ->
/dev/mysqldata/crappy_tables
drwxrws--- mysql mysql production
Where the 'control' database "mysql" is within the normal mysql installation
directory 'tree', but the user databases "my_database" and "schmengies_stuff"
are in different locations (the more important "my_database" is on a drive
'array' for performance, while "schmengies_stuff" is just in another
directory which is actually named "crappy_tables".
The database "production" appears to reside in the normal "mysql" directory
'tree', but the file contents of the directory look like this:
lrwxrwxrwx mysql mysql yubby.myd ->
/array/mysqldata/production/yubby.myd
lrwxrwxrwx mysql mysql yubby.myi ->
/array/mysqlindex/production/yubby.myi
lrwxrwxrwx mysql mysql dubby.myd ->
/array/mysqldata/production/dubby.myd
lrwxrwxrwx mysql mysql dubby.myi ->
/array/mysqlindex/production/dubby.myi
As you can see, the data portions of the tables "yubby" and "dubby" are in a
different location than the indexes... (though again, this hasn't worked for
me since upgrading above version 3.23.33)
P.S. Be sure to read the official docs on the effects of 'dropping' and
'creating' databases and tables located via symbolic links...
