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

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

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


Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

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 }}