I’ve been personally using BackupBuddy to help me backup my sites and restore/migrate them. The interface is intuitive and restoring is a breeze. But…
In an ideal world, all backups and restoration/migration of sites using BackupBuddy will be fairly painless but that is not always the case, there will be hosts that uses a legacy environment where the versions of MySQL and PHP that they are using is below the recommended WordPress versions.
To run WordPress we recommend your host supports:
- PHP version 7.2 or greater
- MySQL version 5.6 or greater OR MariaDB version 10.0 or greater
- HTTPS support
Every so often when restoring or importing databases you will encounter this:
Error #9010: Unable to import SQL query. Error: `Unknown collation: 'utf8mb4_unicode_ci'`
There is a fix to this, it is not easy and you will require access to the database. It will be a pain in the ass. If you’d rather not do this fix, you can try asking your host to upgrade their MySQL and PHP versions. If that is not happening anytime soon you can try following the fix below, make sure you have a backup of your site though.
You may want to head to this post to see why this is happening: The utf8mb4 Upgrade
In short, if your database supports utf8mb4 (MySQL version 5.5.3 or higher) WordPress will update your database tables from utf8 to utf8mb4.
- Backup, backup, backup. I could not stress this enough. It would be better to be paranoid and make an XML backup, a database backup, backup of all your files and your BackupBuddy backup.
- Assuming that you have a live site and can still access it. Go to BackupBuddy > Settings > Advanced Settings/Troubleshooting. Scroll down until you see the Database settings then uncheck this: Use separate files per table (when possible)
- Run a database backup, make sure you already have all your files extracted in your server. If not, you will need a full backup.
- Download importbuddy.php
- Open your backup zip file and look for your database backup, if you’ve forced the backup into a single file you will find it inside the database backup file (backup-yoursite-2018_02_26-06_52am-db-RANDOMSTRING/db_1.sql) if on a complete backup it will be in a similar folder structure: backup-yoursite-2018_02_26-06_52am-full-RANDOMSTRING/wp-content/uploads/backupbuddy_temp/RANDOMSTRING/db_1.sql
- You will need to do a search and replace. Find all instances of `utf8mb4` and replace it with `utf8`. If you did not do STEP 2. You will need to do this for all the .sql files in your backup.
- Open wp-config.php and make sure that DB_CHARSET is set as utf8, not utf8mb4.
- Also don’t forget to modify define(‘DB_COLLATE’, ‘utf8_general_ci’);
- Save the file/s, you will see a popup asking you if you want to update the file in the archive. Click Yes. Open the file again to make sure that your changes were added.
- Upload the fixed zip file and importbuddy and run through the BackupBuddy restoration process.
If all else fails, add all your files to your server. Then follow these instructions:
- Log in to phpMyAdmin.
- Select your database and select the “Export” tab.
- For the export method choose “Custom”.
- Scroll down to the “Format-specific options” and select MYSQL40 for Database system or older MySQL server to maximize output compatibility with.
- Scroll to the bottom and click GO.
- Open the sql file on your editor of choice and do a “Search and Replace”. Then you put “utf8mb4” on search and “utf8” on replace.
- Import your fixed sql file to your PhpMyAdmin (destination) and set the DB collation to utf8_unicode_ci (Operations > Collation).
- You will need to do a search and replace again if you’re migrating to a new URL. Replace http://www.oldurl with your old URL and http://www.newurl with the new URL. Run this code in the SQL tab in your PHPMyAdmin, make sure you have the correct database selected.
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl')
WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl');
UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');
- Clear cache and check your handiwork. If it’s not working, please check disclaimer below. Or search Google. Happy Fixing!