首页 > 数据库技术 > 详细

Mysql zero downtime deployment

时间:2017-02-20 14:18:38      阅读:299      评论:0      收藏:0      [点我收藏+]

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

原文:http://shadowisper.blog.51cto.com/3189863/1899343

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!