|
Why we cannot perform an>
How mysql performs>
- 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>
- 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> Cons:
About 4th slower than> Limitation:
Cannot handle foreign key perfectly
- Detect and write redo log changes to xtrabackup_log
Copy .ibd;>
- 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
|
|
|