gh-ost was released in August, it might be the best tool to upgrade MySQL table structure online so far. But haven’t gotten a chance to try.
This week, I was preparing release of one of our big sites, which has three big tables need to be upgraded (structure change), they have more than 70 million rows in total, data and index occupy more than 30G space on disk. I tried the normal structure change in MySQL, it took me around 5 hours to finish all changes on all of the three tables. That means we have to close the production site for 5 hours, sounds crazy, but what we did always.
Then, I thought maybe I can try this new tool, gh-ost, which created and tested by GitHub. But, finally, I found it does not support foreign key!
user1@db1:~/gh-ost$ ./gh-ost --max-load=Threads_running=25 --critical-load=Threads_running=1000 --chunk-size=1000 --max-lag-millis=1500 --user="user" --password="******" --host="127.0.0.1" --allow-on-master --database="database1" --table="TRANSACTION2" --verbose --alter="ALTER TABLE TRANSACTION2 MODIFY COLUMN TEMP_GH_OST_TEST_FIELD_1 INT NULL" --switch-to-rbr --cut-over=default --exact-rowcount --concurrent-rowcount --default-retries=60 --nice-ratio=0.5 --serve-socket-file=/home/user1/gh-ost/game_tx2/sock.gh-ost.database1.TRANSACTION2 --throttle-flag-file=/home/user1/gh-ost/game_tx2/flag.gh-ost.database1.TRANSACTION2.throttle --panic-flag-file=/home/user1/gh-ost/game_tx2/flag.gh-ost.database1.TRANSACTION2.panic.flag --postpone-cut-over-flag-file=/home/user1/gh-ost/game_tx2/flag.gh-ost.database1.TRANSACTION2.postpone.flag 2016-10-06 03:54:25 INFO starting gh-ost 1.0.20 2016-10-06 03:54:25 INFO Migrating `database1`.`TRANSACTION2` 2016-10-06 03:54:25 INFO connection validated on 127.0.0.1:3306 2016-10-06 03:54:25 INFO User has ALL privileges 2016-10-06 03:54:25 INFO binary logs validated on 127.0.0.1:3306 2016-10-06 03:54:25 INFO Restarting replication on 127.0.0.1:3306 to make sure binlog settings apply to replication thread 2016-10-06 03:54:26 INFO Table found. Engine=InnoDB 2016-10-06 03:54:54 INFO Found foreign key on `database1`.`ALARM_LOG` related to `database1`.`TRANSACTION2` 2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2` 2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2` 2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2` 2016-10-06 03:54:54 INFO Found foreign key on `database1`.`UNKNOWN_WIN` related to `database1`.`TRANSACTION2` 2016-10-06 03:54:54 ERROR Found 5 foreign keys related to `database1`.`TRANSACTION2`. Foreign keys are not supported. Bailing out 2016-10-06 03:54:54 FATAL 2016-10-06 03:54:54 ERROR Found 5 foreign keys related to `database1`.`TRANSACTION2`. Foreign keys are not supported. Bailing out |