Performing schema changes on production databases comes with significant overhead, such as locking entire tables and potentially causing replication lag in master-slave architectures. For MySQL and its variants, there's a powerful tool that mitigates these issues: pt-online-schema-change.
What is pt-online-schema-change?
pt-online-schema-change is a tool that alters a table's structure without blocking reads or writes, making it ideal for use in production environments.
How It Works
Table Copying: It creates an empty copy of the table with the desired alterations.
Data Migration: It copies rows from the original table to the new one in small, manageable chunks.
Real-time Updates: During the copy process, any modifications to the original table are reflected in the new table using triggers.
Atomic Swap: Once copying is complete, it uses an atomic RENAME TABLE operation to switch the old and new tables.
Cleanup: Finally, it drops the original table.
Key Features and Safety Measures
Chunk-based Processing: Data is copied in small chunks, optimized for performance (configurable with --chunk-time).
Trigger Mechanism: Ensures data consistency during the migration process.
Foreign Key Handling: Supports methods to update foreign key references after the schema change.
Safety Checks:
Requires a PRIMARY KEY or UNIQUE INDEX in most cases.
Detects and respects replication filters.
Pauses operations if replicas lag behind.
Monitors server load and can pause or abort if thresholds are exceeded.
Sets conservative lock wait timeouts to minimize disruption.
Careful handling of foreign key constraints.
Considerations
By default, it doesn't modify the table unless the --execute option is specified.
It may rename foreign keys and indexes slightly to avoid naming collisions.
Existing triggers on the table will prevent the tool from working.
Example Usage
#!/bin/bash
USERNAME="root"
PASSWORD="your_password_here"
HOST="localhost"
DATABASE="critical_db"
TABLE="critical_table"
ALTER_STATEMENT="ADD COLUMN guest VARCHAR(45), ADD COLUMN name VARCHAR(100)"
if [[ "$1" == "--execute" ]]; then
EXECUTE=true
else
EXECUTE=false
fi
echo "Performing dry run..."
pt-online-schema-change \
--user="$USERNAME" \
--password="$PASSWORD" \
--host="$HOST" \
D="$DATABASE",t="$TABLE" \
--alter="$ALTER_STATEMENT" \
--preserve-triggers \
--dry-run
if [ "$EXECUTE" = true ]; then
echo "Applying changes..."
pt-online-schema-change \
--user="$USERNAME" \
--password="$PASSWORD" \
--host="$HOST" \
D="$DATABASE",t="$TABLE" \
--alter="$ALTER_STATEMENT" \
--preserve-triggers \
--execute
fi
To use this script:
Save it to a file (e.g., alter_table.sh)
Make it executable: chmod +x alter_table.sh
Run a dry run: ./alter_table.sh
Execute the changes: ./alter_table.sh --execute
Conclusion
pt-online-schema-change offers a robust solution for performing schema changes in production environments, minimizing downtime and reducing the risks associated with traditional ALTER TABLE operations.
