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

This post is about how to use percona's pt-archiver tool in mysql. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

DBAs or OPS personnel often need to archive and clean data regularly. We can use percona's pt-archiver tool to complete this function, making data archiving convenient and simple.

An example of archived data:

The original table structure is:

mysql>desc test.t1;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| a | int(11) | YES | MUL | NULL | |

| b | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql>show create table test.t1 \G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`id` int(11) NOT NULL,

`a` int(11) DEFAULT NULL,

`b` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `a` (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

Archive to File:

# pt-archiver --source h=192.168.140.51,u=root,p=123456,P=3306,D=test,t=t1 --file '/tmp/test.txt' --progress 500 --no-delete --statistics --where '1=1' --txn-size 1000 --no-check-charset

TIME ELAPSED COUNT

2019-06-19T16:14:48 0 0

2019-06-19T16:14:48 0 500

2019-06-19T16:14:49 0 1000

2019-06-19T16:14:49 0 1000

Started at 2019-06-19T16:14:48, ended at 2019-06-19T16:14:49

Source: D=test,P=3306,h=192.168.140.51,p=..., t=t1,u=root

SELECT 1000

INSERT 0

DELETE 0

Action Count Time Pct

select 1001 0.6883 72.33

print_file 1000 0.0149 1.57

commit 2 0.0004 0.04

other 0 0.2480 26.06

Note:

Tables for pt-archiver operations must have a primary key.

--where '1=1' condition is to export all data, if you want to export some data, you can set where condition, for example--where 'b>500'

--no-check-charset Set not to check character set, otherwise an error will be reported:

Character set mismatch: --source DSN uses latin1, table uses utf8mb4. You can disable this check by specifying --no-check-charset.

Common parameters are:

Number of rows to fetch and archive per statement.

--txn-size 1000 Set 1000 rows to commit once per transaction, Number of rows per transaction.

--where '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.