I need to set up a system to do basically a partial backup/replication of a couple of MySQL databases (actually Percona XtraDB 5.5, all tables are InnoDB). There are a few hundred tables in one of the databases and a handful of tables in the other.
Some tables should be replicated in full, while others should only have certain records replicated. Basically the data belongs to different "users", and I only want a backup of the data related to one of the "users" (including any data that is not specific to a particular user).
So I don't think that any of the normal replication options would help with this, but please correct me if I'm wrong.
I think I'll have to write a script that exports the relevant rows from the relevant tables periodically and then imports them into the "slave" database. The problem with this is that I'd like to do this once per day, and I would like to do essentially an incremental export instead of exporting everything every day.
Obviously I can't just keep track of the primary keys of the records I have exported, because older records can be updated after the daily export has run. Maybe I could create triggers on every table to add a timestamp to every modified row or clear an "exported" field, but I suspect adding triggers to hundreds of tables might impact the performance of the system.
So is there some other way I can find out which records have been modified in the last 24 hours so that I can copy the relevant ones again?
Or is there another solution to this problem?