I actually wrote a stored procedure to do this for me. I'll try and provide an example; actually I wrote two. One to create tables and the other to copy data.
I have two "foreign keys" that are used in pretty much every table they are called vhostid and mid. So this is what I did to back up tables I wrote:
CREATE DEFINER = 'mydbuser'@'%' PROCEDURE `sp_create_backup_tables`()
SQL SECURITY DEFINER
DECLARE done INT DEFAULT FALSE;
DECLARE table_to_create, database_name VARCHAR(255);
DECLARE backup_tables_cursor CURSOR FOR
SELECT DISTINCT (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
INNER JOIN INFORMATION_SCHEMA.TABLES
ON INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA
AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE INFORMATION_SCHEMA.TABLES.TABLE_TYPE <> 'VIEW'
AND (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'vhostid'
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'mid')
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA <> 'db_archive'
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA LIKE 'mydbname%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
CREATE DATABASE IF NOT EXISTS db_archive;
WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME LIKE 'mydbname%'
AND INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME <> 'db_archive';
FETCH backup_tables_cursor INTO table_to_create;
IF done THEN
SET @backup_table_name = CONCAT('db_archive.', table_to_create, '_archive');
SET @SQL = CONCAT('CREATE TABLE IF NOT EXISTS ', @backup_table_name, ' LIKE ', database_name, '.', table_to_create);
PREPARE stmt FROM @SQL;
DEALLOCATE PREPARE stmt;
So this essentially creates all the backup tables you should need.
Next step let's get all the records. Unfortunately I'm unable to find that stored procedure at the moment but it works very much the same way, all my rows that I care about have a datetime column of creation date. So I traverse every table that's in my db_backup table and select all rows where the creation date is less than now(); I then extract all the row ids from my key tables, vhosts and media and store those ids in a cursor.
Next I go through all the other tables that might maintain that foreign key relationship and copy those rows over to the new database. It is fairly cpu intensive on a large db but it works and I execute at a low point in traffic over night.
Lastly, I have a variable that's passed to the trigger on whether or not to delete the rows I've copied over, if I do, then I destroy the rows in the various tables either as I go along.
Hopefully that helps, I wish I could find that stored procedure I wrote to copy over rows but for some reason it seems to be missing.