Is this possible / advisable with MySQL

[SimonMac]

I have several copies of the same application, each with a MySQL database, each identical except for some random characters before each table name as below:

db1
xxx_table1
xxxx_table2
xxxx_table3
xxxx_table4

db2
yyyy_table1
yyyy_table2
yyyy_table3
yyyy_table4

etc

What I would like is to take say table 2 of each database an import them into database X so we have

dbx

xxxx_table2
yyyy_table2
zzzz_table2

I can then point an application at this one database, rather than each of the individual databases, I can also change/add/remove the source databases buy just changing a script to point to the new/change/removed location.

As the version of the app will always be consistent across the environments I am hoping that there will be no issues with schema etc.

Firstly is this possible? Secondly if so what is the best way to do it, I assume a cron job or something on the target server, can this be run to limit the entries pulled back from the tables for a given period IE update the target database with new entries from the last hour.

[TonyF]

Yes it's possible.

If you aren't limited in the nuymber of databases you can have, then I wouldn't bother bringing different applications into the same database - beyond only having one set of credentials, what benefit does it bring? And I'm not saying that having one set of credentials is a benefit.

Even if the applications are different (different versions or completely different applications), you can have them in the same database as long as you have space etc for the data. I'm not sure what the benefit would be though, unless you are being charged per database instance.

[SimonMac]

Yes it's possible.

If you aren't limited in the nuymber of databases you can have, then I wouldn't bother bringing different applications into the same database - beyond only having one set of credentials, what benefit does it bring? And I'm not saying that having one set of credentials is a benefit.

Even if the applications are different (different versions or completely different applications), you can have them in the same database as long as you have space etc for the data. I'm not sure what the benefit would be though, unless you are being charged per database instance.

Yep it's to try and get round a licence problem, easier/cheaper to interrogate one database than multiples

[TonyF]

Yep it's to try and get round a licence problem, easier/cheaper to interrogate one database than multiples

In that case then as long as the tables are all named differenly then there is nothing to stop you from putting them all in the same database.

MySQL doesn't support database schemas in the same way that other databases do (where you would have multiple schemas in the same database and keep the tables separately), so you have to have them all in the same database, but as long as the names don't clash then you'll be OK.

You should be able to do a table extract to flat file and then create the table in the common instance fairly easily.

[vetran]

stored procedure to update each table or to join all the tables together?

[minestrone]

I would prob set up triggers on the source databases to update the merged one in real time

If the PKs are unique I would just extract to flat file and search and replace the tables names, obvs dependant on DB size.

[DimPrawn]

Maybe the reason is for security, not risking customer A accessing customer B data by having the security aspects completely partitioned.

Many times a flaw in application security has allowed data to be revealed with expensive legal repercussions...

[minestrone]

I once worked in a place that put .001 .002 on all the primary keys to signify which cluster they were managed from.

When I complained it was "utter tulip" the tech lead claimed they get performance improvements on the queries.

Some amount of complete roasters in this industry