The error means the target MySQL server has never heard of the collation named in your dump file, usually because it is older than the server you exported from. The safe fix is to change the collation to one the target does know inside the same character set, not to rewrite utf8mb4 as utf8, because that second edit is a downgrade that throws away every four-byte character in the database.
Every guide that ranks for this error recommends the downgrade. It works, in the sense that the import completes.
What the error is actually telling you
Unknown collation: 'utf8mb4_unicode_520_ci' is a collation problem, not a character set problem. Those are two different things, and the standard fix confuses them.
A character set decides which characters can be stored. A collation decides how they sort and compare. One character set has many collations, and MySQL versions ship different ones.
utf8mb4_unicode_520_ci arrived in MySQL 5.6. utf8mb4_0900_ai_ci is the MySQL 8 default and does not exist in MariaDB at all. Move a dump from a newer server to an older one and the CREATE TABLE statements name a collation the target cannot resolve, so it refuses the statement.
The character set in that name, utf8mb4, is almost certainly fine. It has been the MySQL recommendation for years and is supported from 5.5.3 onwards.
Why the popular fix is not a rename
Search the error and you get two answers. Export with mysqldump --compatible=mysql40, or open the .sql file and replace utf8mb4_unicode_ci with utf8_general_ci and utf8mb4 with utf8.
The first replacement is harmless: it swaps one collation for another inside the same character set. The second one changes the character set, and that is a different kind of edit.
MySQL's own documentation is direct about what utf8mb3 (the modern name for utf8) can hold. It "requires a maximum of three bytes per multibyte character" and "supports BMP characters only (no support for supplementary characters)".
Supplementary characters are the four-byte ones. Emoji, most of the historic scripts, mathematical alphanumerics, and a large slice of the CJK extension blocks.
So the find-and-replace does not make the dump compatible. It declares that a set of characters currently in your database is no longer storable, and then imports the file anyway.
The two failures are separated by weeks
This is why the downgrade reads as a success. The damage arrives in two parts, and the second part is not connected to the first in anyone's mind by the time it shows up.
On import, the existing four-byte characters have to go somewhere. Under MySQL's strict mode the row errors out. Without strict mode, the value is truncated at the offending character and you get a warning that scrolls past in a terminal, so a product title becomes half a product title and the import reports success.
After import, new content is the problem, and here WordPress does the discarding rather than MySQL. wpdb runs every write through strip_invalid_text_for_column(), documented as stripping "any invalid characters from the string for a given table and column".
It reads the column's real charset and removes what will not fit. No error, no notice, no debug.log entry. Someone pastes an emoji into a post title, saves, and the title saves without it.
That is the failure worth caring about. A truncated row can be spotted. A character that never gets written leaves nothing behind to find.
Test the import on a disposable copy first
The whole question is answerable before you touch the destination, and it needs a real MySQL server rather than a syntax check, because the interesting behaviour is truncation at insert time.
Create a sandbox on the WordPress version you are running, and use SSH to work on it. WP-CLI is preinstalled, so wp db query gets you a MySQL prompt without opening phpMyAdmin.
sandywp ssh my-import-test
Import the edited dump there, not into the target. Then run the checks below on the result and compare them with the same checks on the source.
If you are restoring from a full site export rather than a bare .sql file, the Cloner plugin does the restore into the sandbox and you skip the manual import step entirely.
Count what the downgrade would cost, before you accept it
Ask the source database how many rows actually contain a four-byte character. If the answer is zero, the downgrade is genuinely free and you can stop worrying.
The trick is to convert a column to utf8mb3 and compare byte lengths. A character that does not fit becomes a single-byte ?, so the length changes:
SELECT COUNT(*) FROM wp_posts
WHERE LENGTH(post_content) <> LENGTH(CONVERT(post_content USING utf8mb3));
Run it against post_title and post_excerpt too, then wp_postmeta.meta_value, wp_comments.comment_content, wp_options.option_value, and wp_users.display_name.
It flags anything the three-byte set cannot represent, not only emoji, which is what you want. Names and addresses in customer meta are a more common source of hits than post content.
A PHP version reads more clearly if you prefer it, and works the same way:
wp eval 'global $wpdb; echo $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts WHERE post_content REGEXP \"[\\\\x{10000}-\\\\x{10FFFF}]\"");'
Whatever the number is, it is now a decision rather than an accident. Twelve rows in a comments table might be acceptable. Twelve rows in wp_postmeta where the store keeps customer names is not.
Check for a half-converted database
The other failure is a database that is partly one charset and partly the other, which happens after a conversion that ran on some tables and skipped others.
WordPress's own converter is conservative by design. maybe_convert_table_to_utf8mb4() checks the columns first and, in the documented behaviour, "rejects tables containing non-utf8 character columns". A plugin table holding a latin1 column is skipped and stays skipped.
List what you actually have:
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND character_set_name IS NOT NULL
AND character_set_name <> 'utf8mb4'
ORDER BY table_name;
An empty result means the database is uniformly utf8mb4. Anything else is a list of places where a four-byte character will be dropped on write, and where a JOIN against a utf8mb4 column can raise Illegal mix of collations.
Do the same for tables, since a table's default charset governs columns added later by a plugin update:
SELECT table_name, table_collation FROM information_schema.tables
WHERE table_schema = DATABASE();
The index length error going the other way
Converting up to utf8mb4 on an older server produces its own failure: Specified key was too long.
The InnoDB limit is documented per row format. DYNAMIC and COMPRESSED tables allow a 3072-byte index key prefix, while REDUNDANT and COMPACT allow 767 bytes. MySQL's manual gives the exact case as an example: "you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set".
That is where the 191 in so many WordPress schema definitions comes from. It is 767 divided by four.
If you hit it, the row format is usually the real answer rather than the index. This is another thing worth finding on a sandbox at three in the morning rather than on the live database.
What a sandbox will not tell you
Three things do not transfer, and pretending otherwise is how a green test turns into a bad night.
The target server's flavour and version. A sandbox reproduces the dump and the WordPress version, not your destination host's MySQL 5.7 build or its MariaDB fork. If you can get the target's SELECT VERSION() and SHOW COLLATION output, test against that knowledge rather than against a guess.
Strict mode. Whether a bad row errors or truncates depends on sql_mode on the destination, which is a server setting you may not control. Check it with SELECT @@sql_mode; on both sides.
Scale and time. ALTER TABLE ... CONVERT TO CHARACTER SET rewrites the table. On a small copy it returns immediately; on a two-million-row wp_postmeta it locks for long enough to matter, and that number is the one you need from a copy of the real database rather than a fresh install.
The version of this that is actually safe
If the target server supports utf8mb4 and only rejects the collation name, change the collation and leave the charset alone. utf8mb4_unicode_ci is understood by every MySQL from 5.5.3 and by MariaDB, which makes it the safe destination:
sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g; s/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql
That is a sorting change, not a storage change, and no characters are at risk.
If the target genuinely cannot do utf8mb4, it is running something older than MySQL 5.5.3, which was released in 2010. The right fix is the host, not the dump. Ask them to move you, and if they will not, that is information about the host.
Only when the count query comes back zero, and you have checked the columns people actually type unusual characters into, is the downgrade an acceptable trade rather than a deferred bug.
After the move, prove it rather than looking at it
The homepage will render either way, so it is not evidence. Two checks are:
Write a four-byte character through WordPress, not through phpMyAdmin, and read it back. Save an emoji into a post title, then wp post get <id> --field=post_title. If it comes back without the emoji, strip_invalid_text_for_column() removed it and the database is still three-byte somewhere.
Re-run the information_schema query above and require an empty result.
Both take a minute on a disposable copy with debug logging on, and both fail loudly, which is the entire point. The default state of this problem is that nothing fails at all.
For the other silent migration failure, the one where the data survives but stops being readable, see how to check a migration did not corrupt serialized data.
