Why we cannot perform an 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 is that lock time will 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 (do 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

本文出自 “shadowisper” 博客，谢绝转载！

Mysql zero downtime deployment