List:MySQL on Win32« Previous MessageNext Message »
From:Shawn Green Date:February 4 2010 2:53pm
Subject:Re: How to have one data file per database
View as plain text  
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


Thread
How to have one data file per databaseGerry Whitmarsh10 Jan
  • RE: How to have one data file per databaseJorge Bastos10 Jan
  • RE: How to have one data file per databaseJohn Bonnett10 Jan
    • Re: How to have one data file per databaseGerry Whitmarsh11 Jan
      • Re: How to have one data file per databaseShawn Green4 Feb
RE: How to have one data file per databaseTony White10 Jan
Re: How to have one data file per databaseGerry Whitmarsh11 Jan