Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ;
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

I changed my account, so it's already Max.

Here, a 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.

What table do you use for your 3 months filter? Is it tblTripData?

Max.

share|improve this answer
    
@max yes I want to delete the tblTripData and also tblTripEvent,tblTripEmail and tblTripSMS too. Must I put the limit? Where and how to run the script now I have done the toolkit installation ready. Wouldnt this this lock my tables when is being run and my output I prefer to be .sql files. –  newbie14 Jan 4 '13 at 16:02
    
It's not a dump, pt-archiver transferts data on the fly between two instances. If you want a SQL file, you should use a classic mysqldump. The "limit" and "txn-size" options are design to avoid long locks. –  mfouilleul Jan 4 '13 at 16:29
1  
Note that you can add a WHERE option on your mysqldump command : dev.mysql.com/doc/refman/5.0/en/… –  mfouilleul Jan 4 '13 at 16:31
    
@max so there is no way I can use pt-archiver to transfer into a text file? The reason we are not keeping it in any of the db the old data is not going be used unless it really need then we plan to put into a table and view it. So what is your best suggestion. I read that mysqldump can lock tables and cause crashes if we do huge deletes right? –  newbie14 Jan 4 '13 at 16:44
    
You can make your mysqldump in low activity period if you have some. pt-archiver transfert data between two DBs, so you can transfert data from "main db" to your "archive DB" and if you want files you can make some backups (mysqldump) of your "archive DB" every days. So with that solution you could limit the locks on your main DB –  mfouilleul Jan 4 '13 at 16:50
show 10 more comments

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

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

It's particulary 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

If you want some examples let me know.

Max.

share|improve this answer
    
@max off course I need examples from you. But first I gone to the page and download the rpm. So should I just rpm -ivh the whole toolkit or should I select pt-archiver? –  newbie14 Jan 3 '13 at 14:47
    
@max I am done with installation. Just to let you the tblTrip is the main table and in all other table the tripID exist as a foreign key. –  newbie14 Jan 3 '13 at 15:27
    
Ok, is your date field in tblTripData? if yes, can you paste a desc tblTripData please? –  Seksi DBA Jan 3 '13 at 15:48
    
@max I have done that yes we have a date field but that date field is for the particular date time of the gps data. –  newbie14 Jan 3 '13 at 16:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.