?
Solved

Once daily, on-way replication with no deletes.

Posted on 2012-09-17
6
Medium Priority
?
318 Views
Last Modified: 2012-09-22
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!
0
Comment
Question by:jriggin
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 38408017
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.
0
 
LVL 1

Author Comment

by:jriggin
ID: 38422988
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?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38423868
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
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 1

Author Comment

by:jriggin
ID: 38423932
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?
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 38424063
No please read up on mysql replication at http://dev.mysql.com/doc/refman/5.5/en/replication.html.

start-slave will start/resume the reading of binary logs from the master and replicating the changes on the slave.  It will continue until you execute stop-slave.  So what you need to do is to monitor it by executing "show slave status" and checking if "seconds behind master" is already zero.  Since you know Perl, maybe you can "borrow" code from the Nagios check_mysql_health plugin (http://exchange.nagios.org/directory/MySQL/check_mysql_health/details), among others,  for this.
0
 
LVL 1

Author Closing Comment

by:jriggin
ID: 38424777
so when seconds behind master = 0, execute stop-slave.  Thanks for the help and the links,
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month15 days, 14 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question