Delete and archive old data from several related tables

Question

Our schema looks like this:

• table tblTrip: stores start and end trip date and time
• table tblTripData: keeps all the trip data
• table tblTripEvent: stores events related to the particular trip.

Then we have two more tables, tblTripEmail and tblTripSms, which store the email and SMS that have been sent according to the events.

We want to keep just 3 months old trip data, we would like, to from time to time, to delete and archive the rest to be stored in offline database.

What is the best mechanism to store in an offline database? I am not sure how partitioning would be helpful because the tables are linked to each other here.

Below is the table desc for tblTripData

CREATE TABLE IF NOT EXISTS `tblTripData` (
  `tripDataID` int(11) NOT NULL AUTO_INCREMENT,
  `tripID` int(5) NOT NULL,  
  `deviceSerialNumber` varchar(20) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `speed` float NOT NULL,
  `course` int(3) NOT NULL,
  `gpsdateTimer` datetime NOT NULL, 
  `insertDateTime` datetime NOT NULL,
  `odoMeter` float NOT NULL DEFAULT '0',
  `driverID` int(5) NOT NULL, 
  `eventAlertID` int(11) NOT NULL DEFAULT '0',
  `tripDataInsertDateTime` datetime NOT NULL  
  PRIMARY KEY (`tripDataID`),
  KEY `dateTime` (`gpsdateTimer`),
  KEY `tripID` (`tripID`),  
  KEY `eventAlertID` (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Answer 1

Try pt-archiver from Percona Toolkit, it permits to transfer data on the fly between two MySQL instances.

You can for instance use the --where option to filter result set by date.

It's particularly well designed for your needs, because you can transfer data and delete it from source in the same command.

Official page is here : pt-archiver

An example :

pt-archiver --source h=<server_source>,D=<database_source>,t=<your_table> --dest h=<server_target> --where "date_field < DATE_SUB(NOW(), INTERVAL 3 MONTH)" --limit 1000 --txn-size 1000 --statistics

Add the option --dry-run if you want to test.

Add the option --no-delete if you want to keep data on source DB.