Percona Toolkit Practical Usage Guide

Percona Toolkit Operations Guide

Table of Contents

pt-online-schema-change

Operational Principle

  1. If foreign keys exist, the tool detects tables related to foreign keys based on the alter-foreign-keys-method parameter value and performs appropriate handling. Without specifying --alter-foreign-keys-method=rebuild_constraints, the tool will not execute.
  2. Creates a new table (table_new) with the same structure as the source table, then executes ALTER to modify the temporary table structure.
  3. Creates three triggers on the original table: INSERT, DELETE, and UPDATE triggers (used to同步 changes made to the source table during the copy process to the new table).
  4. Copies data from the source table to the new table. During the copy process, any write operations on the source table are also applied to the temporary table.
  5. Modifies child tables with foreign key relationships based on the modified data.
  6. Renames the source table to old, renames the new table to the original name, and deletes the old table.
  7. Removes the triggers.

Prerequisites

  1. The target table must have a primary key or unique index, otherwise an error occurs.
  2. The table must not have existing triggers, otherwise an error occurs.

Advantages

  • Reduces the risk of master-slave replication lag
  • Alllows speed and resource limiting to prevent excessive MySQL load

Recommendation

Perform operations during off-peak hours to minimize business impact.

Usage Overview

pt-online-schema-change --host=192.168.1.100 --port=3306 --user=dbadmin --password='SecurePass123' D=production_db,t=orders_table --alter="modify order_id bigint(20) COMMENT 'Order ID';" --critical-load="Threads_running=200" --sleep=1 --charset=utf8mb4 --check-slave-lag="192.168.1.2,192.168.1.3" --check-interval=1 --execute

Key Parameters

Parameter Description
--dry-run Creates and modifies the new table without creating triggers or replacing the original table. Mutually exclusive with --execute
--execute Performs the actual operation, creating triggers to同步 new changes to the new table
--critical-load Monitors specified status variables (default: Threads_running) before and after each chunk operation. Terminates if threshold exceeded
--charset Sets connection character set (e.g., utf8mb4)
--check-slave-lag Checks specified slave for replication lag
--check-replication-filters Exits if replication filters are detected
--set-vars Sets MySQL variable values
--sleep Seconds to wait between chunk operations
--[no]drop-old-table Whether to drop the old table after rename (use no- prefix to keep)
--chunk-size Number of rows per chunk (default: 1000)
--max-load Pauses operation when status variables exceed threshold (vs. --critical-load which terminates)
--max-lag Pauses replication if slave lag exceeds this value

Adding Columns Online

Original SQL:
alter table inventory add column status VARCHAR(10);

Using PT tool:
pt-online-schema-change -h dbserver.example.com -P 3306 -u admin -p 'SecurePass' --socket /data/mysql/socket/mysql.sock --alter "add column copyright_status VARCHAR(10);" D=production_db,t=inventory_table --execute --print --no-check-replication-filters --charset=utf8mb4 --no-check-unique-key-change --max-load="Threads_running=30" --critical-load="Threads_running=50" --recursion-method=none;

Note: Adding --no-drop-old-table preserves the original table as table_name_old instead of deleting it.

Creating Indexes Online

Original SQL:
ALTER TABLE inventory ADD INDEX idx_status (status);

Using PT tool:
pt-online-schema-change -h dbserver.example.com -P 3306 -u admin -p 'SecurePass' --socket /data/mysql/socket/mysql.sock --alter "ADD INDEX idx_copyright_status(copyright_status);" D=production_db,t=inventory_table --execute --print --no-check-replication-filters --charset=utf8mb4 --no-check-unique-key-change --max-load="Threads_running=30" --critical-load="Threads_running=50" --recursion-method=none;

Modifying Table Columns Online

Original SQL:
alter table orders modify column id int(20) NOT NULL AUTO_INCREMENT COMMENT 'ID';

Using PT tool:
pt-online-schema-change -h dbserver.example.com -P 3306 -u admin -p 'SecurePass' --alter "modify column id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID';" D=analytics_db,t=orders --no-drop-old-table --execute --print --no-check-replication-filters --charset=utf8mb4 --no-check-unique-key-change --max-load="Threads_running=30" --critical-load="Threads_running=50" --recursion-method=none;

Foreign Key Handling

Problem Scenario

After adding a column to Table A using pt-online-schema-change, foreign key errors occurred. The child table B's foreign key was pointing to a non-existent table _A_old:

Cannot add or update a child row: a foreign key constraint fails (db.B, CONSTRAINT B_ibfk_2 FOREIGN KEY (A_id) REFERENCES _A_old (id))

--alter-foreign-keys-method Options

Method Description
auto Automatically chooses between rebuild_constraints and drop_swap
rebuild_constraints Uses ALTER TABLE to drop and re-add foreign keys pointing to the new table. Safest but slower for large child tables.
drop_swap Disables foreign key checks, drops old table before rename. Faster but riskier - table briefly does not exist during the swap window.
none All foreign keys point to the non-existent old table name after rename. Useful when you want to handle foreign keys manually.

Resolution

Since foreign key relationships follow table renames, the fix is straightforward:

-- Step 1: Rename current table to _A_old
alter table A rename _A_old;

-- Step 2: Rename back to original name (foreign keys will now point correctly)
alter table _A_old rename A;

Recommendation: Change the default configuration from none to drop_swap to avoid foreign key reference issues.

pt-archiver

pt-archiver is part of the Percona Toolkit主要用于:

  • Data deletion
  • Data archiving
  • Data migration between MySQL instances

Data Cleanup

For large tables, PT tools provide efficient deletion capabilities, especially when the table has a primary key.

Method 1 - Using PT Tool:

timestamp_start=`date +"%Y-%m-%d_%H:%M:%S"`
echo "Starting cleanup at $timestamp_start"

pt-archiver --source h=localhost,D=archive_db,t=content_table,u=admin,p="SecurePass" \
  --where 'record_id not in(select record_id from dbname.reference_table)' \
  --purge --limit=10000 --no-check-charset --txn-size 10000 \
  --bulk-delete --statistics --progress 10000 \
  --skip-foreign-key-checks --primary-key-only

timestamp_end=`date +"%Y-%m-%d_%H:%M:%S"`
echo "Completed at $timestamp_end"

Method 2 - Using Stored Procedure:

delimiter $$
drop procedure if exists proc_purge_old_records;
create procedure proc_purge_old_records()
begin
    set sql_log_bin=0;
    purge_loop: loop
        delete from legacy_table where id not in(select id from current_table) limit 5000;
        if row_count() < 5000 then
            leave purge_loop;
        end if;
        select sleep(1);
    end loop;
end $$
delimiter ;

-- Execute in background
nohup sh cleanup_script.sh > cleanup.log &

Data Archiving

pt-archiver --source h=127.0.0.1,P=3306,u=archiver,p='ArchivePass',D=analytics_db,t=log_events \
  --charset 'UTF8' --dest h=127.0.0.1,P=3306,u=archiver,p='ArchivePass',D=analytics_db,t=log_events_archive \
  --no-version-check --where "event_id <= 4383363" --statistics \
  --no-delete --bulk-insert --progress 5000 --limit=500 --txn-size=100 \
  >> archive_log.log &

Parameter Reference

Parameter Description
--limit Rows to fetch per statement (default: 1000)
--txn-size Rows per transaction commit
--where WHERE clause condition
--progress Output progress every N rows
--statistics Display execution statistics
--charset Character set (e.g., UTF8)
--bulk-delete Batch delete from source (efficient for large deletes)
--bulk-insert Batch insert into destination (uses LOAD DATA LOCAL INFILE)
--replace Use REPLACE INTO instead of INSERT
--sleep Seconds to sleep between batches
--file Export to file (specify path)
--purge Delete matching records from source
--no-version-check Required for some cloud RDS instances
--analyze Run ANALYZE after operation (d=dest, s=source)

Known Bug: max(id) Record Not Migrated

There's a known issue where the record with the maximum ID is not migrated. To fix:

vim /usr/bin/pt-archiver +6285

# Change this line:
$first_sql .= " AND ($col < " . $q->quote_val($val) . ")";

# To this:
$first_sql .= " AND ($col <= " . $q->quote_val($val) .")";

Usage Examples

Delete Old Data (No Character Set Required)

pt-archiver \
--source h=localhost,u=admin,p=SecurePass,P=3306,D=test_db,t=test_table \
--no-check-charset --where 'created_at<=2023-01-01' --limit 10000 --txn-size 1000 --purge

Copy to Another MySQL Instance Without Deleting Source

pt-archiver \
--source h=localhost,u=admin,p=SecurePass,P=3306,D=test_db,t=source_table \
--dest h=192.168.2.12,P=3306,u=archiver,p=ArchivePass,D=test_db,t=backup_table \
--progress 5000 --where 'category_id<=125' \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --no-delete

Copy and Delete from Source

pt-archiver \
--source h=localhost,u=admin,p=SecurePass,P=3306,D=test_db,t=source_table \
--dest h=192.168.2.12,P=3306,u=archiver,p=ArchivePass,D=test_db,t=historical_table \
--progress 5000 --where "created_date <'2023-05-01 00:00:00'" \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --bulk-delete

Copy with Bulk Insert (Faster)

pt-archiver \
--source h=localhost,u=archiver,p=ArchivePass,P=3306,D=test_db,t=source_table \
--dest h=192.168.2.12,P=3306,u=archiver,p=ArchivePass,D=test_db,t=historical_table \
--progress 5000 --where "created_date <'2023-05-01 00:00:00'" \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --no-delete --bulk-insert

Export to File

pt-archiver \
--source h=10.0.20.26,u=admin,p=SecurePass,P=3306,D=test_db,t=test_table \
--file '/tmp/export_data.txt' \
--progress 5000 --where 'id<12000' \
--no-delete --statistics --charset=UTF8 --limit=10000 --txn-size 1000

Export and Delete from Database

pt-archiver \
--source h=10.0.20.26,u=admin,p=SecurePass,P=3306,D=test_db,t=test_table \
--file '/tmp/export_data.txt' \
--progress 5000 --where 'id<12000' \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --purge

pt-query-digest - Slow Query Analysis

Basic Usage

pt-query-digest -uroot -S /tmp/mysql.sock -p'RootPass' /var/log/mysql/slow_query.log >> /tmp/slow_analysis.log

# Or simpler form:
pt-query-digest /var/log/mysql/slow_query.log >slow_report.log

Key Analysis Points

  • Query execution frequency - how many times per hour
  • Average execution time per query
  • Execution plan analysis - look for full table scans
  • Time range of analysis
  • Data volume considerations
  • Index usage - identify fields needing indexes, check for high cardinality

pt-heartbeat - Replication Lag Monitoring

Checking Replication Delay

pt-heartbeat -uroot -p'RootPass' --host=10.25.150.200 -D monitoring_db --master-server-id=2013306 --check

pt-table-checksum - Data Consistency Verification

For detailed information on data consistency checking, please refer to the comprehensive documentation on checksum operations.

pt-slave-restart - Replication Error Recovery

Overview

pt-slave-restart monitors MySQL replication slaves and attempts to restart replication after errors. It intelligently checks slaves with exponentially varying sleep intervals.

Important: While this tool can help skip errors, it should not be relied upon as a permanent fix. Identify and address the root cause of frequent replication errors.

Important GTID Consideration

Starting from Percona Toolkit 2.2.8, GTID replication is supported. However, with multi-threaded replication (slave_parallel_workers > 0), pt-slave-restart cannot skip transactions because it cannot determine which specific slave thread failed.

Configuration for GTID Replication

Before using pt-slave-restart with GTID replication, set parallel workers to 0:

-- Check current setting
show variables like '%slave_parallel_workers%';

-- Disable parallel replication temporarily
set global slave_parallel_workers=0;

After successful error recovery, restore the setting:

set global slave_parallel_workers=8;

Parameter Reference

Parameter Description
--always Never stop slave thread (even manual STOP)
--ask-pass Prompt for password (more secure)
--error-numbers Specific error numbers to skip (comma-separated)
--error-text Skip errors matching specific text
--log Output to log file
--recurse Execute from master, monitor slaves
--runtime Tool execution duration (e.g., 30s, 2m, 1h)
--slave-user Slave user (used when running from master)
--skip-count Number of errors to skip per iteration (default: 1)
--master-uuid For cascading replication, specify which master's errors to skip
--until-master Stop at specified master log position (format: "file:pos")

Practical Example

In a test environment, a DDL statement increasing a field length was executed on master, causing the slave to fail because the field length was between original and target values.

Environment: MySQL 5.7.19 with GTID dual-master replication

Resolution Steps:

  1. Try manually reverting field length to original value and restarting slave - Failed with error 1677
  2. Manually skip the problematic transaction:
mysql> stop slave;
mysql> set gtid_next="d7c35015-9dd1-11e7-b70d-005056aa19c3:51629";
-- Note: With dual-master GTID, show master status shows two GTIDs (one from each master)
-- Only specify the master's GTID

mysql> begin; commit;
-- Empty transaction to update GTID

mysql> set gtid_next='automatic';
-- Required: after setting a specific GTID, must reset to automatic mode

mysql> start slave;

Bulk Error Skipping Methods

Method 1: Using slave-skip-errors in my.cnf (requires restart)

Method 2: Using pt-slave-restart (recommended)

pt-slave-restart --user=root --password=SecurePass --socket=/data/mysql/3306/tmp/mysql.sock --error-numbers=1677

Additional examples:

-- Skip specific error numbers
pt-slave-restart -h192.168.112.128 -P3306 -uroot -pSecurePass --sleep=11

-- Skip duplicate key errors
pt-slave-restart --error-numbers=1062 -h localhost -uroot -pmysql -S /tmp/mysql.sock

Tags: MySQL percona-toolkit Database-Administration pt-online-schema-change pt-archiver

Posted on Sun, 06 Sep 2026 16:39:16 +0000 by kaspari22

Hot Tags

©2026 Freaks City