WP-CLI Database Backups with Emojis


My preferred way to backup a WordPress site is directly on the web server using SSH. The database backup part is performed with WP-CLI like so:

wp db export --add-drop-table

This will output a .sql file which can easily be imported. I’ve been using this method for nightly database backups for the last few years. However, I recently discovered a rather disturbing issue. The default wp db export fails to save emojis properly. Now I know not every one uses emojis, however for me this would be quite a disaster if I wasn’t able to restore my blog with emojis. 😢

Verify emojis within .sql files 👌

This is not a database import issue but rather an issue in the way the database exports are made. If you use WP-CLI to create database dumps, then I’d highly recommend you scan the database backup for emojis.

In my case the .sql files exports did not contain my emojis. That resulted in emojis like 🚤 being translating to “???”. You can scan for emojis using grep as shown here. If you see emojis in the results, then everything is working properly.

grep --include "*.sql" -rn "🚤" wp-content/

If not then change the database character set used on export.

I wasn’t the first to run into this particular issue: https://github.com/wp-cli/wp-cli/issues/3949. Ok so unicode is quite complex. I haven’t dug in too deep, however I suspect the native WP-CLI export is using a slightly different database character set which causes the emojis to get jumbled. The workaround is to force the character set on export to match the database character set. For both Kinsta and WP Engine, defining utf8mb4 seems to resolves the issue.

wp db export --add-drop-table --default-character-set=utf8mb4