Online Schema Changes with pt-online-schema-change in MySQL

Introduction

Performing schema changes on a live MySQL database can be a challenging task, especially when dealing with large tables or high-traffic environments. Traditional methods of altering tables can lead to significant downtime, locking issues, and potential data loss. This is where pt-online-schema-change, a tool from the Percona Toolkit, comes into play. It allows for non-blocking schema changes, enabling you to alter your tables without significant downtime.

In this tutorial, we will explore how to use pt-online-schema-change to perform online schema changes in MySQL. This guide is intended for users who are familiar with MySQL and have some experience with database administration but may not have used pt-online-schema-change before.

Prerequisites

Before we begin, ensure you have the following:

A MySQL database with administrative access.
Basic knowledge of MySQL command-line operations.
A backup of your database. While pt-online-schema-change is designed to minimize risk, having a backup is always a good practice.

Understanding pt-online-schema-change

pt-online-schema-change is part of the Percona Toolkit, a collection of advanced command-line tools for MySQL and MariaDB. The tool works by creating a shadow copy of the table to be altered, applying the changes to the shadow table, and then swapping the original table with the altered table. This process ensures minimal disruption to database operations.

Key features include:

Non-blocking operation: Changes are applied without locking the entire table.
Data integrity: Ensures data consistency throughout the operation.
Customizable: Supports various options to control the behavior of the schema change.

Installing Percona Toolkit

To install Percona Toolkit, you can use your package manager of choice. Below are the installation steps for different operating systems.

On Ubuntu/Debian

sudo apt-get update
sudo apt-get install percona-toolkitCode language: Bash (bash)

On CentOS/RHEL

sudo yum install percona-toolkitCode language: Bash (bash)

On macOS

brew install percona-toolkitCode language: Bash (bash)

Alternatively, you can download and compile it from the source if you prefer.

Basic Usage of pt-online-schema-change

The basic syntax of pt-online-schema-change is as follows:

pt-online-schema-change --execute --alter "SCHEMA CHANGE" D=database,t=tableCode language: Shell Session (shell)

--execute: Executes the schema change. Without this option, the tool will run in dry-run mode.
--alter: Specifies the schema change to be applied.
D=database,t=table: Specifies the database and table to be altered.

Example

Let’s start with a simple example where we add a new column to a table.

pt-online-schema-change --execute --alter "ADD COLUMN new_col INT" D=mydb,t=mytableCode language: Shell Session (shell)

This command adds a new column new_col to the mytable table in the mydb database.

Performing Schema Changes

Adding Columns

To add a column, use the ADD COLUMN clause in the --alter option.

pt-online-schema-change --execute --alter "ADD COLUMN age INT" D=mydb,t=usersCode language: Shell Session (shell)

Modifying Columns

To modify an existing column, use the MODIFY COLUMN clause.

pt-online-schema-change --execute --alter "MODIFY COLUMN age BIGINT" D=mydb,t=usersCode language: Shell Session (shell)

Dropping Columns

To drop a column, use the DROP COLUMN clause.

pt-online-schema-change --execute --alter "DROP COLUMN age" D=mydb,t=usersCode language: Shell Session (shell)

Adding Indexes

To add an index, use the ADD INDEX clause.

pt-online-schema-change --execute --alter "ADD INDEX idx_age (age)" D=mydb,t=usersCode language: Shell Session (shell)

Dropping Indexes

To drop an index, use the DROP INDEX clause.

pt-online-schema-change --execute --alter "DROP INDEX idx_age" D=mydb,t=usersCode language: Shell Session (shell)

Advanced Usage

Chunk Size Management

pt-online-schema-change processes the table in chunks to minimize locking. The default chunk size is 1000 rows. You can adjust this using the --chunk-size option.

pt-online-schema-change --execute --alter "ADD COLUMN age INT" D=mydb,t=users --chunk-size 2000Code language: Shell Session (shell)

Throttle Options

To control the tool’s impact on your server, use the --max-load and --critical-load options. These options specify the maximum permissible load on the server before the tool pauses.

pt-online-schema-change --execute --alter "ADD COLUMN age INT" D=mydb,t=users --max-load Threads_running=25 --critical-load Threads_running=50Code language: Shell Session (shell)

Specifying Where Clauses

You can limit the rows to be processed using the --where option.

pt-online-schema-change --execute --alter "ADD COLUMN age INT" D=mydb,t=users --where "age IS NULL"Code language: Shell Session (shell)

Handling Foreign Keys

When dealing with foreign keys, use the --alter-foreign-keys-method option to specify how foreign keys should be handled. The default method is auto.

pt-online-schema-change --execute --alter "ADD COLUMN age INT" D=mydb,t=users --alter-foreign-keys-method autoCode language: Shell Session (shell)

Custom Triggers

You can create custom triggers to perform specific actions during the schema change.

pt-online-schema-change --execute --alter "ADD COLUMN age INT" D=mydb,t=users --before-trigger "CREATE TRIGGER..."Code language: Shell Session (shell)

Monitoring and Troubleshooting

Monitoring Progress

pt-online-schema-change provides progress output by default. You can also monitor progress by querying the information_schema tables.

SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;Code language: Shell Session (shell)

Handling Errors

If an error occurs, pt-online-schema-change will stop and provide an error message. Review the message to diagnose the issue.

Cleaning Up

If the tool is interrupted, it may leave behind temporary tables. You can clean these up using the --cleanup option.

pt-online-schema-change --execute --alter "ADD COLUMN age INT" D=mydb,t=users --cleanupCode language: Shell Session (shell)

Best Practices

Test in a staging environment: Always test schema changes in a staging environment before applying them to production.
Monitor server load: Use the --max-load and --critical-load options to prevent the tool from overwhelming your server.
Backup your data: Ensure you have a recent backup of your data before performing schema changes.
Read the documentation: Familiarize yourself with the official documentation for detailed information and advanced options.

Conclusion

pt-online-schema-change is a powerful tool for performing online schema changes in MySQL. By following this tutorial, you should now be equipped to use pt-online-schema-change to alter your database schemas with minimal downtime and disruption. Remember to always test your changes in a staging environment and monitor your server load during the process. With these best practices, you can confidently perform schema changes on your live databases.

For more information, refer to the official Percona Toolkit documentation.