Re: Multiple MySQL schemas

On Tue, Dec 8, 2009 at 12:54 PM, Philip Thompson <philthathril@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