Hi All, can you please let me know how important following “And remember you should be using the four-byte UTF8MB4 character set,” ? from https://www.percona.com/blog/mysql-5-7-to-8-0-upgrade-in-place-or-logical-upgrade/ . You know we have some small scale servers and we did in place upgrade for them and did not do anything about character set, and adter inplace upgrade all works fine without any issues. Now we have much larger database 1TB and we know we have to do a logical upgrade and I stumbled on this. I wonder maybe during inplace upgrade mysql does it itself but for logical this has to be done manually?
Migrating charset is not a strong requirement but will be the new default. If you wish you can change the default configuration and continue to use latin1 (which is the default for 5.7) or any other charset.
When doing an in place upgrade, existing tables won’t be rebuild. This means that if the table definition is using latin1 then after upgrading the table will continue to use latin1. For newly created tables (that you do not specify what charset to use) the new default will be used.
If the migration is not done in place but rather through a logical restore then you are in this case: Migrating Database Charsets to utf8mb4
I.e existing tables will be converted to the new default charset configured at the time of the restore. Before starting the restore you can decide which charset to use
Thank you @CTutte I wonder should I also change default to latin1 to avoid inconsistencies (between tables created after upgrade and before upgrade) or it is not really an issue?
Why do you know this? Typically, large datasets are perfectly fine doing in-place binary upgrades.
well, it is just an assumption because of following reasons.
the blog I shared states "Generally, the In-Place upgrade will be faster as you do not have to perform a backup on the old instance and then run a restore on the new platform. Those with very large instances should choose this option. "
Logical upgrade seems safer since rollback will much easier
From in-place upgrade tests I remember for 40GB db inplace upgrade there was considerable upgrade time, like we start mysqld but it does not start right away but do data directory upgrade. We worry that with 1TB database it will take hours.
Note: my logical upgrade I understand “setup replica, upgrade replica, make replica catch master (5.7) and promote replica to master”
This depends on how many tables you have, not the size of the dataset. 5x1TB tables will upgrade faster than 10,000x5MB tables.
Yes, do this path. Create a 5.7 replica using Percona Xtrabackup (streaming physical backup), make replica catch up, then stop replica and upgrade replica in-place to 8.0. Resume replication to catch up. When ready, cutover to 8.0. Leave 5.7 stopped in case of roll back.