I was trying to restore a database to a MySQL server on a test machine and this error occurred:
#1273 - Unknown collation: 'utf8mb4_unicode_520_ci'
The problem is, the server that runs the live database is MySQL 5.7 that supports utf8mb4_unicode_520_ci but the test server I run is only MySQL 5.5 which does not support this specific encoding.
By the way, to check which version of MySQL you are running, run the following command:
mysql -V
The easiest way to fix this collation error is to upgrade MySQL 5.5 to either 5.6 or 5.7. Since the version of my test machine is Ubuntu 14.04, I can easily upgrade it to 5.6 by following:
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install mysql-client-core-5.6
sudo apt-get install mysql-client-5.6
sudo apt-get install mysql-server-5.6The restore went successfully once the server is upgraded to 5.6.
You may want to back up all databases and restore them back, in case anything goes wrong.
To back up all databases at once,
mysqldump --lock-all-tables -u root -p --all-databases > dump.sql
To restore them all once the server is upgraded,
mysql -u root -p < dump.sql
If upgrading MySQL isn’t an option for you, you will need to modify the backup file by replacing all utf8mb4_unicode_520_ci with utf8mb4_unicode_ci. Not ideal, but still works.