0

We are planning to upgrade from Aurora MySQL 2.10.2 (MySQL 5.7 equivalent) to Aurora MySQL 3.03.0 (MySQL 8 equivalent). One of the rollback plans that we are considering is having another Aurora MySQL 2.10.2 replicating from Aurora MySQL 3.03.0. It looks like this:

enter image description here

The reason for this is that in case there are performance issues with Aurora MySQL 3.03.0, we can roll back to the Aurora MySQL 2.10.2 replica. But based on this article, we have to change the character_set_server/character_set_database settings to utf8 and collation_server/collation_database to utf8_unicode_ci because when we tried to insert data directly on Aurora MySQL 3.03.0, the Aurora MySQL 2.10.2 replication breaks with this error Character set '#255' is not a compiled character....

So after we do our upgrade and assuming we do not need to rollback, we plan to change the UTF settings of our Aurora MySQL 3.03.0 to the default settings of the character_set_server to utf8mb4 again and collation_server to utf8mb4_0900_ai_ci possibly after a week.

I am worried that we are changing the default charset/collation related settings to our new Aurora MySQL 3.03.0. Is there any downside to this approach? For example, from the time of the upgrade to the time when we change back our charset/collation related settings, would there be any possible data corruptions/truncation because the default charset on Aurora MySQL 3.03.0 is utf8? We tested this and it seems there is none but we wanted to ask if people have seen any issue with this. But again, I'm not sure which is why I wan ted to ask about this.

6
  • The changes from 5.7 to 8.0 are significant. The main rule is that you probably will not be able to downgrade, nor replicate 9.0->5.7.
    – Rick James
    Jun 7, 2023 at 17:32
  • Thank you. Is this "rule" somewhere in the MySQL documentation? The closes that I found is this: dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/… . Also, are you saying that even if we are able to successfully replicate from 8.0->5.7, it may cause issues if we decide to repoint our app back to the 5.7 replica? Jun 7, 2023 at 20:08
  • I found one reference from 6 years ago; see my Answer. There may be other issues, even with a logical mysqldump.
    – Rick James
    Jun 7, 2023 at 21:25
  • I understand that downgrading is not possible and we don't want to downgrade at all. I saw these percona.com/blog/replicating-mysql-8-0-mysql-5-7 and minsql.com/mysql8/MySQL-8.0-to-5.7-backward-replication and it seems they were successful in backward replication from 8.0->5.7. So, I am just wondering if people have used something like this, and if they did a rollback to 5.7, did they find any issues? Jun 7, 2023 at 23:43
  • If it were me, I would not try to do this. I would upgrade a test instance of the database (not the production instance), test until I am sure there are no surprises, and then do the upgrade of production. We're just a few months from the scheduled end of support of 5.7, so if you want to avoid security liabilities, you must upgrade soon regardless. Jun 8, 2023 at 5:03

1 Answer 1

0

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-2.html

----- 2017-07-17 8.0.2 Development Milestone -- Data Dictionary Notes -- -----

The InnoDB storage engine now uses the global MySQL data dictionary rather than its own storage engine-specific data dictionary. For information about the data dictionary, see MySQL Data Dictionary.

The following list briefly describes the main implications of this change:

Upgrade and downgrade implications:

To upgrade from MySQL 5.7 to MySQL 8.0, you must perform the upgrade procedure described at Upgrading MySQL.

Downgrading from MySQL 8.0 to MySQL 5.7 is only supported using the logical downgrade method (a mysqldump downgrade). In-place downgrades are not supported.

Metadata updates associated with exporting and importing tablespaces using the transportable tablespace feature are now performed on global data dictionary tables instead of InnoDB data dictionary tables.

InnoDB in-memory metadata is now instantiated from global data dictionary objects. This metadata was previously read from InnoDB system tables.

Table options that signify tablespace encryption and transparent page compression are now retrieved from the global data dictionary.

Data dictionary support was added for InnoDB FULLTEXT indexes. Auxiliary index table names were changed to lowercase.

InnoDB metadata created or modified during DDL operations is now written to the global data dictionary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.