Problems with user credentials after Upgrading MySQL 5.7.40 -> 8

[Sidney Mueller]

Hi,

I had problems when upgrading from MySQL 5.7.4 to MySQL 8. The databases were not adapted without problems and the MySQL server could no longer be started. I then set up a VM, installed MySQL 5.4.7 on it and integrated the source databases there in order to be able to dump all these databases with MySQL. I then imported these dumps into MySQL and the MySQL 8 server can now be started. However, there seem to be problems with the imported user table and/or their passwords.

In MySQL 8, the table STRUCTURE says plugin char(64) [caching_sha2_password], whereas in MySQL 5.7.4 it says plugin char(64) [mysql_native_password]. After the SQL import on the MySQL 8 server, however, almost all users have "mysql_native_password" in the "Plugin" column, contrary to the above-mentioned structure definition. Among other things, Nextcloud suddenly stopped working for me because the relevant user from Nextcloud-config.php was no longer accepted with his account data.

This could also be the reason why the message "[Warning] [MY-010319] [Server] Found invalid password for user: '92788_1.usr4711@localhost'; Ignoring user" is displayed when MySQL Server 8 is started. Possibly the entries for mysql.infoschema, mysql.session and mysql.sys are no longer correct in the MysQL 8 user DB.

What do I have to do to correctly export the users from MySQL server 5.7.40 and import them into MySQL 8 so that they are stored correctly in MySQL 8 and their passwords are still recognised correctly?

Kind regards
Sidney