BackupBuddy is a WordPress Plugin with lots of options, like scheduled backups to remote locations etc. It’s also helpful if you want to migrate your WordPress installation to another server, but in some cases it just returns the following error messages when trying to import the MySQL Tables (using included ImportBuddy) to your new MySQL server:
Error #9010: Unable to import SQL query. Error: `Unknown collation: ‘utf8mb4_unicode_520_ci’` Errno: `1273`. Click to view error details in the Knowledge Base
Followed by lots of these:
Error #9010: Unable to import SQL query. Error: `Table ‘mysqldb.wp_commentmeta’ doesn’t exist` Errno: `1146`. Click to view error details in the Knowledge Base
If you click on the provided links you’ll only get some very common information that something did not work….
The problem is caused by collation not available on your target system. You could read more about collation names on MySQL Documentation.
So here is short information how to fix that problem.
Option 1: Upgrade your target MySQL Server
The problem is caused by an old version of MySQL on your target system. Therefore the best option is of course to update your target MySQL Server to the same version (or at least 5.6) as your source MySQL Server. That should fix your problems. But there might be configurations where upgrading the target MySQL-Server is not an option, so have a look at another option:
Option 2: Update script
The second (and also second-best) option is to update the script of BackupBuddy. Extract the zip file containing the backup files you want to restore. The BackupBuddy Scripts are stored in \wp-content\uploads\backupbuddy_temp. This directory should contain another subdirectory with a random string. Within this directory you’ll find lots of scripts named “[mysqltable].sql”.
Open the first script, e.g. “wp_commentmeta.sql” with a text editor like Notepad++. Within the script you’ll find the collation causing the problems:
As collation ‘utf8mb4_unicode_520_ci’ does not seem to be available on your target system we have to replace this with the collation ‘utf8_unicode_ci’ (so without version number).
Fortunately with Notepad++ it’s quite easy to do this for all the files: Go to ‘Search’ -> ‘Find in Files’. Enter ‘utf8mb4_unicode_520_ci’ to be replaced by ‘utf8mb4_unicode_ci’.