Mysql Zero Downtime Deployment

Why we cannot perform a ALTER TABLE directly to update schema?

How MySQL performs ALTER TABLE:

1. Lock the table

2. Make a copy of the table

3. Modify the copy (the "New Table")

4. Copy all the rows into the new table

5. Swap the old and new table

6. Unlock the table

The key issue here was that lock time would be quite long for large table, whick blocks online transaction.

Percona ' s Solution:

• Pt-online-schema-change Tool:

  How Pt-online-schema-change performs ALTER TABLE?
  

1. Make a copy of the table

2. Modify the copy (the "New Table")

3. Copy all the rows into the new table (does in small chunks, insert: Select)

4. ADD triggers to keep track of changes

5. Swap the old and new table

Pros:
Remove the Lock/unlock steps, no longer blocking
Replication-awareness
Load awareness-Chunk size is auto adjusted

Cons:
About 4th slower than ALTER TABLE directly

Limitation:
Cannot handle foreign key perfectly

Xtrabackup

This article from "Shadowisper" blog, declined reprint!

Mysql Zero Downtime Deployment