asked on

Once daily, on-way replication with no deletes.

I have two servers, production and archive/admin. I want replicate a few tables from the production server to archive once per day, but never delete any records from the archive, just add and update. I'm doing this now with a very kludgy export/import, and have not found a way to do it with replication, though I have dug through replication docs until my head hurts.

Is there a way to do this? If so, how?

Thanks!

johanntagle

Replication will do all updates, including deletes. There's a way around it though. The following solution assumes the tables in question have primary keys, or at least one unique key.

1. On the master, create "clone" tables of the ones you need to track - exactly the same columns, plus the same primary key (you can leave other indexes)
2. Create insert and update triggers on the tables to be tracked to do a REPLACE INTO to their respective clones so that the new/updated rows are reflected on the new clone table.
3. On the slave/archive server also create the respective clone tables, but this time the triggers will be on the clones to do a REPLACE INTO to the original tables
4. Configure replication - on the slave use --replicate-do-db and --replicate-do-table to replicate only the clone tables. See http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html

So what will happen is when an insert or update is done on a table, the trigger will reflect the same change on it's clone table, which will get replicated on the clone table on the save, which will then trigger to reflect the change to the counterpart of the original table.

The above will replicate the inserts and updates in near real-time, so if you only want it done in batches, just turn the replication on and off.

Member_2_241474

ASKER

Thanks, johanntagle -- Sorry for the delay.

That's a more complex than I was hoping for. Is there a way to turn replication on and off through a Perl script so I can put it on cron?

johanntagle

Sure. Actually no need for Perl as a bash script will do. But if you want Perl, just do a system call for:

mysqladmin -uroot -ppassword stop-slave
mysqladmin -uroot -ppassword start-slave

Member_2_241474

ASKER

I assume I would put
mysqladmin -uroot -ppassword start-slave
in the script and when control returns to script that means replication has been completed. Then mysqladmin -uroot -ppassword stop-slave would stop the slave from writing binray logs?

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_241474

ASKER

so when seconds behind master = 0, execute stop-slave. Thanks for the help and the links,