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

发布时间：2020-09-02 05:01:06 所属栏目：MySql 来源：互联网

导读：原文地址：https://www.percona.com/blog/2013/08/12/want-to-archive-tables-use-pt-archiver/Percona Toolkit’spt-archiveris one of the best utilities to archive the records from large tables to another tablesorfiles. One interesting thing is t

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

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 causespt-archiverto 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-archiversleeps 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).

pt-archiverworks with(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.

pt-archiveris 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.for more info on that.

Bugs related to pt-archiver:

More details about pt-archiver:

（编辑：商洛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

原文地址：https://www.percona.com/blog/2013/08/12/want-to-archive-tables-use-pt-archiver/

Percona Toolkit’sis one of the best utilities to archive the records from large tables to another tablesorfiles. One interesting thing is thatpt-archiveris a read-write tool. It deletes data from the sourceby 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-deleteoption if you’re not sure about. The purpose of this script is mainly to archiveold 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 doespt-archiverselect records to archive?

Pt-archiveruses the index to select records from the table.The index is used to optimize repeated accesses to the table.Pt-archiverremembers 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 runpt-archiverwith a specific index you can use the“-i”option in–sourceDSN options. The“-i”optiontellspt-archiverwhich index it should scan to archive. This appears in aFORCE INDEXorUSE INDEXhint in theSELECTstatements that are used to fetch rows to archive. If you don’t specify anything,pt-archiverwill auto-discover a good index,preferring a PRIMARY KEYif one exists. Most of the time,without“-i”option,pt-archiverworks well.

How to run pt-archiver?

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