If you’re switching your WordPress installation from a MySQL 8 server to MySQL 5.7 (or below), you’ll most likely encounter the 1273 – Unknown collation: ‘utf8mb4_0900_ai_ci’ error when trying to import the database. No matter which tools you use to import or export the database, you can’t escape this error.
However, if you have previously run your blog on a MySQL 5.7 server and recently switched to a MySQL 8 but now switching back to MySQL 5.7, then the core WordPress tables (posts, taxonomies, options, comments, etc.) and any any plugins you installed on the MySQL 5.7 server should still be using the “utf8mb4_unicode_520_ci” collation.
You can import all tables from your database which uses the “utf8mb4_unicode_520_ci” collation. So what you need to do is find the tables in your database which uses “utf8mb4_0900_ai_ci” collation and exclude them from the exported database backup file.
🔎 Find which tables use the “utf8mb4_0900_ai_ci” collation
You need to find which tables in your database uses the “utf8mb4_0900_ai_ci” collation so we can exclude those tables when exporting the database.
If you have SSH access to the server and the database access credentials (which you can totally get from the wp-config.php file), you can run the following command to easily find the tables with “utf8mb4_0900_ai_ci” collation.
mysqlshow -u username -p --status database | grep "utf8mb4_0900_ai_ci"💡 Replace the username and database with your Database and Username in the command above.
Enter your Database user password when prompted to Enter password: and you’ll have the list of tables using the “utf8mb4_0900_ai_ci” collation in your database.
The tables using “utf8mb4_0900_ai_ci” collation should be of plugins only which you installed after switching over to MySQL 8. Write down the names of the tables so you can exclude them the next time you export your database.
💡 Tip
If you don’t SSH access to the server, then download the .sql database file on your computer and open it with a text editor such as Notepad++ and use the search function (Ctrl +F) to find which tables uses the “utf8mb4_0900_ai_ci” collation.
Export database excluding “utf8mb4_0900_ai_ci” collation tables
Now that you have the the names of the tables using “utf8mb4_0900_ai_ci” collation, you can export a new database backup file which doesn’t include the “utf8mb4_0900_ai_ci” tables so you can import it into a WordPress installation running on a MySQL 5.7 server.
Assuming you already use WP-CLI to export/import WordPress database, run the following command to export your database while excluding some of the tables.
wp db export --exclude_tables=table_name,table_name,table_name💡 Replace table_name in the command above with the actual names of the tables which uses “utf8mb4_0900_ai_ci” collation.
That’s it. You can now easily import your WordPress database to the new server running MySQL 5.7.
🗒 Important note
For database tables which you excluded from the backup, be sure to manually re-create their data on the new server. Since those tables are of plugins only, do check if those plugins offer a way to export data in the plugin settings or else reconfigure the plugin on the new server the same way it was setup on the old server.
Member discussion