Multiple MySQL schemas
in [Databases]
|
Prev: unlink problems
Next: Combing PDO with mysql_pconnect connections in one application --will this degrade performance?
From: Philip Thompson on 8 Dec 2009 12:54 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
From: Bastien Koert on 8 Dec 2009 13:51
On Tue, Dec 8, 2009 at 12:54 PM, Philip Thompson <philthathril(a)gmail.com> wrote: > 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 > > 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. -- Bastien Cat, the other other white meat |