Database as Symbolic Link - What am I missing?

Hi,

I've done this before so I don't see what I could doing wrong here.
I'm running mysql 5x on freebsd. I'm using the default data directory
of "/var/db/mysql"

In there I have several dozen mysql datasbases that are functioning
perfectly. I am trying to add a database which will be stored on a
different drive, therefore the entry in "/var/db/mysql" should be a
symbolic link

With mysql running I go...

mkdir /sata2/database/d1
chmod 700 /sata2/database/d1
chown mysql:mysql /sata2/database/d1


Now all I have to do is make a symbolic link and I should be set
ln -s /sata2/database/d1 /var/db/mysql


Change the owner of the symbolic link
chown -h mysql:mysql /var/db/mysql/d1

Doing a directory listing, I see:
lrwxr-xr-x 1 mysql mysql 25 Jun 19 11:24 d1 -> /sata2/database/s1

So the symbolic is there and looks to have permissions good. I restart
mysql. Mysql does not show the database "d1". It's as if the database
isn't there.

Just for testing, I tried making a directory "/var/db/mysql/d2" via
command line. OF course, since that is and actual directory, when
restarting mysql, mysql see its.

Re: Database as Symbolic Link - What am I missing?

amygrant1701@gmail.com wrote:
> Hi,
>
> I've done this before so I don't see what I could doing wrong here.
> I'm running mysql 5x on freebsd. I'm using the default data directory
> of "/var/db/mysql"
>
> In there I have several dozen mysql datasbases that are functioning
> perfectly. I am trying to add a database which will be stored on a
> different drive, therefore the entry in "/var/db/mysql" should be a
> symbolic link
>
> With mysql running I go...
>
> mkdir /sata2/database/d1
> chmod 700 /sata2/database/d1
> chown mysql:mysql /sata2/database/d1
>
>
> Now all I have to do is make a symbolic link and I should be set
> ln -s /sata2/database/d1 /var/db/mysql
>
>
> Change the owner of the symbolic link
> chown -h mysql:mysql /var/db/mysql/d1
>
> Doing a directory listing, I see:
> lrwxr-xr-x 1 mysql mysql 25 Jun 19 11:24 d1 -> /sata2/database/s1
>
> So the symbolic is there and looks to have permissions good. I restart
> mysql. Mysql does not show the database "d1". It's as if the database
> isn't there.
>
> Just for testing, I tried making a directory "/var/db/mysql/d2" via
> command line. OF course, since that is and actual directory, when
> restarting mysql, mysql see its.
>


You should not symlink tables on systems that do not have a fully
operational realpath() call. (Linux and Solaris support realpath()). You
can check whether your system supports symbolic links by issuing a SHOW
VARIABLES LIKE 'have_symlink' statement.
Symlinks are fully supported only for MyISAM tables. For files used by
tables for other storage engines, you may get strange problems if you
try to use symbolic links.

In the data directory, you always have the table format (.frm) file, the
data (.MYD) file, and the index (.MYI) file. The data file and index
file can be moved elsewhere and replaced in the data directory by
symlinks. The format file cannot.

You can symlink the data file and the index file independently to
different directories.

The .frm file must never be a symbolic link (as indicated previously,
only the data and index files can be symbolic links). Attempting to do
this (for example, to make synonyms) produces incorrect results. Suppose
that you have a database db1 under the MySQL data directory, a table
tbl1 in this database, and in the db1 directory you make a symlink tbl2
that points to tbl1:

shell> cd /path/to/datadir/db1
shell> ln -s tbl1.frm tbl2.frm
shell> ln -s tbl1.MYD tbl2.MYD
shell> ln -s tbl1.MYI tbl2.MYI