pt-archiver: multiple dependent tables?

Polyglot Projects Percona Toolkit
1

Hi,

does the pt-archiver tool allows to archive multiple dependent tables in a single run?

Example:

  • Table A has the column(s) with the functional archive critera (e.g. archive all rows where date_column older than 3 months)
  • Table B references table A
  • Table C contains rows which reference rows in table B
  • Table C contains rows which has no references to table B and date_column older than 3 months
  • …

I would like to archive all rows which match the example to a different MySQL instance on a different host

Thanks for help,
Jörg

2

I think you can make use of pt-archiver’s --plugin module functionality ([url]pt-archiver — Percona Toolkit Documentation and [url]pt-archiver — Percona Toolkit Documentation) to suite your needs.

3

jrivera,

The details for cascade archive and insert is not clear from the documentation. Would you mind sharing an example of how the cascaded archive & cascaded insert can be performed.

I assume this is the most common use case for many products.

I am specifically referring to these sections in the documentation before_insert(row => @row)
[INDENT] This method is called for each row just before it is inserted. This applies only to –dest. You could use this to insert the row into multiple tables, perhaps with an ON DUPLICATE KEY UPDATE clause to build summary tables in a data warehouse.
This method is not called if –bulk-insert is given.
[/INDENT] before_delete(row => @row)
[INDENT] This method is called for each row just before it is deleted. This applies only to –source. This is a good place for you to handle dependencies, such as deleting things that are foreign-keyed to the row you are about to delete. You could also use this to recursively archive all dependent tables.

[/INDENT]