Online DDL Tool pt-osc: Principles, Usage, and Comparison

Online DDL Tool: pt-osc

For MySQL online DDL there are three mainstream tools: native online DDL, pt-osc (online-schema-change), and gh‑ost. This article focuses on the usage of pt‑osc and a brief comparison of the three tools.

1. Principle and Limitations

1.1 Principle

Create an empty table with the same structure as the original, suffix _new.

Alter the structure of the new table.

Add three triggers (delete/update/insert) on the original table to copy statements to the new table.

Copy data from the original table to the new table in chunks.

Rename the original table to _old and rename the new table to the original name, then drop the old table.

Drop the triggers.

1.2 Limitations

The original table must have a primary key or unique index.

Rename clause cannot be used.

Columns cannot be renamed by dropping and adding.

Adding a NOT NULL column requires a default value.

When dropping foreign keys, the constraint name must be prefixed with an underscore.

CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)

Must specify --alter "DROP FOREIGN KEY _fk_foo".

2. Usage

2.1 Syntax pt-online-schema-change [OPTIONS] DSN DSN is a key=value string (e.g., host, port, user, password, database, table).

Installation

# Install yum repository
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# Install Percona Toolkit
yum install percona-toolkit -y

Example: Change column type from CHAR(20) to VARCHAR(200)

GRANT SELECT, INSERT, UPDATE, DELETE, \
    CREATE, DROP, PROCESS, REFERENCES, \
    INDEX, ALTER, SUPER, LOCK TABLES, \
    REPLICATION SLAVE, TRIGGER \
ON *.* TO 'ptosc'@'%'

pt-online-schema-change \
    --user=ptosc --password=ptosc \
    --host=127.0.1.1 --port=3306 \
    --database=testdb --table=sbtest1 \
    --alter='modify c varchar(200) not null default ""' \
    --dry-run

Run a dry‑run first to verify the process.

# pt-online-schema-change --print --statistics \
    --progress time,30 --preserve-triggers --user=ptosc \
    --password=ptosc --alter 'modify c varchar(200) not null default ""' \
    h=127.0.1.1,P=3306,D=testdb,t=sbtest1 \
    --pause-file=/tmp/aa.txt --max-load=threads_running=100,threads_connected=200 \
    --critical-load=threads_running=1000 --chunk-size=1000 \
    --alter-foreign-keys-method auto --dry-run

After confirming, replace --dry-run with --execute to apply the change.

3. Online DDL Tool Comparison

3.1 Native MySQL Online DDL

Since MySQL 5.6, native online DDL supports in‑place changes. Two algorithms exist:

Copy : creates a temporary table, locks the original, copies data, then swaps tables (non‑online).

Inplace : modifies the table without a temporary copy. It can be rebuild (requires rebuilding the clustered index) or no‑rebuild (metadata‑only changes).

3.2 gh‑ost

gh‑ost creates a ghost table ( _xxx_gho) and a changelog table ( _xxx_ghc), copies data, applies binlog events, and finally swaps tables. It can run on a replica to reduce load on the primary.

4. How to Choose

If MySQL version < 5.6, use pt‑osc or gh‑ost.

If version ≥5.6 and the DDL requires a copy table, use pt‑osc or gh‑ost.

For inplace no‑rebuild DDL, use native online DDL.

For inplace rebuild DDL, choose pt‑osc or gh‑ost when replication lag is sensitive; otherwise native online DDL.

When minimizing impact on the primary is critical, prefer gh‑ost.

5. pt‑osc Parameter Dictionary

Common Options --dry-run: Test without making changes. --execute: Perform the DDL. --user, -u: MySQL username. --password, -p: MySQL password. --host, -h: Hostname. --port, -P: Port number. --socket: Socket file. --ask-pass: Prompt for password. --alter "string": Alter statement without the ALTER TABLE keyword.

Output Control --print: Print the SQL statements. --progress: Show copy progress. --statistics: Print statistics.

Foreign‑Key Handling

--alter-foreign-keys-method "auto|rebuild_constraints|drop_swap"

: How to update foreign keys.

Other options include --chunk-size, --max-load, --critical-load, --preserve-triggers, --new-table-name, etc., each controlling specific aspects of the migration process.