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

Question

I used to copy the db from remote to my local using script

ssh -Tq remotehost.com 'mysqldump -C -u remoteuser -p"remotepwd" -h remotehost.com remote_db_name --skip-lock-tables | gzip' | gzip -d | mysql -u localuser -plocalpwd local_db_name

It used to work fine but now I'm getting below error -

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

If I just get sql from remotehost and do run the below command I get below error -

sed -i 's/utf8mb4/utf8/g' portal_dump.sql I get error

sed: 1: "portal_dump.sql": extra characters at the end of p command

What should I do to fix this script?

Answer 1

In case someone is still interested in the answer to this question:

This occurs when you try to migrate a database with mysqldump to a server with earlier versions of MySQL than 8.0

Collation utf8mb4_0900_ai_ci is new in MySQL 8.0 and not recognized in earlier versions, so that's why you should replace all instances in your sql file from utf8mb4_0900_ai_ci to utf8mb4_general_ci

Answer 2

Replace all utf8mb4_0900_ai_ci to utf8mb4_general_ci in your sql file. I think it would be the closest match and it will solve your error.