2 years later ... OK this sounds like the best approach as it lets you scale things out as you grow and actually need more servers (then you just have to manage which database to switch too).
The cons of this approach is that most ORM's don't support this. On Fri, Jun 1, 2012 at 2:37 PM, Christopher Schultz < ch...@christopherschultz.net> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Ahmed, > > On 5/31/12 9:33 AM, S Ahmed wrote: > >> It would be easier if all databases were hosted by a single > >> instance of MySQL -- then you could use Tomcat-pool's feature of > >> being able to provide credentials when obtaining connections from > >> the pool -- and get the right database. That way, a much smaller > >> number of connections could be maintained with roughly the same > >> semantics. > > > > Can you expand on how I could do this? > > Well, first you have to accept that the same MySQL instance (with > perhaps different databases) will be hosting all your data. If that's > not okay with you, then you can forget this suggestion (or, maybe it > just changes the suggestion slightly because you could always have N > databases and maybe 4 MySQL instances when them split across the 4... > but then you'd have to figure out which connection pool to grab before > getting a connection or you'd never get connected). > > Next, you'd have to switch to tomcat-pool because commons-dbcp (the > default CP in Tomcat) does not support obtaining pooled connections > with credentials. > > Next, you remove the database name from the JDBC URL (or maybe change > it to something everyone can access, like the 'test' database or > 'information_schema' depending on your version of MySQL). > > Then, you have your code call > DataSource.getConnection(username,password) instead of calling > DataSource.getConnection(). This gets you the right credentials for > your target database. > > Finally, make sure you issue a query to set the database for the > connection: > > 'USE databasename;' > > Then, you set your connection pool to have however many connections > you want (100?) and every client (thread) shares those connections > with every client database that runs in a particular MySQL instance. > > - -chris > -----BEGIN PGP SIGNATURE----- > Version: GnuPG/MacGPG2 v2.0.17 (Darwin) > Comment: GPGTools - http://gpgtools.org > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEARECAAYFAk/JC8wACgkQ9CaO5/Lv0PBEpgCghI3t8gpE+SBSNV/pYjyLqqwq > 2hwAoIY8mYqGGG+owxzsFPQ+CFa2cVeL > =Fh/Y > -----END PGP SIGNATURE----- > > --------------------------------------------------------------------- > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > >
