Tables character-set migration without huge downtime- mysql

Question

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 command:

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.

Answer 1

If downtime is a concern, then use one of the online schema change tools:

• pt-online-schema-change. You can perform most types of ALTER TABLE statements with nearly no downtime. See more of my notes about that in my answer here: https://dba.stackexchange.com/a/313406/2703
• gh-ost. This uses a different approach, but has a similar benefit, that you can alter tables without downtime.