Buy Percona ServicesBuy Now!

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

 | August 12, 2013 |  Posted In: MySQL, Percona Server for MySQL, Percona Software, Percona Toolkit


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–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:

More details about pt-archiver:

Nilnandan Joshi

Nilnandan officially started with Percona as a Support Engineer. Before joining Percona, he has worked as a MySQL Database administrator with different types of service based companies managing high-traffic websites and web applications. Nilnandan has extensive experience in database design and development, database management, client management, security/documentations/training, implementing DRM solutions, automating backups and high availability. Nilnandan is based at Pune (India). In his spare time, he likes to listen Indian classical/semi-classical music, watching tv, playing cricket/badminton and hang out with his family.


  • 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.

  • 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

  • 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.

  • 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 ?

  • 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.

  • 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.

  • 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.

  • 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?

  • Also,

    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?

  • Nilandan,

    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

  • 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 -usdbrw -p=$sdbrw_pw,D=temp,t=xyz –dest -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 -usdbrw -p=$sdbrw_pw,D=temp,t=xyz –file xyz –where “1=1”
    DBI connect(‘temp;;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.

Comments are closed