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.
2 Answers 2
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.
-
That is not a fancy storage trick but a feature that many NAS storages (like NetApp, Oracle ZFS Appliance) supply. And if one uses these storage boxes it does not depend on the application, so it can be used for mysql, too.miracle173– miracle1732015年07月14日 08:27:53 +00:00Commented Jul 14, 2015 at 8:27
-
I'm not a storage admin and have never come close to touching a decent storage device. It may as well be done with magic beans for all I know about what storage devices can do :-)Phil Sumner– Phil Sumner2015年07月14日 08:31:46 +00:00Commented Jul 14, 2015 at 8:31
-
1I will give you +1 because I once had a client that had LVM snapshots galore and would shutdown mysql, bring up a LUN, and start mysql. If one could do this and record the binary logs in between, then this answer is very viable. Don't forget Oracle is now the grandparent of MySQL (DOH !!! I still hate saying that)RolandoMySQLDBA– RolandoMySQLDBA2015年07月14日 15:29:21 +00:00Commented Jul 14, 2015 at 15:29
Not an answer - but this won't fit as a comment.
This sounds like a product that I used to work on. It was travel software that kept a local db of "deals" or "specials" and the "master" was a GDS (Global Distribution System - for airline reservations).
When a fare (trip) was requested, it searched both databases and presented the cheapest deals first then the cheapest GDS fares. We could update the local db but not the GDS.
However, this product was very specialised and many development hours went into it (Java).
If I were you, I'd look into MySQL replication - there are many options but not any (AFAIK) that correspond to your exact requirements. Look into multi-master, master-slave, synchronous, asynchronous. MySQL NDB, Tungsten Continuent and Galera.
show master statusand note binlog position. turn replication off and try your local stuff. If you like it, turn it back on. If not, revert back to binlog position before starting replication back on. ;-)