Let’s see the problem and solution to the issue faced recently:
The Problem:
During the migration of a WordPress application, I faced the following error with the restoration of the MySQL database. The collation id may differ based on the MySQL version. Error message: Here you go with a solution.
The Solution:
After searching for the error, I found that the MySQL server running on the other server is an older version than the source. So we find out that the destination server doesn’t support the ‘utf8mb4_unicode_520_ci’ collation. To resolve this issue, I did a little change in the backup file. Edit the database backup file in text editor and replace all occurrences of “utf8mb4_unicode_520_ci” with “utf8mb4_general_ci“. Also, if you found “CHARSET=utf8mb4“, replace this with “CHARSET=utf8“. Replace the below string: with: Save the backup file and restore the database. The Linux system users can use the sed command to replace text in files directly. That’s it, Now the database is successfully restored without any errors! Hopefully, this is solution helped you to resolve “Unknown collation: ‘utf8mb4_unicode_520_ci’” issue with MySQL databases.
