Shulou(Shulou.com)05/31 Report--

This article mainly introduces the Percona-Toolkit pt-archiver how to delete historical data, the article is very detailed, has a certain reference value, interested friends must read it!

Pt-archiver is a component of the Percona-Toolkit toolset and is primarily a tool for archiving and purging MySQL table data. It can archive data to another table or to a file. Pt-archiver does not affect the query performance of OLTP transactions in the process of purging table data. For data archiving, it can be archived to another table on another server or to a file, which can be loaded with LOAD DATA INFILE, which is similar to the incremental deletion of table history data.

For the archiving of online historical data, I directly use mysqldump to restore it, but delete the historical data after archiving. It is much more convenient to write a shell script through pt-archiver.

The parameter description of pt-archiver-help mainly describes what can be used to delete historical data.

1.-progress prints progress information per number of lines

2.-- limit limits the number of rows returned by select

3.-- sleep specifies the sleep time of select statements

4.-- txn-size specifies how many rows commit a transaction

5.-- bulk-delete deletes each row block in batch with a single DELETE statement. This statement deletes each line between the first and last lines of the block, implying-- commit-each

6.-- dry-run prints the query and exits after doing nothing

Create a new table employees_ptarc test:

CREATE TABLE `employees_ ptarc` (

`id`int (11) NOT NULL AUTO_INCREMENT

`vint` int (11) DEFAULT NULL

`v _ string` varchar (50) DEFAULT NULL

`s_ string` char (20) NOT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Create data through stored procedures:

Delimiter $$

CREATE PROCEDURE i_employees_ptarc (IN row_num INT)

BEGIN

DECLARE i INT DEFAULT 0

WHILE i

< row_num DO INSERT INTO employees_ptarc (v_int, v_string, s_string) VALUES ( floor(1 + rand() * 1000000), substring( MD5(RAND()), 1, floor(1 + rand() * 20) ), substring(MD5(RAND()), 1, 20) ) ; SET i = i + 1 ; END WHILE ; END$$ delimiter ; call i_employees_ptarc(400000); # pt-archiver --source h=localhost,P=7777,u=root,p='Asd.123@#',D=yoon,t=employees_ptarc,A=utf8 --purge --charset=utf8 --where "id < 400000" --progress=100 --txn-size=100 --limit=100 --bulk-delete --statistics --dry-run SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id < 400000) AND (`id` < '400000') ORDER BY `id` LIMIT 100 SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id < 400000) AND (`id` < '400000') AND ((`id` >

=?)) ORDER BY `id` LIMIT 100

DELETE FROM `yoon`.`cake _ ptarc` WHERE (`id` > =?) AND (`id` =?)) AND ((`id`)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.