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

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

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.

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.