Changing data type from mediumint to int without downtime

My current table is using MEDIUMINT for ID column ‘primary key auto increment’. The current table records soon will reach the maximum value for mediumint which is 8388607. That being the case, I need to change the type to int or change it to unsigned without any downtime.

My setup is master-slave MySQL 5.6 on Amazon RDS. I have one master and 4 slaves.

• I tried to change the column type to int on slave but it break the replication.

• Records are changing, because of this it is not possible to copy the table to another table.

• I cannot use pt-table-sync on Amazon RDS to sync another table and then do the changes on new table.

Is there any way to change to int without locking the table?

Update: I am not sure if the attribute is signed or unsigned. I am not able to find where it signed or not. I run show columns from table and here is the result:

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
|     id         | mediumint(8) | NO   | PRI | NULL    | auto_increment |

While Rolando has my vote, because he is right in your case, I want to answer your original question (for you in the future and for others) regarding percona toolkit:

pt-table-sync is not what you want. Think about pt-online-schema-change. While there are some hard corners, it works on RDS with the right configuration or minimal changes on the script. pt-table-sync will also fix the issues with rds in the next release. There are other alternatives for online schema changes, oak-toolkit and Facebook ones.

As an alternative, you need a spare slave that can suffer the lag for the time the ALTER is running and then doing a controlled switchover.

StackExchange Database Administrators Q#73209, answer score: 6