Sample Header Ad - 728x90

Alter large table without downtime

12 votes
2 answers
22408 views
I have a very large table, lets call it example, and I try to execute a little alter command on that table: ALTER TABLE example ADD example_column TINYINT(1) NOT NULL DEFAULT 0; Checking the progress of alter command: mysql -e 'show engine innodb status \G' | grep 'undo \| log \| entries' Give me nice info, about the time - more or less it will take 17 days to complete ... Alter block the table, so blocking production table for 17 days is not a good option. ;) I try to investigate some great tools on the net, like: 1. Pt-online-schema-change - Percona 2. gh-ost - GitHub's Online Schema Migrations for MySQL I have also read the documentation, and the limitation section of the above tools: gh-ost limitations - does not support triggers at all - does not support foreign keys at all pt-online-schema-change limitations - The use of triggers means that the tool will not work if any triggers are already defined on the table. - Does not provide any good way of altering tables which contain foreign keys My example table have triggers and foreign keys... Could you give me some advice, how to deal with this alter? I have MySQL 5.6. I use GTID (row-based) replication. I will be very grateful for advice!
Asked by crashoverbike (221 rep)
Aug 1, 2017, 08:28 AM
Last activity: Aug 13, 2017, 07:00 PM