How to Modify MySQL Tables Online Without Downtime Using pt-online-schema-change

Solution Idea

1. Create a new table with the desired schema.

2. Add triggers on the old table to replicate updates to the new table.

3. Copy existing data from the old table to the new one.

4. After synchronization, rename the tables to swap them.

5. Drop the old table and its triggers.

Implementation

Percona provides the pt-online-schema-change tool, which performs online schema modifications without blocking reads and writes.

Usage example:

pt-online-schema-change --user=root --password=111111 --host=192.168.31.157 --alter "modify name CHAR(50)" D=sakila,t=test --execute

The tool outputs progress information during execution.

Parameter explanation: --user, --password, --host: database connection details. --alter: the ALTER statement to apply, e.g.,

alter table test modify name varchar(60);

D=database, t=table

: specify target database and table. --execute: actually perform the change; use --dry-run to preview.

Installation

Download the Percona Toolkit from https://www.percona.com/downloads/percona-toolkit/ and install on CentOS 7:

yum install epel-release-7-5.noarch
yum install perl-DBD-MySQL
yum install perl-Digest-MD5
yum install perl-Config-Tiny
yum install perl-Log-Dispatch
yum install perl-Parallel-ForkManager
yum install -y rrdtool perl-rrdtool rrdtool-devel perl-Params-Validate
rpm -ivh percona-toolkit.rpm

After installation, the tool can be executed directly.