Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Innodb engine table with 100GB,I have a planned to do partition the table.Can anyone tell me how to do it in zero downtime?

In 100GB table the records are increasing for each and every seconds.How can I do partition with out affecting the incoming records?

Using Percona-Mysql 5.5.30.Innodb-file-per-table was enabled.

Please tell me the steps.

share|improve this question

1 Answer 1

You may use one of the online-alter-table tools: these are specialized scripts which simulate an ALTER TABLE operation, using TRIGGERs and shadow tables, and which allow for non-interruptive process. Thus, they will copy your table in small chunks into a shadow table even while you are modifying (inserting, deleting, updating) your table, synching the two.

They end with a very brief (up to a few seconds, typically sub second) lock, where your original table is thrown away and the "shadow" table kicks in to replace it.

Two such tools are:

  1. oak-online-alter-table, as part of the openark-kit (disclosure: I'm author of this tool). Been around since 2009 and the first of its kind.
  2. pt-online-schema-change, as part of the percona-toolkit; later arrival but more sophisticated and more tested.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.