Creating a 'shadow' database in MySQL A while back, a friend told me of a facility that would allow us to "shadow" a production database using a local database. The term shadow may be incorrect (so please correct me if so)

What I understand, and want to do is:


When I make an update / insert, it only impacts the database that is local.
I make a select statement, the return is first the data from the database that is shadowing, then the production data is filled in.
table modifications only impact the database that is shadowing
clear out (roll back transactions?) to get the local to a neutral point.
would prefer not to have to copy the database over, but pull from the live data if possible.


Since this is not designed for production, performance is not really an issue.

The use case in mind is that I would have said database for local / testing, and a quick way to revert all the changes that I had done, if they need to be.
In Oracle Database world, what you are describing sounds like dNFS-based cloning (http://www.oracle.com/technetwork/server-storage/hardware-solutions/o13-022-rapid-cloning-db-1919816.pdf [PDF], https://oracle-base.com/articles/11g/clonedb-11gr2).

Start with a read-only copy of all the data files, which are read by the clone instance.  Changes/writes are written to delta files ("copy on first write"), and the database looks to these delta files before looking at the read-only copies.  This provides full "copies" of the database content in remarkably little space.  The whole lot can be rolled back by shutting down the instance and removing the delta files.

I've never seen such a thing in MySQL.  It might be possible with fancy storage tricks, but I don't think it's a feature that the database supports.