Large MySQL migrations with pt-online-schema-change

pt-online-schema-change is a CLI script and part of Percona-toolkit by Percona.

It’s one of the most efficient tools for performing large MySQL table migrations, minimizing DB downtime.

Here’s a rough outline of the performed operations:

1. create empty a_table_new duplicating a_table's stucture
2. alter empty a_table_new as specified(performs structure migration)
3. add data-copy-trigger to a_table so any new data gets copied to a_table_new
4. copy actual records from a_table to a_table_new
5. swap a_table with a_table_new by performing an atomic rename

So a_table_new becomes a_table with altered structure and all the data.

Example usage

On a table with 5M rows.

$ pt-online-schema-change  --execute --ask-pass --user=admin --chunk-time=1 --nodrop-old-table --alter "add column foo int(11) default null" D=an_app_staging,t=foo_records,h=localhost
Enter MySQL password: 

Altering `an_app_staging`.`foo_records`...
Creating new table...
Created new table an_app_staging._foo_records_new OK.
Altering new table...
Altered `an_app_staging`.`_foo_records_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 5413020 rows...
Copying `an_app_staging`.`foo_records`:   9% 04:36 remain
Copying `an_app_staging`.`foo_records`:  20% 03:59 remain
Copying `an_app_staging`.`foo_records`:  27% 03:55 remain
Copying `an_app_staging`.`foo_records`:  36% 03:25 remain
Copying `an_app_staging`.`foo_records`:  46% 02:53 remain
Copying `an_app_staging`.`foo_records`:  54% 02:27 remain
Copying `an_app_staging`.`foo_records`:  62% 02:05 remain
Copying `an_app_staging`.`foo_records`:  71% 01:37 remain
Copying `an_app_staging`.`foo_records`:  80% 01:07 remain
Copying `an_app_staging`.`foo_records`:  91% 00:28 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `an_app_staging`.`foo_records`.

Migration took ~5 minutes.

Notice: it may take a while until the tool starts showing progress, as querying information_schema takes a long time. Use show processlist to see what it’s doing.

Percona-toolking

Percona-toolkit contains many useful tools.

Watch Bill Karwin’s percona-toolkit presentation

Installing on OSX

$ brew install percona-toolkit
$ pt-find

Got an error:

Cannot connect to MySQL because the Perl DBD::mysql module is not installed or not found.  Run 'perl -MDBD::mysql' to see the directories that Perl searches for DBD::mysql.  If DBD::mysql is not installed, try:
  Debian/Ubuntu  apt-get install libdbd-mysql-perl
  RHEL/CentOS    yum install perl-DBD-MySQL
  OpenSolaris    pgk install pkg:/SUNWapu13dbd-mysql

Then tried building DBD::mysql module with:

$ ARCHFLAGS="-arch x86_64" PATH=/usr/local/mysql/bin/:$PATH cpan DBD::mysql

failed.

This worked:

$ cd ~/.cpan/build/DBD-mysql-4.021-obe4Nm/
$ ARCHFLAGS='-arch x86_64' perl Makefile.PL --testuser='root'
$ make && make install

Great success!