List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:November 25 2009 4:58pm
Subject:Re: question regarding mysql database location
View as plain text  
On Wed, Nov 25, 2009 at 3:42 PM, Manasi Save <
manasi.save@stripped> wrote:

> Dear Johan,
>
> Need your help again in understanding How mysql reads symlink.
>
> As you said below, I have created symlinks in default mysql directory.
> and try to read that symlink file as a database. But mysql is not reading
> that file as Database. Is there any settings which I need to change.
>

Make sure the directory the symlink points to has the same owner, group and
permissions as your other databases. The permissions of the symlink itself
are irrelevant (at least, on a Linux system. YMMV for other *nixen).

*mytest1:~# cd /var/lib/mysql
mytest1:/var/lib/mysql# ls -lh*
total 117M
-rw-r--r-- 1 root  root     0 2009-11-19 12:08 debian-5.0.flag
-rw-rw---- 1 mysql mysql 106M 2009-11-25 11:53 ibdata1
-rw-rw---- 1 mysql mysql 5.0M 2009-11-25 11:53 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M 2009-11-25 11:53 ib_logfile1
drwxr-xr-x 2 mysql root  4.0K 2009-11-24 10:27 mysql
-rw------- 1 root  root     7 2009-11-19 12:08 mysql_upgrade_info
drwx------ 2 mysql mysql 4.0K 2009-11-24 10:43 tmp
*mytest1:/var/lib/mysql# mysql*
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 690
Server version: 5.0.51a-24+lenny2 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

*mysql> show databases;*
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| tmp                |
+--------------------+
10 rows in set (0.13 sec)

mysql> Bye
*mytest1:/var/lib/mysql# mkdir /tmp/remotedatabase
mytest1:/var/lib/mysql# chown mysql:mysql /tmp/remotedatabase
mytest1:/var/lib/mysql# chmod u+rwX /tmp/remotedatabase
mytest1:/var/lib/mysql# ln -s /tmp/remotedatabase ./
mytest1:/var/lib/mysql# mysql*
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 691
Server version: 5.0.51a-24+lenny2 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

*mysql> show databases;*
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| remotedatabase     |
| tmp                |
+--------------------+
11 rows in set (0.01 sec)

*mysql> use remotedatabase;*
Database changed
*mysql> create table a (a int);*
Query OK, 0 rows affected (0.04 sec)

*mysql> show tables;*
+--------------------------+
| Tables_in_remotedatabase |
+--------------------------+
| a                        |
+--------------------------+
1 row in set (0.01 sec)

mysql> Bye
mytest1:/var/lib/mysql#





>
> Thanks in advance.
>
> --
> Regards,
> Manasi Save
> Artificial Machines Pvt Ltd.
>
> > On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save <
> > manasi.save@stripped> wrote:
> >
> >> Hi Johan,
> >>
> >> I am Sorry. If I have complicated the senerio But, this still not fix my
> >> purpose.
> >>
> >> What I want is - From your example :-
> >>
> >> /data/disk1/mysql/db1 (directory)
> >>                 /db2 (directory)
> >>                 /db3 (directory)
> >>                 /db4 (symlink to /data/disk2/mysql/db4)
> >>                 /db5 (symlink to /data/disk2/mysql/db5)
> >>                 /db6 (symlink to /data/disk2/mysql/db6)
> >>
> >> I dont want to create these directories here (/data/disk1/mysql/d4
> >> /d5
> >> /d6).
> >
> >
> > They're not directories, they're symlinks, which are (to the OS) a kind
> of
> > file, and thus not limited to 32000 per directory. They behave mostly
> > identical to a directory, though, so MySQL will pick them up seamlessly,
> > with the one hitch that you'll have to replace "create database"
> > statements
> > by mkdir and ln calls on the OS level.
> >
> > This is afaik the only way to do this on the MySQL level. It is
> impossible
> > to specify multiple base directories.
> >
> > Another possible option, but higher in complexity and most likely less
> > performant, would be to run two instances of MySQL on different ports
> with
> > different data directories, and use MySQL Proxy to redirect incoming
> > connections based on whatever criterion you could script into it - "use
> > database" statements, for example. This is however going to come with
> it's
> > very own set of catches and limitations.
> >
> > I'm not big on proxy, myself, so I'm afraid if the symlink option is not
> > acceptable to you, I can't help you any further.
> >
>
>
>

Thread
question regarding mysql database locationManasi Save25 Nov
  • Re: question regarding mysql database locationWaynn Lue25 Nov
    • Re: question regarding mysql database locationManasi Save25 Nov
      • Re: question regarding mysql database locationWaynn Lue25 Nov
        • Re: question regarding mysql database locationManasi Save25 Nov
          • Re: question regarding mysql database locationJohan De Meersman25 Nov
            • Re: question regarding mysql database locationManasi Save25 Nov
              • Re: question regarding mysql database locationJohan De Meersman25 Nov
                • Re: question regarding mysql database locationManasi Save25 Nov
                • Re: question regarding mysql database locationManasi Save25 Nov
                  • Re: question regarding mysql database locationJohan De Meersman25 Nov
                    • Re: question regarding mysql database locationManasi Save27 Nov
                  • Re: question regarding mysql database locationnitin mehta26 Nov
                    • Re: question regarding mysql database locationManasi Save27 Nov
  • Re: question regarding mysql database locationKrishna Chandra Prajapati25 Nov
    • Re: question regarding mysql database locationJohan De Meersman25 Nov
Re: question regarding mysql database locationManasi Save25 Nov