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

Question

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.

Answer 1

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

Answer 2

You are suppose to remove utf8mb4 character set & collation, and add relevant character set in your backup file & then try to restore.

Like :

:%s/SET character_set_client  = utf8mb4/SET character_set_client  = latin1/
:%s/SET character_set_results = utf8mb4/SET character_set_results = latin1/
:%s/SET collation_connection  = utf8mb4_general_ci/SET collation_connection  = latin1_swedish_ci/

:wq 

I think still you will face issue for mysql schema restoration as its architecture is changed in MySQL 8.0 than MySQL 5.7

Answer 3

Actually, my question seemed to have a false premise.

Maybe my data was not missing.

2 factors had led me to believe that it was.

First, when I ran:

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

I saw databases that were much smaller than before (e.g. 0.5 MB in v5.7 instead of 54.8 MB in v8.0).

But in looking at row counts of various tables, they seem to be the same in 5.7 as they were in 8.0.

Secondly, I had some confusion about usernames and passwords in the 'mysql' database vs in the 'users' table of one of the databases of one of my apps.

Answer 4

I am on mac, this is what works for me

LC_ALL=C sed -i '' s/utf8mb4/utf8/g your_db.sql
LC_ALL=C sed -i '' s/utf8mb4_0900_ai_ci/utf8_general_ci/g your_db.sql
LC_ALL=C sed -i '' s/utf8_0900_ai_ci/utf8_general_ci/g your_db.sql

Hope this helps :)

• LC_ALL=C is needed if you come across error "sed: RE error: illegal byte sequence"