question regarding mysql database location

Hi All,

I have asked this question before But, I think I am not able to describe
it better.

Sorry for asking it again.
I have multiple databases but there is a limit on the folders getting
created in one folder.

I have mysql default directory set as /var/lib/mysql/data.
Now, After 32000 folder creation I am not able to create more folders than
that. Well Its not like I want to create 32000 database's in it (Which I
wanted to earlier :-P).

for example - I want to create 10 databases but 5 in
/var/lib/mysql/data/d1 to d5
and othe 5 in /var/lib/mysql/data/d6 to d10.

but I want to access all the databases that is d1-d10.

as I ca change the database location after 5 databases but not able to
access old five which I have created in old location.


Please let me know if anymore information is needed on this. I am really
looking for the solution. Please Help me.
--
Thanks and Regards,
Manasi Save
Artificial Machines Pvt Ltd.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question regarding mysql database location

Also I forgot to mention that I have gone through the innodb option of
innodb_data_file_path but I can just specify it as :

innodb_data_file_path=ibdata1:2048M:autoextend:max:1024M;ibd ata1:2048M:autoextend:max:1024M;

But not as :

innodb_data_file_path=/var/lib/mysql/data/ibdata1:2048M:auto extend:max:1024M;/var/lib/mysql/data1/ibdata1:2048M:autoexte nd:max:1024M;

Is there any wayout for this?

Thanks and Regards,
Manasi Save
Artificial Machines Pvt Ltd.

> Hi All,
>
> I have asked this question before But, I think I am not able to describe
> it better.
>
> Sorry for asking it again.
> I have multiple databases but there is a limit on the folders getting
> created in one folder.
>
> I have mysql default directory set as /var/lib/mysql/data.
> Now, After 32000 folder creation I am not able to create more folders than
> that. Well Its not like I want to create 32000 database's in it (Which I
> wanted to earlier :-P).
>
> for example - I want to create 10 databases but 5 in
> /var/lib/mysql/data/d1 to d5
> and othe 5 in /var/lib/mysql/data/d6 to d10.
>
> but I want to access all the databases that is d1-d10.
>
> as I ca change the database location after 5 databases but not able to
> access old five which I have created in old location.
>
>
> Please let me know if anymore information is needed on this. I am really
> looking for the solution. Please Help me.
> --
> Thanks and Regards,
> Manasi Save
> Artificial Machines Pvt Ltd.
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=manasi.save@artificialmac hines.com
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question regarding mysql database location

I fixed this by using symlinks for the directories for the underlying
databases. The limit for files is significantly higher than
directories.

Waynn

On 11/24/09, Manasi Save wrote:
> Hi All,
>
> I have asked this question before But, I think I am not able to describe
> it better.
>
> Sorry for asking it again.
> I have multiple databases but there is a limit on the folders getting
> created in one folder.
>
> I have mysql default directory set as /var/lib/mysql/data.
> Now, After 32000 folder creation I am not able to create more folders than
> that. Well Its not like I want to create 32000 database's in it (Which I
> wanted to earlier :-P).
>
> for example - I want to create 10 databases but 5 in
> /var/lib/mysql/data/d1 to d5
> and othe 5 in /var/lib/mysql/data/d6 to d10.
>
> but I want to access all the databases that is d1-d10.
>
> as I ca change the database location after 5 databases but not able to
> access old five which I have created in old location.
>
>
> Please let me know if anymore information is needed on this. I am really
> looking for the solution. Please Help me.
> --
> Thanks and Regards,
> Manasi Save
> Artificial Machines Pvt Ltd.
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=waynnlue@gmail.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question regarding mysql database location

Thanks Waynn,

I could not get your point of using symlinks. Because as per my knowledge
symlink will store same data which is there in original directory.
and What do you mean by "The limit for files is significantly higher than
> directories."

Can you elaborate it more.

Thanks in advance.

Regards,
Manasi Save
Artificial Machines Pvt Ltd.

> I fixed this by using symlinks for the directories for the underlying
> databases. The limit for files is significantly higher than
> directories.
>
> Waynn
>
> On 11/24/09, Manasi Save wrote:
>> Hi All,
>>
>> I have asked this question before But, I think I am not able to describe
>> it better.
>>
>> Sorry for asking it again.
>> I have multiple databases but there is a limit on the folders getting
>> created in one folder.
>>
>> I have mysql default directory set as /var/lib/mysql/data.
>> Now, After 32000 folder creation I am not able to create more folders
>> than
>> that. Well Its not like I want to create 32000 database's in it (Which I
>> wanted to earlier :-P).
>>
>> for example - I want to create 10 databases but 5 in
>> /var/lib/mysql/data/d1 to d5
>> and othe 5 in /var/lib/mysql/data/d6 to d10.
>>
>> but I want to access all the databases that is d1-d10.
>>
>> as I ca change the database location after 5 databases but not able to
>> access old five which I have created in old location.
>>
>>
>> Please let me know if anymore information is needed on this. I am really
>> looking for the solution. Please Help me.
>> --
>> Thanks and Regards,
>> Manasi Save
>> Artificial Machines Pvt Ltd.
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=waynnlue@gmail.com
>>
>>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question regarding mysql database location

--00151761caeeb7377b04792e422d
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Nov 25, 2009 at 12:53 AM, Manasi Save <
manasi.save@artificialmachines.com> wrote:

> Thanks Waynn,
>
> I could not get your point of using symlinks. Because as per my knowledge
> symlink will store same data which is there in original directory.
> and What do you mean by "The limit for files is significantly higher than
> > directories."
>
> Can you elaborate it more.
>
> Thanks in advance.
>
>
So assuming /var/lib/mysql/data/ is your mysql data directory, you could
create a new directory called /var/lib/mysql/data/data1, then move all the
directories from /var/lib/mysql/data/* into data1. Then you could create a
symlink in /var/lib/mysql/data/ pointing to /var/lib/mysql/data/data1/ name>. When mysql tries to load the data directory, it follows the symlink
to the underlying directory (in /var/lib/mysql/data/data1).

--00151761caeeb7377b04792e422d--

Re: question regarding mysql database location

Well Waynn,

In this case I need to move all the existing databases to new location
right. Which I don't want to do. Is it possible that I create sym link
between two and use both.
--
Thanks and Regards,
Manasi Save
Artificial Machines Pvt Ltd.

> On Wed, Nov 25, 2009 at 12:53 AM, Manasi Save <
> manasi.save@artificialmachines.com> wrote:
>
>> Thanks Waynn,
>>
>> I could not get your point of using symlinks. Because as per my
>> knowledge
>> symlink will store same data which is there in original directory.
>> and What do you mean by "The limit for files is significantly higher
>> than
>> > directories."
>>
>> Can you elaborate it more.
>>
>> Thanks in advance.
>>
>>
> So assuming /var/lib/mysql/data/ is your mysql data directory, you could
> create a new directory called /var/lib/mysql/data/data1, then move all the
> directories from /var/lib/mysql/data/* into data1. Then you could create
> a
> symlink in /var/lib/mysql/data/ pointing to /var/lib/mysql/data/data1/ > name>. When mysql tries to load the data directory, it follows the
> symlink
> to the underlying directory (in /var/lib/mysql/data/data1).
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question regarding mysql database location

--00504501416d20c44904792f2962
Content-Type: text/plain; charset=ISO-8859-1

You don't need to move any databases. Look at this structure:

/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)
/disk2/mysql/db4 (directory)
/db5 (directory)
/db6 (directory)


If your mysql data directory is set to /data/disk1/mysql, the server will
pick up the symlinks there and use them as if they were just ordinary
directories.


On Wed, Nov 25, 2009 at 10:48 AM, Manasi Save <
manasi.save@artificialmachines.com> wrote:

> Well Waynn,
>
> In this case I need to move all the existing databases to new location
> right. Which I don't want to do. Is it possible that I create sym link
> between two and use both.
> --
> Thanks and Regards,
> Manasi Save
> Artificial Machines Pvt Ltd.
>
> > On Wed, Nov 25, 2009 at 12:53 AM, Manasi Save <
> > manasi.save@artificialmachines.com> wrote:
> >
> >> Thanks Waynn,
> >>
> >> I could not get your point of using symlinks. Because as per my
> >> knowledge
> >> symlink will store same data which is there in original directory.
> >> and What do you mean by "The limit for files is significantly higher
> >> than
> >> > directories."
> >>
> >> Can you elaborate it more.
> >>
> >> Thanks in advance.
> >>
> >>
> > So assuming /var/lib/mysql/data/ is your mysql data directory, you could
> > create a new directory called /var/lib/mysql/data/data1, then move all
> the
> > directories from /var/lib/mysql/data/* into data1. Then you could create
> > a
> > symlink in /var/lib/mysql/data/ pointing to
> /var/lib/mysql/data/data1/ > > name>. When mysql tries to load the data directory, it follows the
> > symlink
> > to the underlying directory (in /var/lib/mysql/data/data1).
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>

--00504501416d20c44904792f2962--

Re: question regarding mysql database location

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). Also is it somthing that in disk1/mysql it will not create physical
folder of it.

/disk2/mysql/db4 (directory)
/db5 (directory)
/db6 (directory)
--
Thanks and Regards,
Manasi Save
Artificial Machines Pvt Ltd.

> You don't need to move any databases. Look at this structure:
>
> /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)
> /disk2/mysql/db4 (directory)
> /db5 (directory)
> /db6 (directory)
>
>
> If your mysql data directory is set to /data/disk1/mysql, the server will
> pick up the symlinks there and use them as if they were just ordinary
> directories.
>
>
> On Wed, Nov 25, 2009 at 10:48 AM, Manasi Save <
> manasi.save@artificialmachines.com> wrote:
>
>> Well Waynn,
>>
>> In this case I need to move all the existing databases to new location
>> right. Which I don't want to do. Is it possible that I create sym link
>> between two and use both.
>> --
>> Thanks and Regards,
>> Manasi Save
>> Artificial Machines Pvt Ltd.
>>
>> > On Wed, Nov 25, 2009 at 12:53 AM, Manasi Save <
>> > manasi.save@artificialmachines.com> wrote:
>> >
>> >> Thanks Waynn,
>> >>
>> >> I could not get your point of using symlinks. Because as per my
>> >> knowledge
>> >> symlink will store same data which is there in original directory.
>> >> and What do you mean by "The limit for files is significantly higher
>> >> than
>> >> > directories."
>> >>
>> >> Can you elaborate it more.
>> >>
>> >> Thanks in advance.
>> >>
>> >>
>> > So assuming /var/lib/mysql/data/ is your mysql data directory, you
>> could
>> > create a new directory called /var/lib/mysql/data/data1, then move all
>> the
>> > directories from /var/lib/mysql/data/* into data1. Then you could
>> create
>> > a
>> > symlink in /var/lib/mysql/data/ pointing to
>> /var/lib/mysql/data/data1/ >> > name>. When mysql tries to load the data directory, it follows the
>> > symlink
>> > to the underlying directory (in /var/lib/mysql/data/data1).
>> >
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>>
>>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question regarding mysql database location

--001636e9080813462e04793008c4
Content-Type: text/plain; charset=ISO-8859-1

Hi Manasi,

At a time mysql can point to one data directory. For your task you can have
n number of mysql installation with different data directory. After that you
can use federated storage engine to perform your task.

Thanks,
Krishna Ch. Prajapati

On Wed, Nov 25, 2009 at 12:19 PM, Manasi Save <
manasi.save@artificialmachines.com> wrote:

> Hi All,
>
> I have asked this question before But, I think I am not able to describe
> it better.
>
> Sorry for asking it again.
> I have multiple databases but there is a limit on the folders getting
> created in one folder.
>
> I have mysql default directory set as /var/lib/mysql/data.
> Now, After 32000 folder creation I am not able to create more folders than
> that. Well Its not like I want to create 32000 database's in it (Which I
> wanted to earlier :-P).
>
> for example - I want to create 10 databases but 5 in
> /var/lib/mysql/data/d1 to d5
> and othe 5 in /var/lib/mysql/data/d6 to d10.
>
> but I want to access all the databases that is d1-d10.
>
> as I ca change the database location after 5 databases but not able to
> access old five which I have created in old location.
>
>
> Please let me know if anymore information is needed on this. I am really
> looking for the solution. Please Help me.
> --
> Thanks and Regards,
> Manasi Save
> Artificial Machines Pvt Ltd.
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=prajapatikc@gmail.com
>
>

--001636e9080813462e04793008c4--

Re: question regarding mysql database location

--001485f99caa0f498804793022da
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save <
manasi.save@artificialmachines.com> 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.

--001485f99caa0f498804793022da--

Re: question regarding mysql database location

--00504501416dba91550479302923
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Nov 25, 2009 at 12:05 PM, Krishna Chandra Prajapati <
prajapatikc@gmail.com> wrote:

> At a time mysql can point to one data directory. For your task you can have
> n number of mysql installation with different data directory. After that
> you
> can use federated storage engine to perform your task.
>

The federated engine ignores indexes on the remote database, though. Read up
on the documentation before jumping in to this :-)

--00504501416dba91550479302923--

Re: question regarding mysql database location

Thanks Johan,

It was really a great help. I'll try to implement it. I dont want to opt
for multiple mysql instances option as thats not feasible.

I'll get back to you all if it works fine.

Thanks again.

--
Best Regards,
Manasi Save
Artificial Machines Pvt Ltd.

> On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save <
> manasi.save@artificialmachines.com> 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.
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question regarding mysql database location

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.

Thanks in advance.

--
Regards,
Manasi Save
Artificial Machines Pvt Ltd.

> On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save <
> manasi.save@artificialmachines.com> 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.
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question regarding mysql database location

--00151774155a76fd64047934f8ff
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Nov 25, 2009 at 3:42 PM, Manasi Save <
manasi.save@artificialmachines.com> 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@artificialmachines.com> 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.
> >
>
>
>

--00151774155a76fd64047934f8ff--

Re: question regarding mysql database location

Is mysql the owner of the directories? ----- Original Message --=
--=0AFrom: Manasi Save =0ATo: Johan De =
Meersman =0ACc: Waynn Lue ; mysql <=
mysql@lists.mysql.com>=0ASent: Wed, November 25, 2009 8:12:25 PM=0ASubject:=
Re: question regarding mysql database location Dear Johan, Need =
your help again in understanding How mysql reads symlink. As you said =
below, I have created symlinks in default mysql directory.=0Aand try to rea=
d that symlink file as a database. But mysql is not reading=0Athat file as =
Database. Is there any settings which I need to change. Thanks in adva=
nce. -- =0ARegards,=0AManasi Save=0AArtificial Machines Pvt Ltd. =
> On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save <=0A> manasi.save@artificia=
lmachines.com> wrote:=0A>=0A>> Hi Johan,=0A>>=0A>> I am Sorry. If I have co=
mplicated the senerio But, this still not fix my=0A>> purpose.=0A>>=0A>> Wh=
at I want is - From your example :-=0A>>=0A>> /data/disk1/mysql/db1 (direct=
ory)=0A>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 /db2 (directory)=0A>>=A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 /db3 (directory)=0A>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 /d=
b4 (symlink to /data/disk2/mysql/db4)=0A>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 /=
db5 (symlink to /data/disk2/mysql/db5)=0A>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
/db6 (symlink to /data/disk2/mysql/db6)=0A>>=0A>> I dont want to create the=
se directories here (/data/disk1/mysql/d4=0A>> /d5=0A>> /d6).=0A>=0A>=0A> T=
hey're not directories, they're symlinks, which are (to the OS) a kind of=
=0A> file, and thus not limited to 32000 per directory. They behave mostly=
=0A> identical to a directory, though, so MySQL will pick them up seamlessl=
y,=0A> with the one hitch that you'll have to replace "create database"=0A>=
statements=0A> by mkdir and ln calls on the OS level.=0A>=0A> This is afai=
k the only way to do this on the MySQL level. It is impossible=0A> to speci=
fy multiple base directories.=0A>=0A> Another possible option, but higher i=
n complexity and most likely less=0A> performant, would be to run two insta=
nces of MySQL on different ports with=0A> different data directories, and u=
se MySQL Proxy to redirect incoming=0A> connections based on whatever crite=
rion you could script into it - "use=0A> database" statements, for example.=
This is however going to come with it's=0A> very own set of catches and li=
mitations.=0A>=0A> I'm not big on proxy, myself, so I'm afraid if the symli=
nk option is not=0A> acceptable to you, I can't help you any further.=0A>=
-- =0AMySQL General Mailing List=0AFor list archives: http://li=
sts.mysql.com/mysql=0ATo unsubscribe:=A0 =A0 http://lists.mysql.com/mysql?u=
nsub=3Dntnmht@yahoo.com =0A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: question regarding mysql database location

Hi Johan,

It worked perfectly. Thank you so much for this explanation.

I am really greatful.

--
Best Regards,
Manasi Save
Artificial Machines Pvt Ltd.

> On Wed, Nov 25, 2009 at 3:42 PM, Manasi Save <
> manasi.save@artificialmachines.com> 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@artificialmachines.com> 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.
>> >
>>
>>
>>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question regarding mysql database location

Hi Nitin,

Yes for the actually directories created mysql is the owner.

--
Thanks and Regards,
Manasi Save
Artificial Machines Pvt Ltd.

> Is mysql the owner of the directories?
>
>
>
> ----- Original Message ----
> From: Manasi Save
> To: Johan De Meersman
> Cc: Waynn Lue ; mysql
> Sent: Wed, November 25, 2009 8:12:25 PM
> Subject: Re: question regarding mysql database location
>
> 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.
>
> Thanks in advance.
>
> --
> Regards,
> Manasi Save
> Artificial Machines Pvt Ltd.
>
>> On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save <
>> manasi.save@artificialmachines.com> 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.
>>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=ntnmht@yahoo.com
>
>
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org