Over a million developers have joined DZone.

Modifying Your MySQL Structure without Downtime

DZone's Guide to

Modifying Your MySQL Structure without Downtime

Did you try to add a new index to your MySQL huge table and suffered from a downtime? If the answer is positive, you should try PT-ONLINE-SCHEMA-CHANGE.

· Database Zone
Free Resource

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

Did you try to add a new index to your MySQL huge table and suffered from a downtime?

If the answer is positive, you should introduce yourself PT-ONLINE-SCHEMA-CHANGE.

How the Magic is Done

Actually Percona imitates MySQL behavior with a little tweak.

When modifying a table structure MySQL copies the original table structure, modifies it, copies the data and finally renames the table.

The only problem w/ this behavior that it locks the original table...

Percona is doing the same, but instead of locking the original table, it reviews the latest changes and implements them on the new table. That way the original table still serves the users, and changes replacement is done in a single atomic process.

Percona Toolkit Installation

Download the Percona toolkit and install it (the following is relevant for Ubuntu):

> wget http://www.percona.com/downloads/percona-toolkit/2.2.13/deb/percona-toolkit_2.2.13_all.deb
> sudo dpkg -i percona-toolkit_2.2.13_all.deb

Making a Change

Just call the tool with permissions, database name (D flag), table name (t flag), command to execute (--alter flag) and finally use the execute flag to implement the changes.

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor -uuser -p"password" --execute

Things to Notice

  1. You must have a primary key on the table
  2. If you want only to verify the process before replacing the tables themselves, use --dry-run instead of execute (or just drop this parameter).

Bottom Line

Modifying your database will cause performance degradation, but it should not result in a downtime.

Keep Performing,
Moshe Kaplan

Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

sql ,mysql ,percona toolkit ,database

Published at DZone with permission of Moshe Kaplan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}