We're in the process of migrating to a new character set for several tables in our database. Some tables are currently using utf8/utf8mb3 character set, which is deprecated now and we want move on using utf8mb4 on those tables. We figured out that we don't only need to change the character set for the tables, we also need to convert our data to work with this new character set, for that we are using the following
ALTER TABLE mySchema.TableToConverty convert to CHARACTER SET utf8mb4 , COLLATE utf8mb4_bin
Note: We had to disable
@foreing_key_checks at the beggining of the script and then turn it back on at the end.
This works fine as mysql docs suggests, but the issue is the service degradation/downtime we might have in our production environment, which is not desired.
We have to apply this
ALTER statement to 70 tables approximately, we already tested this in one of our non-production environments and the entire scripts takes around 5 hours to complete, which will cause the same time of service degradation on our production environment. I was wondering if there's a way to make these
ALTER statements not last that long, improve them in some way, specially for tables that contains lots of data, which are the ones that takes lots of time to migrate. Thanks.