Fastest way to change a 600GB table indexed key datatype from INT to BIGINT

Question

I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much same pain. The table's engine is InnoDB. What would be easier:

1. ALTER TABLE
2. Copying structure and INSERT INTO (SELECT *)
3. Dumping table and changing dump file table definitions
4. Anything else?

UPDATE: As requested, MySQL ver 5.5.15, no foreign keys and create table:

 CREATE TABLE `tbl` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `created_at` datetime NOT NULL,
    `tid` bigint(20) NOT NULL,
    `t` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `f` tinyint(1) NOT NULL,
    `i_id` bigint(20) NOT NULL,
    `ir_id` int(11) NOT NULL,
    `r_c` int(11) NOT NULL,
    `r` tinyint(1) NOT NULL,
    `e` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `t` varchar(5) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user` (`user_id`,`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=1657146169 DEFAULT CHARSET=utf8

Answer 1

Assuming your table does not have any triggers on it, you should consider using pt-online-schema-change since it will allow you to ALTER the table without locking it.

It will still take a fair amount of time given the size of the table.

Also, with this method or with ALTER TABLE you will need to make sure you have 600 GB of extra disk space to support two copies of the table while it is being rebuilt.

Answer 2

Using percona toolkit pt-online-schema-change would be my choice on production without affecting the use. This will add some triggers to get your deltas and a temporal table that would rename after this is done.

example:

pt-online-schema-change --alter "CHANGE `id` `id` BIGINT  NOT NULL; " D=DB,t=TABLE