Percona Toolkit Utilities for MySQL: Archiving, Config Comparison, Replication Lag, Variable Advice, and Debugging

Percona Toolkit provides a collection of command‑line utilities that help MySQL DBAs automate routine tasks.

pt‑archiver can move rows that meet a condition to another database or file and optionally delete the original rows. Example commands:

# archive to another DB and delete original rows
pt-archiver --source h=172.20.134.1,P=5722,u=repl,p=repl,D=sbtest,t=sbtest1,A=utf8 --where "id<=1000" --dest h=172.20.134.3,P=5722,u=dba,p=dba,D=sbtest,t=sbtest1,A=utf8

# archive to another DB without deleting
pt-archiver --source h=172.20.134.1,P=5722,u=repl,p=repl,D=sbtest,t=sbtest1,A=utf8 --no-delete --where "id<=1000" --dest h=172.20.134.3,P=5722,u=dba,p=dba,D=sbtest,t=sbtest1,A=utf8

# archive to a file
pt-archiver --source h=172.20.134.1,P=5722,u=repl,p=repl,D=sbtest,t=sbtest1,A=utf8 --file=/tmp/archive.save --where "id<=1000"

Loading the archived file can be done with a standard LOAD DATA INFILE statement.

pt‑config‑diff compares MySQL runtime variables or configuration files between servers, showing differences such as general_log_file or max_allowed_packet . Example:

# compare runtime variables
pt-config-diff h=172.20.134.1,P=5722,u=repl,p=repl h=172.20.134.3,P=5722,u=dba,p=dba --report-width 200

pt‑heartbeat measures replication lag more accurately by inserting timestamps on the master and comparing them on the replica. Example usage:

# start heartbeat on master
pt-heartbeat -D delay_checker --create-table --interval=5 --update -h 172.20.134.1,P=5722,u=repl,p=repl

# check lag from replica
pt-heartbeat -D delay_checker --check 172.20.134.2,P=5722,u=repl,p=repl

pt‑variable‑advisor scans MySQL variables and suggests improvements, emitting warnings and notes such as adjusting innodb_log_buffer_size or enabling binary log purge.

pt‑pmp uses gdb to capture and merge stack traces from a running mysqld process, helping diagnose hard‑to‑find crashes. Example command:

# capture stack traces
pt-pmp --binary /path/to/bin/mysqld --pid 11788

The article concludes that leveraging these tools can reduce DBA workload, and provides a quick installation snippet for Percona Toolkit.

wget https://www.percona.com/downloads/percona-toolkit/3.1.0/binary/tarball/percona-toolkit-3.1.0_x86_64.tar.gz
tar -xvf percona-toolkit-3.1.0_x86_64.tar.gz -C /usr/local