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

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

  • 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.


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()

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'.

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.