Mysql – How to alter/migrate the schema of a table with tons of records

migrationMySQL

I have a table which has about 1 million records, and recently i'm going to ship a new feature which requires 2 new fields. But I found it takes about minutes to get it finish, and during the migration, i have to shutdown the service, as the table was locked.

Is there any ideal way to do such kind of jobs without making the online service offline?

Thanks.

Best Answer

What I do in this case is:

  • create a shadow table with the new schema
  • move over all the data
  • xlock the original table
  • apply the changes since moving the data
  • rename the original table to bak
  • rename the "new" table to the correct name

This makes your downtime minimal at the cost of disk space.

Related Question