pt-online-schema-change is the magic trick that lets you reshape your MySQL tables without making users wait.
Let’s see it in action. Imagine you have a users table and need to add an index to speed up lookups by email.
-- Original table structure
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Data to populate
INSERT INTO users (username) VALUES ('alice'), ('bob'), ('charlie');
Now, you want to add an index on email (which doesn’t exist yet). Doing this directly on a large table:
ALTER TABLE users ADD INDEX idx_email (email);
would lock the table for the duration of the ALTER operation. If your users table has millions of rows, that’s minutes, maybe hours, of downtime.
pt-online-schema-change sidesteps this by creating a new, empty table with the desired schema, then copying data in chunks from the original to the new table. While this is happening, it uses triggers to capture any changes made to the original table and apply them to the new one in near real-time. Once all data is copied and synced, it swaps the new table with the old one.
Here’s how you’d use pt-online-schema-change to add that index:
First, install pt-online-schema-change. It’s part of the Percona Toolkit.
# On Debian/Ubuntu
sudo apt-get update
sudo apt-get install percona-toolkit
# On CentOS/RHEL
sudo yum install percona-toolkit
Now, run the command. We’ll need to specify the database, the table to alter, and the ALTER statement.
pt-online-schema-change \
--execute \
--alter "ADD INDEX idx_email (email)" \
h=localhost,D=your_database_name,u=your_mysql_user,p=your_mysql_password \
--print
Let’s break this down:
--execute: This flag tellspt-online-schema-changeto actually perform the schema change. Without it, it will just show you the SQL it would run.--alter "ADD INDEX idx_email (email)": This is the exactALTER TABLEstatement you want to apply.h=localhost,D=your_database_name,u=your_mysql_user,p=your_mysql_password: These are your database connection details. Replaceyour_database_name,your_mysql_user, andyour_mysql_passwordwith your actual credentials. If you’re connecting to a remote host, changelocalhostto the server’s IP or hostname.--print: This is useful for seeing theALTERstatement before execution, but--executewill run it.
The tool will create a new table (e.g., your_database_name.users_new), copy data in small batches (defaulting to 1000 rows at a time), and set up triggers on the original users table to capture INSERT, UPDATE, and DELETE operations. Once the copy is complete and the new table is in sync, it will perform a quick RENAME TABLE operation, which is atomic and effectively instantaneous.
The users table will now have the idx_email index.
This process is highly configurable. You can control the chunk size (--chunk-size), the delay between chunks (--max-lag), how aggressively it tries to catch up (--critical-load), and more. The goal is to keep the load on the master database as low as possible while the copy is in progress.
One of the most important aspects of pt-online-schema-change is its ability to handle concurrent writes. It doesn’t just copy data; it actively monitors the original table for changes and applies them to the new table. This is achieved through triggers. For every INSERT, UPDATE, or DELETE on the original table, a corresponding INSERT, UPDATE, or DELETE is executed on the new table. This ensures that by the time the tool performs the final table swap, the new table is an exact replica of the old one.
What most people don’t realize is that the triggers created by pt-online-schema-change are actually a pair: one to capture changes and write them to a temporary table, and another to apply those changes from the temporary table to the final destination table. This two-stage approach helps to buffer writes and prevent the triggers themselves from becoming a bottleneck, especially under heavy write loads. It allows the tool to manage the catch-up process more gracefully and reduces the risk of the slave lagging too far behind the master during the migration.
After the RENAME TABLE operation, the original table (users) is dropped, and the new table (users_new) is renamed to users. The triggers are also dropped.
The next thing you’ll likely encounter is the need to manage the temporary tables and triggers that pt-online-schema-change creates if something goes wrong or if you need to clean up manually.