Want to archive tables? Use Percona Toolkit’s pt-archiver

pt-archiverPercona Toolkit’s pt-archiver is one of the best utilities to archive the records from large tables to another tables or files. One interesting thing is that pt-archiver is a read-write tool. It deletes data from the source by default, so after archiving you don’t need to delete it separately.

As it is done by default, you should take care before actually running it on then production server. You can test your archiving jobs with the — dry-run  OR you can use the –no-delete option if you’re not sure about. The purpose of this script is mainly to archive old data from the table without impacting OLTP queries and insert the data into another table on the same/different server OR into a file in a format which is suitable for LOAD DATA INFILE.

How does pt-archiver select records to archive? 

Pt-archiver uses the index to select records from the table. The index is used to optimize repeated accesses to the table. Pt-archiver remembers the last row it retrieves from each SELECT statement, and uses it to construct a WHERE clause. It does this using the columns in the specified index that should allow MySQL to start the next SELECT where the last one ended – rather than potentially scanning from the beginning of the table with each successive SELECT.

If you want to run pt-archiver with a specific index you can use the “-i” option in –source DSN options. The “-i” option tells pt-archiver which index it should scan to archive. This appears in a FORCE INDEX or USE INDEX hint in the SELECT statements that are used to fetch rows to archive. If you don’t specify anything, pt-archiver will auto-discover a good index, preferring a PRIMARY KEY if one exists. Most of the time, without “-i” option, pt-archiver works well.

How to run pt-archiver?

For archive records into normal file, you can run something like

From archive records from one table to another table on same server or different, you can run something like

Please check this before you use default file option (-F) in –source  https://www.percona.com/doc/percona-toolkit/2.1/pt-archiver.html#cmdoption-pt-archiver–dest

Archiving in a replication environment:

In the replication environment it’s really important that the slave should not lag for a long time. So for that, there are two options which we can use while archiving to control the slave lag on slave server.

–check-slave-lag : Pause archiving until the specified DSN’s slave lag is less than –max-lag. In this option, you can give slave details to connect slave lag. (i.e –check-slave-lag h=localhost,S=/tmp/mysql_sandbox29784.sock)

–max-lag : Pause archiving if the slave given by –check-slave-lag lags.

This options causes pt-archiver to look at the slave every time when it’s about to fetch another row. If the slave’s lag is greater than the option’s value, or if the slave isn’t running (so its lag is NULL), pt-archiver sleeps for –check-interval seconds and then looks at the lag again. It repeats until the slave is caught up, then proceeds to fetch and archive the row.

Some useful options for pt-archiver:

–for-update/-share-lock  : Adds the FOR UPDATE/LOCK IN SHARE MODE  modifier to SELECT statements.

–no-delete : Do not delete archived rows.

–plugin : Perl module name to use as a generic plugin.

–progress : Print progress information every X rows.

–statistics : Collect and print timing statistics.

–where : WHERE clause to limit which rows to archive (required).

Percona Toolkit’s pt-archiver works with Percona XtraDB Cluster (PXC) 5.5.28-23.7 and newer, but there are three limitations you should consider before archiving on a cluster. You can get more information here.

pt-archiver is extensible via a plugin mechanism. You can inject your own code to add advanced archiving logic that could be useful for archiving dependent data, applying complex business rules, or building a data warehouse during the archiving process. Follow this URL for more info on that.

Bugs related to pt-archiver: https://bugs.launchpad.net/percona-toolkit/+bugs?field.tag=pt-archiver

More details about pt-archiver: https://www.percona.com/doc/percona-toolkit/2.2/pt-archiver.html

Share this post

Comments (17)

  • Document Storage London

    Document Storage London is the good environment and save your document on low cost leavel.

    September 26, 2013 at 6:34 am
  • Rachel

    I execute following command, always encounter following error:
    C:\Program Files\MySQL\MySQL Server 5.5\bin>pt-archiver –source h=localhost,D=a
    mi,t=table –user root –password root –file ‘c:\test.txt’ –where
    “id=’915′” –no-check-charset –commit-each –limit 1
    Cannot open ‘c:\test.txt’: Invalid argument

    Appreciate your comments and suggestion ASAP.

    December 5, 2013 at 11:20 pm
  • Nilnandan Joshi

    Hi Rachel,

    Percona Toolkit is not fully supported to Windows though some utilities works fine. Here, it looks like pt-archiver can’t understand the path of –file. (seems it’s problem of backslash v/s forward slash). Can you try to run same command with –file options like –file ‘C:/test.txt’ OR –file ‘C:\\test.txt’ i.e

    >pt-archiver –source h=localhost,D=ami,t=table –user root –password root –file ‘c:/test.txt’ –where “id=915” –no-check-charset –commit-each –limit 1

    December 10, 2013 at 1:02 am
  • Irvin

    I am looking to use pt-archiver to archive several fast growing tables. I want to put the script in a cron job but don’t necessarily want to have the password in the script.

    Will pt-archiver ever use –login-path=xxx ? This seems a much more secure method of running scripts in batch mode.

    May 21, 2014 at 4:57 pm
  • CEPE

    Hi, in your last example, the limit condition (–limit) seems to be useless, isn’t it?

    July 10, 2014 at 12:01 pm
  • Rajeev Rai

    I have the same problem with pt-archver as Irvin has..

    I dont want to provide username/password details in command line.. Can this tool pick it up from a source file ?

    March 1, 2015 at 5:42 am
  • Nilnandan Joshi

    Hi Irvin/Rajeev,

    You can create .my.cnf file at the location from where you want to run pt-archiver. So you don’t need to give user/pass with command line. It will take user credentials from there only. i.e

    nilnandan@desktop:~$ cat .my.cnf
    user = root

    Please check and let me know if it works or not.

    March 2, 2015 at 2:33 am
  • Prateek

    HI there, I am new to this tool and want to archive tables created in MYSQL. can u please help me how to start from the very beginning. what all commands to write ..I have read the document but not able to understand how to start my work. Please help me.

    October 14, 2015 at 2:25 am
  • Nilnandan Joshi

    Hi Prateek,

    It’s very easy. You can select the records with –where option and archive them. I would suggest to read this wiki and you’ll get all the steps which you needed.

    October 22, 2015 at 11:19 pm
  • Nani

    Hi Nilandan,

    If Im going to archive old data from parent table to another table.

    How is the application going to fetch old data if required, will there be any reference stored somewhere that data is in another table?

    October 27, 2015 at 3:45 am
  • Nani


    One more question is does pt-archiver archives data based on Date.

    That is all the data that is created before 6 months should be archived, is there any option like that?

    October 27, 2015 at 3:47 am
  • Nani


    Also few more questions may be not relevant to this KB, I have few questions on Indexes.

    1) If I do MysqlDump of all databases & restore on new Server does Indexes gets created automatically on new server
    2) Does it require to periodically Drop/Recreate indexes for better performance
    3) Can we drop & create Indexes on Slave (master-Slave ) scenario , does this break replication

    October 27, 2015 at 4:04 am
  • Gopal

    Hi Nil, I am facing a Problem with Login, when I specify the –file.
    gopal@D252:~/Work/percona-toolkit-2.2.17/bin$ perl pt-archiver –source h=sedodb2-analysis.i.sedorz.net -usdbrw -p=$sdbrw_pw,D=temp,t=xyz –dest h=sedodbdevha1.i.sedorz.net -usdbrw -p$sdbrw_pw,D=temp,t=xyz –where “1=1” –progress=1
    2016-05-19T10:32:51 0 0
    2016-05-19T10:32:51 0 1
    2016-05-19T10:32:51 0 2
    2016-05-19T10:32:51 0 3
    2016-05-19T10:32:51 0 4
    2016-05-19T10:32:51 0 4

    gopal@D252:~/Work/percona-toolkit-2.2.17/bin$ perl pt-archiver –source h=sedodb2-analysis.i.sedorz.net -usdbrw -p=$sdbrw_pw,D=temp,t=xyz –file xyz –where “1=1”
    DBI connect(‘temp;host=sedodb2-analysis.i.sedorz.net;mysql_read_default_group=client’,’sdbrw’,…) failed: Access denied for user ‘sdbrw’@’’ (using password: YES) at pt-archiver line 2492.

    I am using the same Credential in the first and Second statement.

    May 19, 2016 at 4:41 am
    • Gopal

      Hi Nil, You can ignore last comment, I found the Problem.

      May 19, 2016 at 5:56 am
    • shruti kapoor

      I am also facing a Problem with Login.

      October 26, 2018 at 2:19 pm
  • vishnu

    Hi Nil,

    can we load infile into a table using pt-archiever ?

    August 3, 2017 at 7:23 am
  • shruti kapoor

    I am facing a Problem with Login.

    November 14, 2018 at 1:44 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.