Replace Collation on a MySQL Dump File

Sometimes we may find difficulty with importing a database from dump file which is created from a different machine.

It may issue an error message like this:

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

Which means the dump file uses a collation which the mysql server on the target machine not understanding.

One solution is to replace the collation with one that is accepeted by the mysql server running. We can use a ‘sed’ command like this to perform that replacement:

sed -i 's|utf8mb4_0900_ai_ci|utf8mb4_unicode_ci|g' my-database.sql