Multiple MySQL schemas

Hi all.

We are wanting to create an app that contains multiple clients. Each =
clients has anywhere from 1 user to more than a hundred. We had the idea =
of separating each client into its own database schema. This reduces the =
single point of failure - if 1 client db dies, it doesn't kill the =
others. This keeps the individual schemas smaller, which will allow us =
to asynchronously (for lack of a better word) transfer our backups to =
our network w/o causing bottlenecks. It also guarantees the separation =
of data (even though the application takes care of this, it's *that =
extra step*).

Is it reasonable to create a new database per client? Or should we =
cluster several clients together (5-10) into a single database? What are =
the pros and cons of each? Note that some clients are *linked* and share =
data, so those would not be mutually exclusive. What is the maximum =
number of schemas per MySQL instance - I'm guessing this is based on the =
filesystem (RHEL)?

Thanks in advance,
~Philip=

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Multiple MySQL schemas

On Tue, Dec 8, 2009 at 12:54 PM, Philip Thompson w=
rote:
> Hi all.
>
> We are wanting to create an app that contains multiple clients. Each clie=
nts has anywhere from 1 user to more than a hundred. We had the idea of sep=
arating each client into its own database schema. This reduces the single p=
oint of failure - if 1 client db dies, it doesn't kill the others. This kee=
ps the individual schemas smaller, which will allow us to asynchronously (f=
or lack of a better word) transfer our backups to our network w/o causing b=
ottlenecks. It also guarantees the separation of data (even though the appl=
ication takes care of this, it's *that extra step*).
>
> Is it reasonable to create a new database per client? Or should we cluste=
r several clients together (5-10) into a single database? What are the pros=
and cons of each? Note that some clients are *linked* and share data, so t=
hose would not be mutually exclusive. What is the maximum number of schemas=
per MySQL instance - I'm guessing this is based on the filesystem (RHEL)?
>
> Thanks in advance,
> ~Philip
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>



Phil,

We do exactly that, having one db per client. As you say, it handles
the separation of data. This should be the only reason to separate the
dbs, if the data is sensitive enough to require that.

The one thing to think about is to create a different user for each DB
instance to ensure that if someone compromises on DB that they can't
automatically gain access to the others. Also ensure that the user
account has the least required privileges needed to do the job. That
may mean more account maintenance headaches for you, but will keep the
data safer, as there is no need to have the user account have admin
privileges on the db for any reason.
--=20

Bastien

Cat, the other other white meat

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php