Why we cannot perform an alter table directly to update schema?
How mysql performs alter table:
Lock the table
Make a copy of the table
Modify the copy (the "new table")
Copy all the rows into the new table
Swap the old and new table
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?
Make a copy of the table
Modify the copy (the "new table")
Copy all the rows into the new table (do in small chunks, insert .. select)
Add triggers to keep track of changes
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:
Detect and write redo log changes to xtrabackup_log
Copy .ibd; idbdata1 to backup folder with timestamp
Flush table with read lock
Copy .frm .myd .myi files
Get binary log position
Unlock tables
Stop and copy xtrabackup_log
mysqldump --single-transaction --master-data
Reference:
https://www.percona.com/files/presentations/WEBINAR-zero-downtime-schema-changes.pdf
http://www.cnblogs.com/olinux/p/5207887.html
http://www.cnblogs.com/cchust/p/4603599.html