Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

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


New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

Topics:
sql ,mysql ,percona toolkit ,database

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}