Fastest way to alter column type in a 2-billion+ records innodb table

Posted on

Question :

I have a MySQL table with 4 columns. The 1st column record_id is of type int, primary key, and auto-incremented. Now that I have added more than 2 billion records to this table I am finding that I have exceeded the range of my auto-incremented field. I need to modify record_id to a “bigint” to accommodate more records. What would be the fastest way to do this?

Is there a way to do this in-situ without dumping the tables?

Answer :

Take a look here at Percona’s online schema change tool (anything from Percona is good – I’d advise you to look at all of the tools while you’re at it). You could also try Shlomo Noach’s online table change tool – test with both and see which one works for you (again, look at his entire toolkit).

Leave a Reply

Your email address will not be published. Required fields are marked *