Find and replace text string in a .sql file

Consider this error when importing a database into your live or local environment. The error is related to the inconsistencies between the encoding on the .sql file and the server.

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

The fix is to run this bash command to find and replace the string “utf8mb4_0900_ai_ci” with “utf8mb4_unicode_ci”, or the specific encoding on your machine.

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' backup.sql

Then try to import the database again.

The find and replace could be done via a text text editor, however, it becomes problematic on large databases where the majority of text editors tend to crash.