Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there a way to set the MySQL replication to add new rows and delete the old rows? for example i have table on the master MySQL that goes back about 5 years. I would like the replication to only keep up to date with the last 30 days. Anything after 30 days will be deleted off the replication. How could i achieve this?

  • Thanks in advance
share|improve this question

migrated from stackoverflow.com Aug 6 '10 at 10:19

This question came from our site for professional and enthusiast programmers.

4 Answers 4

No, a replica that is not a replica from the original, isn't a replica.

share|improve this answer
    
Correct. A partial copy is NOT a replica. –  John Gardeniers Aug 6 '10 at 12:45

You cannot get to where you want to go via that route. Your first problem is that MySQL needs a way of determining what data is to be kept and what is to be deleted. If you have no dependencies between tables you could do it with a timestamp or date field in each table, in which case you just run one simple query on each table.

There is a possibility that you could do it via the logs but that would be both tricky and error prone.

share|improve this answer

While you cannot do this with native MySQL support, as many others have pointed out, in most cases it can be done fairly easily with a script. However, easiness will depend on how complex your schema is.

Here's an example of a query that deletes from a table that has a timestamp field using date and time functions...

delete from table where DATE_ADD( DATE_FORMAT( table.timestamp,'%Y-%m-%d %T'), INTERVAL 4 HOUR) < NOW()
share|improve this answer

After you've set up replication and got the databases identical, issue 'stop slave' and delete the old data. It won't reappear unless the master does something involving those rows. Then 'start slave'.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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