Mysql import error was shown when exporting a database from cPanel to a local server.

During the migration of a web application, I got the below error while restoring a database on another server. The collation id may differ based on the MySQL version.

Error message:

mysql backup.sql < backup.sql
ERROR 1253 (42000) at line 31: COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8'

The MySQL server running on the new server is an older version than the source server. The destination server doesn’t contain the required database collation.

To resolve, edit the database backup file in text editor and replace “utf8mb4_0900_ai_ci” with “utf8mb4_general_ci” and “CHARSET=utf8mb4” with “CHARSET=utf8“.

Replace the below string:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

with:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Save your file and restore the database.

The Linux system users can use the sed command to replace text in files directly.

sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' backup.sql  
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql  

That it. after the above changes, the database was successfully restored!