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:
- Pt-online-schema-change - Percona
- gh-ost - GitHub's Online Schema Migrations for MySQL
I have also read the documentation, and the limitation section of the above tools:
- does not support triggers at all
- does not support foreign keys at all
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!