Archiving data over date range.

[slnp]

Hello Everyone,

I am looking at a better solution for archiving large amounts of data over a date range. At the moment I am using a script that I created that creates a temporary table and inserts data into it using the following query:

INSERT INTO temp_candata SELECT * FROM candata WHERE timestamp between ‘2011-02-01 00:00:00’ and ‘2011-03-01 00:00:00’

Then I use mysqldump to dump the table, tar it and then delete it.

I know this method is not elegant or optimal. The time it takes to archive is much longer than I think it could be.

I had a quick look at mk-archiver but I am unsure of how to get the data from a specific date range.

Can anyone suggest a way I could do this?

Thanks in advance

[gmouse]

http://www.maatkit.org/doc/mk-archiver.html
As you can see, there is a --where option that can be set to “timestamp between ‘2011-02-01 00:00:00’ and ‘2011-03-01 00:00:00’”. Try --dry-run first if you are experimenting.

[slnp]

Thank you for the reply.

When I run

xxxx@xxxx:~/mysql_files$ mk-archiver --source h=localhost,D=xxxx,t=datavalue --purge --where ‘timestamp < ‘2011-03-01 00:00:00’’

I get the output:

Usage: /usr/bin/mk-archiver --source DSN --where WHERE

Errors in command-line arguments:

• Unrecognized command-line options 00:00:00

mk-archiver nibbles records from a MySQL table. The --source and --dest
arguments use DSN syntax; if COPY is yes, --dest defaults to the key’s value
from --source. For more details, please use the --help option, or try ‘perldoc
/usr/bin/mk-archiver’ for complete documentation.