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

alter-tabledatabase-designMySQLmysql-5.5

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

Best Answer

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.

Related Question