Hi All,

I have two MySQL databases and I would like to place each one of them on
a separated disk, and backup each one of them separately.


I understand I need to use the "One file per table" option, right? It
will effect all the databases.

How do I place each database on a different volume? Do I use file link?
How to do it on Windows platform?

Thanks, Reuven

Option 1:
Assuming you do not need the databases to interact with each other I
would setup two instances of mysql with separate datadirectories. This
should make your back process easier. Read:
http://dev.mysql.com/doc/refman/5.0/...ion-files.html
http://dev.mysql.com/doc/refman/5.0/...tion-file.html

Option 2:
You could symlink (or NTFS junction for windows using a tool like
winbolic link) the data directories for the individual databases to
different locations.

As for "One file per table" I don't think that is what you want/need.
http://dev.mysql.com/doc/refman/5.0/...blespaces.html
"Using multiple tablespaces can be beneficial to users who want to
move specific tables to separate physical disks..." and you want to
move the whole database.





On 3/31/08, Reuven Nisser <rnisser@era-massis.com> wrote:
> Hi All,
>
> I have two MySQL databases and I would like to place each one of them on
> a separated disk, and backup each one of them separately.
>
>
> I understand I need to use the "One file per table" option, right? It
> will effect all the databases.
>
> How do I place each database on a different volume? Do I use file link?
> How to do it on Windows platform?
>
> Thanks, Reuven
>
>
>


--
Rob Wultsch
(480)223-2566
wultsch@gmail.com (email/google im)
wultsch (aim)
wultsch@hotmail.com (msn)

I would not recommend using either SMB or NFS to access data
directories , especially if you expectingany kind of load. File locks
simply do do work effectively (or at all) across such mounts.

- michael dykman

On Tue, Apr 1, 2008 at 12:05 PM, Rob Wultsch <wultsch@gmail.com> wrote:
> Option 1:
> Assuming you do not need the databases to interact with each other I
> would setup two instances of mysql with separate datadirectories. This
> should make your back process easier. Read:
> http://dev.mysql.com/doc/refman/5.0/...ion-files.html
> http://dev.mysql.com/doc/refman/5.0/...tion-file.html
>
> Option 2:
> You could symlink (or NTFS junction for windows using a tool like
> winbolic link) the data directories for the individual databases to
> different locations.
>
> As for "One file per table" I don't think that is what you want/need.
> http://dev.mysql.com/doc/refman/5.0/...blespaces.html
> "Using multiple tablespaces can be beneficial to users who want to
> move specific tables to separate physical disks..." and you want to
> move the whole database.
>
>
>
>
>
>
>
> On 3/31/08, Reuven Nisser <rnisser@era-massis.com> wrote:
> > Hi All,
> >
> > I have two MySQL databases and I would like to place each one of them on
> > a separated disk, and backup each one of them separately.
> >
> >
> > I understand I need to use the "One file per table" option, right? It
> > will effect all the databases.
> >
> > How do I place each database on a different volume? Do I use file link?
> > How to do it on Windows platform?
> >
> > Thanks, Reuven
> >
> >
> >
>
>
> --
> Rob Wultsch
> (480)223-2566
> wultsch@gmail.com (email/google im)
> wultsch (aim)
> wultsch@hotmail.com (msn)
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>
>



--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

Hi Rob,

Thank you for the answer.

Yes, the two databases do not interact but I access them using ODBC
connection and if I use two instances, I will need two ODBC connections
setup on each production PC. If I use one instance I could use for the
second connection:

;ODBC=XXX;Database=yyy

So, I prefer not to use option 1.


I did not understand option 2. If I do not use "One file per table"
then all data for all databases is kept in the same set of files
(IBDATA*), right? If I use "One file per table", then I could use
winbolic link to place the database on a different disk, right?

Am I missing something in option 2? Is there a way to separate the data
of one database without "One file per table"? How?


Thanks, Reuven


Rob Wultsch wrote:

> Option 1:
> Assuming you do not need the databases to interact with each other I
> would setup two instances of mysql with separate datadirectories. This
> should make your back process easier. Read:
> http://dev.mysql.com/doc/refman/5.0/...ion-files.html
> http://dev.mysql.com/doc/refman/5.0/...tion-file.html
>
> Option 2:
> You could symlink (or NTFS junction for windows using a tool like
> winbolic link) the data directories for the individual databases to
> different locations.
>
> As for "One file per table" I don't think that is what you want/need.
> http://dev.mysql.com/doc/refman/5.0/...blespaces.html
> "Using multiple tablespaces can be beneficial to users who want to
> move specific tables to separate physical disks..." and you want to
> move the whole database.
>
>
> On 3/31/08, Reuven Nisser <rnisser@era-massis.com> wrote:
>
>> Hi All,
>>
>> I have two MySQL databases and I would like to place each one of them on
>> a separated disk, and backup each one of them separately.
>>
>>
>> I understand I need to use the "One file per table" option, right? It
>> will effect all the databases.
>>
>> How do I place each database on a different volume? Do I use file link?
>> How to do it on Windows platform?
>>
>> Thanks, Reuven
>>
>>
>>
>>
>
>
>

2008/4/1 Reuven Nisser <rnisser@era-massis.com>:

> Hi Rob,
>
> Thank you for the answer.
>
> Yes, the two databases do not interact but I access them using ODBC
> connection and if I use two instances, I will need two ODBC connections
> setup on each production PC. If I use one instance I could use for the
> second connection:
>
> ;ODBC=XXX;Database=yyy
>
> So, I prefer not to use option 1.
>
>
> I did not understand option 2. If I do not use "One file per table" then
> all data for all databases is kept in the same set of files (IBDATA*),
> right? If I use "One file per table", then I could use winbolic link to
> place the database on a different disk, right?
>
> Am I missing something in option 2? Is there a way to separate the data of
> one database without "One file per table"? How?
>

I think you are correct and was wrong. For some reason I was thinking inodb
data was stored on a per database manner, inside the database directory. (I
am almost exclusively a myisam user). Boy, that is an ugly way of doing
things...

With the one file per table a symlink or junction should work.

Sorry for the misinformation.

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

Hi Michael,
I do not understand, please explain.
The plan is to use "One file per table", move the database directory to
a separate disk and establish a link to the directory from the previous
location. What locking problem do you see in Unix or Windows?
Thanks, Reuven

Michael Dykman wrote:
> I would not recommend using either SMB or NFS to access data
> directories , especially if you expectingany kind of load. File locks
> simply do do work effectively (or at all) across such mounts.
>
> - michael dykman
>
> On Tue, Apr 1, 2008 at 12:05 PM, Rob Wultsch <wultsch@gmail.com> wrote:
>
>> Option 1:
>> Assuming you do not need the databases to interact with each other I
>> would setup two instances of mysql with separate datadirectories. This
>> should make your back process easier. Read:
>> http://dev.mysql.com/doc/refman/5.0/...ion-files.html
>> http://dev.mysql.com/doc/refman/5.0/...tion-file.html
>>
>> Option 2:
>> You could symlink (or NTFS junction for windows using a tool like
>> winbolic link) the data directories for the individual databases to
>> different locations.
>>
>> As for "One file per table" I don't think that is what you want/need.
>> http://dev.mysql.com/doc/refman/5.0/...blespaces.html
>> "Using multiple tablespaces can be beneficial to users who want to
>> move specific tables to separate physical disks..." and you want to
>> move the whole database.
>>
>>
>>
>>
>>
>>
>>
>> On 3/31/08, Reuven Nisser <rnisser@era-massis.com> wrote:
>> > Hi All,
>> >
>> > I have two MySQL databases and I would like to place each one of them on
>> > a separated disk, and backup each one of them separately.
>> >
>> >
>> > I understand I need to use the "One file per table" option, right? It
>> > will effect all the databases.
>> >
>> > How do I place each database on a different volume? Do I use file link?
>> > How to do it on Windows platform?
>> >
>> > Thanks, Reuven
>> >
>> >
>> >
>>
>>
>>

Hi Reuven,

Michael is warning you against using SMB or NFS for your data
directory, in case you were thinking of doing so. I know you didn't
mention that but I think he was trying to be ful just in case you
got the idea to use one of these technologies.

Baron

2008/4/1 Reuven Nisser <rnisser@era-massis.com>:
>
> Hi Michael,
> I do not understand, please explain.
> The plan is to use "One file per table", move the database directory to a
> separate disk and establish a link to the directory from the previous
> location. What locking problem do you see in Unix or Windows?
> Thanks, Reuven
>
>
>
> Michael Dykman wrote:
> I would not recommend using either SMB or NFS to access data
> directories , especially if you expectingany kind of load. File locks
> simply do do work effectively (or at all) across such mounts.
>

--

Baron Schwartz, Senior Consultant, Percona Inc.
Tel: +1 888 401 3401 ext 507
24/7 Emergency Line +1 888 401 3401 ext 911
Our Services: http://www.percona.com/services.html
Our Blog: http://www.mysqlperformanceblog.com/