COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'

While importing mysql database, you might see following error...

COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'

This error shows up if you have different character and collation setting in your mysql server than what you are trying to source from .sql file. To fix that first make sure you have same settings in your .sql file and in your mysql database.

Let us first check what settings we have in our mysql-server.

Run following command in your mysql client shell...

SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

As we can see above, character_set_server is set to utf8.

Let us check our collation settings using following command...

mysql> SHOW VARIABLES LIKE '%collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

Ok, let us open our .sql file and replace everywhere 'utf8mb4' with utf8. I don't need to change 'utf8_general_ci' since that is the same setting I have in my.cnf.

That's it. Now you should be able to import your .sql file without any error.

Note - You can also do the other way around (not preferable way though) . You can fix the default settings of your mysql server in my.cnf file.

Open the my.cnf file and add following settings...

[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8

You can change above settings to whatever you have in your my.cnf file.