Gerry Whitmarsh wrote:
> Hi Guys,
>
> Thanks a lot for the replies. @Tony, if I know it is possible I will
> persevere, thanks. @John, I definitely agree but it is so much easier to
> supply the data file with the fixed tables already filled. Also it would be
> nice to keep the environments separate on my box.
>
> Gerry.
>
> 2010/1/10 Bonnett, John <John.Bonnett@stripped>
>
>> I don't think you should be deploying the ibdata1 file anywhere. You
>> should regard that file as part of the MySQL installation on a
>> particular machine. To deploy a database you should dump the database
>> using MySQLDump or similar and restore it to the target machine.
>>
>> John Bonnett
>>
>> -----Original Message-----
>> From: Gerry Whitmarsh [mailto:gerry.whitmarsh@stripped]
>> Sent: Monday, 11 January 2010 3:40 AM
>> To: win32@stripped
>> Subject: How to have one data file per database
>>
>> Hi, maybe this is a daft question and I am missing something, but here
>> goes.
>>
>>
>> I have two applications - app A and app B with two databases - db A and
>> db B. I have a server running and ODBC connections to the two databases.
>> I can run the applications and app A accesses db A and app B access db
>> B. I now need to deploy these two applications to two different users
>> but there is only one ibdata1 file.
>>
>> Is there a way to keep the two databases apart and have one ibdata1(or
>> another name) file per database?
>>
>> I have tried creating a new service pointing to a different ini file
>> where innodb_data_home_dir points to a different location but I just get
>> all kinds of errors.
>>
>> I am running MySQL 5.1.
>>
>> Any help would be greatly appreciated.
>> Gerry.
>>
>> --
>> Life's journey is not to arrive at the grave safely, in a well-preserved
>> body, but rather to slide in sideways, totally worn out, shouting ****,
>> what a ride!!!
>>
>
>
>
Why can you not use restricted login rights for the users? If they
connect from machine A only allow them to use DatabaseA. Same for
DatabaseB and connections from machineB. Which user accounts allow
access from which machines is determined by the host portion in the
user's login definition. User privileges can be set at the global,
database, table, or column levels.
There is no physical or logical reason to force MySQL to change how it
manages its data just to accommodate what should be a permissions-based
decision. One MySQL instance (the service) can host many databases at
the same time. In fact, we install two by default, `mysql` and `test`,
and you have probably created several more.
Of, course, if you *must* have physically distinct MySQL instances
containing one or more databases of information, then you need
physically distinct MySQL instances. This either means that you setup
two or more instances on one machine or two instances on two machines.
The choice is yours.
For more information, I encourage you to read our fine manual:
http://dev.mysql.com/doc/refman/5.1/en/privilege-system.html
http://dev.mysql.com/doc/refman/5.1/en/multiple-servers.html
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
