How to take a dump from Mysql 8.0 into 5.7?

Question

I would like to take a dump from Mysql 8.0.11 and restore it into 5.7.27.

When I tried to restore it I got the error:

ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'

Then I tried to use the compatible flag to make it easier on an older MySQL DB.

mysqldump --compatible=mysql4 --add-drop-table -u r00t -h xxx.eu-north-1.rds.amazonaws.com -p radius_db > ~/radius.sql

But that doesn't seem to work either:

mysqldump: Couldn't execute '/*!40100 SET @@SQL_MODE='MYSQL40' */': Variable 'sql_mode' can't be set to the value of 'MYSQL40' (1231)

Any advice would be appreciated.

Answer 1

Simply put, use as a DEFAULT "utf8" and as COLLATE "utf8_general_ci".

One way to solve your problem is to change in your import .sql-Files from

• "utf8mb4" to "utf8"
• and "utf8mb4_0900_ai_ci" (or something else) to "utf8_general_ci"

Hint: Don't forget to backup your files just in case ;-)

Go to your (.sql) import files and do these changes. From:

ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8_general_ci;

to:

ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Reimport again.