dasini.net – Journal d'un expert MySQL

It is great to be able to build small utilities on top of an excellent RDBMS. Thank you MySQL.

This is a small but complex utility to perform online schema change for MySQL. We call it OSC and the source is here.

Some ALTER TABLE statements take too long form the perspective of some MySQL users. The fast index create feature for the InnoDB plugin in MySQL 5.1 makes this less of an issue but this can still take minutes to hours for a large table and for some MySQL deployments that is too long.

A workaround is to perform the change on a slave first and then promote the slave to be the new master. But this requires a slave located near the master. MySQL 5.0 added support for triggers and some replication systems have been built using triggers to capture row changes. Why not use triggers for this? The openarkkit toolkit did just that with oak-online-alter-table. We have published our version of an online schema change utility (OnlineSchemaChange.php aka OSC).

The remainder of this document is copied from the internal documents that were written for this project. Note that this project was done by Vamsi and he did an amazing job with it. In addition to writing the tool, writing the docs and doing a lot of testing he also found and fixed or avoided a few bugs in MySQL to make sure OSC would be reliable.

…

http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

Olivier DASINI

Architecte Solution Cloud chez Oracle
MySQL Geek, Architecte, DBA, Consultant, Formateur, Auteur, Blogueur et Conférencier.
—–

Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–

Filed under:
MySQL by Olivier DASINI