4

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.

Improve this question
5
  • Is this for MySQL in Amazon RDS ??? Commented Jul 10, 2015 at 15:57
  • MySQL, but Amazon RDS is intresting too Commented Jul 10, 2015 at 19:02
  • maybe you are you want to have a clone blogs.oracle.com/oem/entry/snap_clone_instant_database_on Commented Jul 14, 2015 at 8:28
  • 1
    Replication with master-slave should work. You write the master database and the slave is read only. Typically a few edits to your my.cnf file to turn on binlog on master, and then commands to start replication and you can try it out. See dev.mysql.com/doc/refman/5.5/en/replication-howto.html Commented Jul 15, 2015 at 7:04
  • 1
    Another option is master-master replication and when you want to try something locally, show master status and 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. ;-) Commented Jul 15, 2015 at 7:09

2 Answers 2

Reset to default
3
+50

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.

Improve this answer
3
  • 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. Commented 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 :-) Commented Jul 14, 2015 at 8:31
  • 1
    I 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) Commented Jul 14, 2015 at 15:29
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.