How import mysqldump .sql file into MySQL 5.7 when exported from MySQL 8

I accidentally installed MySQL 8 and did a ton of work there, creating a lot of data. I then backed up all of the databases using mysqldump.

Later, I realized that I needed to downgrade back to MySQL 5.7 (after not even having realized that I'd upgraded).

I've uninstalled 8 and installed 5.7, but I've been having trouble importing my data from the .sql file from the dump.

First, I got the error ERROR 1273 (HY000) at line 1753: Unknown collation: 'utf8mb4_0900_ai_ci', which I solved by using Notepad++ to replace:

• utf8mb4_0900_ai_ci with utf8_unicode_ci

• utf8mb4 with utf8

• utf8_unicode_520_ci with utf8_unicode_ci

Then I also deleted the section of the .sql file related to the 'mysql' database.

Now the command mysql < alldb.sql runs without error, but when I go look at my data, a lot of it is missing.

P.S. I feel optimistic that someone can help me, but it's a bit worrying that MySQL 8 docs say:


Downgrade from MySQL 8.0 to MySQL 5.7, or from a MySQL 8.0 release to a previous MySQL 8.0 release, is not supported. The only supported alternative is to restore a backup taken before upgrading. It is therefore imperative that you backup your data before starting the upgrade process.

Though the author already answered. Still for the benefit of other users, I am putting it here.
You need to replace charset "utf8mb4" with "utf8" and COLLATE "utf8_unicode_520_ci" or "utf8mb4_0900_ai_ci" and in your case of 5.7 with "utf8_general_ci". If your source is not UTF8 then you have to find both your MYSQL source server COLLATE as well as target COLLATE. Change the command below accordingly.

You can achieve the replacement in MAC with below sed command. In Linux remove ''.

For Mac

sed -i '' s/utf8mb4_0900_ai_ci/utf8_general_ci/g NEW-CLEANED-DUMP.sql
sed -i '' s/utf8mb4/utf8/g NEW-CLEANED-DUMP.sql

For Linux

sed -i  s/utf8mb4_0900_ai_ci/utf8_general_ci/g NEW-CLEANED-DUMP.sql
sed -i  s/utf8mb4/utf8/g NEW-CLEANED-DUMP.sql

StackExchange Database Administrators Q#262072, answer score: 3