Every client of our online services has its own MySQL database. All databases have exactly same schema, however, a number of databases is constantly changing as we archive db for inactive client's as well as new db gets created after every signup.
Is there a way to run analytics in this kind of environment? There seems to be no way to run query across multiple databases. The same applies to ETL solutions that we have investigated.
Merging databases and updating app code seems like a major effort. Create merged db for reporting purposes using db triggers might be the way to go.
Currently, if we need to run some reports across db, we use PHP script to execute query on every single client db which is very inefficient.