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.

  • Is this for MySQL in Amazon RDS ??? – RolandoMySQLDBA Jul 10 '15 at 15:57
  • MySQL, but Amazon RDS is intresting too – Jdahern Jul 10 '15 at 19:02
  • maybe you are you want to have a clone blogs.oracle.com/oem/entry/snap_clone_instant_database_on – miracle173 Jul 14 '15 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 – Mike S. Jul 15 '15 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. ;-) – Mike S. Jul 15 '15 at 7:09
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 | |
  • 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 Jul 14 '15 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 Jul 14 '15 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) – RolandoMySQLDBA Jul 14 '15 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, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.