Error while importing mysql dump sql file Unknown collation: utf8mb4_0900_ai_ci

This is a quick tutorial on how we can resolve the issue while importing the MySQL dump SQL file. Sometimes due to the incompatibility of the MySQL version, the following error might occurs

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

Before resolving the issue, make sure to back up the dump SQL file

sudo cp sql_file_name.sql sql_file_name_backup.sql 

In order to resolve this issue, we are going to replace the collation utf8mb4_0900_ai_ci with the valid collation utf8_general_ci. For this open, the SQL file with vim or vi and hit Shift + : and add the following replace command and hit enter.

%s/utf8mb4_0900_ai_ci/utf8_general_ci/g

The above command will find each occurrence of 'utf8mb4_0900_ai_ci' (in all lines), and replace it with 'utf8_general_ci'.

Now lets change the CHARSET=utf8mb4 to CHARSET=utf8

%s/utf8mb4/utf8/g

Make sure to hit enter after each command.

Now save the file by Shift+: and type wq!

After the changes, import the dump SQL file which supposes to work fine.