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?
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