Migrating a WordPress Site from utf8mb4 to utf8

Starting in version 4.2, WordPress will attempt to upgrade its database tables from utf8 to utf8mb4. You can read more about the change on make.wordpress.org if you’re into that kind of thing, but most of the time it won’t affect you. Most of the time.

It did affect me though. I ran into an issue where I’d built a site on a development environment running on MySQL 5.5 which supports utf8mb4, but had to launch it on Media Temple’s Grid service, which runs MySQL 5.1 and doesn’t support utf8mb4. I saw a few people recommending exporting the 5.5 database in phpMyAdmin using MYSQL40 compatibility and importing that into your 5.1 environment, but when I tried it, it messed up some serialized Beaver Builder data.

Eventually I discovered that WP Migrate DB is the way to go. The free plugin allows you to export your data with URL and file paths automatically replaced, and pre MySQL 5.5 compatibility. I ran it on the development site and got an export that I was able to import directly into the live, 5.1 environment without errors or corrupted serialized data.

I’m not sure how much of an edge case my situation is, but hopefully this is helpful to someone. Any other work arounds you’re aware of?

21 Replies to “Migrating a WordPress Site from utf8mb4 to utf8”

  1. Unfortunately this only works in quite simple setups. First the export still has this line:
    /*!40101 SET NAMES utf8mb4*/;

    Also simply changing utf8mb4 to utf8 (what is seems to do) will cause most special characters like german umlauts to be broken.

    Thanks for the tip anyway, just not quite what I was looking for.

  2. I solved this (on copy of database) by running:

    #Change default database charset and collation:
    ALTER DATABASE valeotechday CHARACTER SET utf8 COLLATE utf8_general_ci;

    #List all tables in database, to modify the queries bellow, to contain all used tables:
    SHOW TABLES;

    #Execute conversion of all character columns to utf8 in used tables:
    ALTER TABLE wp_commentmeta CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_comments CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_content_status CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_core_status CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_flags CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_languages CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_languages_translations CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_locale_map CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_message_status CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_node CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_reminders CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_string_positions CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_string_status CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_string_translations CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_strings CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_translate CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_translate_job CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_translation_batches CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_translation_status CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_icl_translations CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_links CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_options CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_postmeta CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_posts CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_term_relationships CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_term_taxonomy CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_termmeta CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_terms CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_usermeta CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    ALTER TABLE wp_users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

  3. OMG! Man, YOU ARE THE BEST….
    I searching at google, anything try but nothing help. To export I try anything change, in SQL run anything command to change… But this export and finish… Voooow! Thanks Man!

  4. Jan Mazának’s tip above did it for me, thanks man!

    There was still actually one renegade table where the ALTER TABLE command wasn’t working, so I just manually removed the ‘COLLATE=utf8mb4_unicode_520_ci’ part from the .sql file.

  5. You are the best!!!

    Después de tres días buscando soluciones, creando scripts y volviéndome loca ya que no soy experta en el temo una solución tan sencilla como este plugin me ha salvado la vida y la web. Mil gracias!!! Voy a hacer link desde mis webs

    Thank you!!!!

  6. It works but not directly. I make sure you set this on your wp-config.php file:

    /** Database Charset to use in creating database tables. */
    define(‘DB_CHARSET’, ‘utf8’);

    then change “utf8mb4” to “utf8” on the sql dump.

  7. You’re Awesome Man!!! i really appreciate your big efforts to help beginners like me. Thanks again for helping us. 🙂

  8. Sure a lifesaver!!

    As Others have said I’ve been searching google for a solution to the problem and I finally found this.

    Had trouble with SQL imports/exports recently and this is such an easy solution. Worked perfectly.

    Thanks for posting this!

  9. Thanks for the tip.

    I want to migrate my website from localhost to live server. How best to proceed on this? As I understand, I install the plugin on localhost and export the database. Do I need the plugin on the server for importing? What about the other website contents (wp-contents, wp-includes etc) – do I just upload these via ftp?

    Would appreciate some directions or a link.

    Thanks.

    1. You would use this plugin to export the database on localhost. During the database export, you’d have the option to update your site URL and server paths to whatever they’d be on the new live server. You’d then use a tool like phpMyAdmin on the server to import the .sql export you got from WP Migrate DB on localhost. If you have a fresh install of WordPress on the live server, you’d only need to move wp-content, but if you’re starting from scratch, you could move all the files via FTP.

Leave a Reply

Your email address will not be published. Required fields are marked *