How to fix ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'

Failed MYSQL import process with ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'

Intro

An error like this commonly occurs when attempting to import a MySQL/MariaDB database that originated from a different version of the software or a contrasting server environment:

Mysql Error:

ERROR 1273 (HY000) at line [line_number]: Unknown collation: ‘utf8mb4_0900_ai_ci’

The error is a classic case of a version mismatch. Your SQL file is likely using the collation utf8mb4_0900_ai_ci.
Since this collation was introduced in MySQL 8.0, any older server version you try to import into (such as MySQL 5.7 or prior) won't have it in its recognized list, resulting in the import failure.

Version Mismatch:

The utf8mb4_0900_ai_ci collation was introduced in MySQL 8.0. If your local or target server is an older version (like MySQL 5.7 or earlier), it won't recognize this collation.

What is Collation?

A collation defines the rules for character comparison and sorting in a database. When a server encounters a collation it doesn't understand, it generates the "Unknown collation" error.

Solution

The fix is to manually replace the current collation in your SQL file with a supported alternative.

Replace collation schema by using SED command, for MYSQL

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

Execution of sed command to replace not supported collation schema utf8mb4_0900_ai_ci to supported one utf8mb4_unicode_ci

Replace collation schema by using SED command, for MARIADB

sed -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_520_ci/g' -i backup.sql

Execution of sed command to replace not supported collation schema utf8mb4_0900_ai_ci to supported one utf8mb4_unicode_520_ci