We had to install a new WordPress site onto an old MySQL server and received the following error on import:
In order to fix, we needed to change the collation of the sql file. To do this easily, we found the following snippet:
In Ubuntu:
sed -i 's/utf8mb4/utf8/g' my_table.sql
In OSX
sed -i '' 's/utf8mb4/utf8/g' my_table.sql
After running sed, the sql file imported without issue.
Thanks:
http://stackoverflow.com/questions/29909290/collation-issue-is-it-possi…
Update
In order to get a newer version of Wordpress to install we had to run this additional command after the ones above:
sed -i '' 's/utf8_unicode_520_ci/utf8_general_ci/g' my_table.sql
This was needed because the 'utf8mb4_unicode_520_ci' collation wouldn't work with our older MySQL server.