4

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 Mar 16 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 Mar 16 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 Mar 16 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 Mar 16 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 Mar 16 at 22:34
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

| improve this answer | |
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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