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.

  • Reinstall mysql 8, insert original database. make a new mysqldump with dev.mysql.com/doc/refman/8.0/en/… and then test this dumb in an virtual machine.
    – nbk
    Commented Mar 16, 2020 at 20:37
  • @nbk I appreciate your answer. I wonder if you meant to link to dev.mysql.com/doc/refman/8.0/en/… instead. I tried that but got mysqldump: Error: 'Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'FILES.EXTRA' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by' when trying to dump tablespaces
    – Ryan
    Commented Mar 16, 2020 at 20:43
  • i mean --compatible=name Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See Section 5.1.7, “Server SQL Modes”.
    – nbk
    Commented Mar 16, 2020 at 20:58
  • @nbk Thanks for following up. You linked to 8.0 docs which say "The only permitted value for this ['compatible'] option is ansi, which has the same meaning as the corresponding option for setting the server SQL mode." I googled and found that you must have meant to link to 5.7 docs: dev.mysql.com/doc/refman/5.7/en/… But I don't think 5.7 docs apply to my 8 system. When I use mysqldump in 8 with --compatible=ansi and then try to import into mysql 5.7, I get ERROR 1273 (HY000) at line 1748: Unknown collation: 'utf8mb4_0900_ai_ci'
    – Ryan
    Commented Mar 16, 2020 at 21:57
  • 1
    @Ryan, your approach is ok. You seem to have avoided the harder spots with ok that the documentation didn't want to try to fully enumerate. As you've only accidently been on 8.0 for a short time its unlikely that you're using any of its new features. Don't worry too much about the notice.
    – danblack
    Commented Mar 16, 2020 at 22:34

4 Answers 4


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

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/


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


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.


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"

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.