Practo Engineering
Published in

Practo Engineering

MySQL zero downtime schema update without algorithm inplace

Of course, we cannot afford such a downtime.

What is algorithm copy and algorithm inplace in respect to MySQL?

Why did algorithm inplace not work in my scenario?

  1. The table that I want to add column to was built in MySQL version 5.5
  2. This table has temporal types.
  3. MySQL version has been updated to 5.6
  4. Any change in schema for this table can’t be done using algorithm inplace because the schema needs to be rebuilt.
  5. To change the schema now, algorithm copy has to be run first which will change the schema by rebuilding it.
  6. After the schema has been rebuilt using copy, any subsequent changes to the schema can be performed using inplace.
Precisely my condition at that time

Solution : pt-online-schema-change by percona toolkit

  1. Creates a new temporary table with updated schema.
  2. Copies data from old table to the newly created table in chunks.
  3. Doesn’t put locks on read.
  4. Doesn’t put locks on writes.
  5. Creates triggers that will copy the newly added data in old table to the new one.
  6. Perform an atomic RENAME operation on the new table.
  7. Drops the old table.
Just kept biting my nails!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sajal Sarwar Sharma

Engineer | Masters @ IIIT Hyderabad | Biker | Sketch Artist | Trying to be human & !(Jon Snow) | https://cosmos-sajal.github.io