Import and export WordPress database with utf8mb4 charset

Here is a short guide how to export and import your WordPress database without data loss. UTF8MB4 charset is tricky and sometimes your database may looks ok but take your time. Double check everything. I lost some data after migrating site between two servers and afterward spend more time to back all the things. Just my experience.

And first advice which you should to remember – don’t doing exporting and importing with PHP My Admin because you can’t control this process. Your export and import operation can depends on many factors:

1. OS (and version).
2. MySQL (and version).
3. Default MySQL config.
4. Your schema.
5. Tools which you use for exporting and importing.

Export

Creating dump of your database with mysqldump is the best option. You fully control everything. For example you can see the error.log after export and make sure that everything is ok. Here is command for creating best export ever (for WordPress).

mysqldump \
  -P 3306 \
  -h 127.0.0.1 \
  blog \
  --add-drop-database=FALSE \
  --add-drop-table=FALSE \
  --all-databases=FALSE \
  --all-tablespaces \
  --comments \
  --complete-insert \
  --create-options=TRUE \
  --default-character-set=utf8mb4 \
  --dump-date \
  --lock-tables=TRUE \
  --log-error=error.log \
  --no-create-db=TRUE \
  --no-create-info=FALSE \
  --no-data=FALSE \
  --password="YOUR_PASSWORD" \
  --result-file=blog.sql \
  --set-charset=TRUE \
  --skip-add-drop-table=TRUE \
  --skip-add-locks=FALSE \
  --skip-comments=FALSE \
  --skip-compact=FALSE \
  --skip-set-charset=FALSE \
  --user=blog;

If you are interesting in understanding this parameters or need more check the official MySQL docs.

Import. MySQL. Not Work

The import process is more complicated for first glance. The most people doing importing like this.

mysql \
  --user=korobochkin \
  --password="password" \
  --default-character-set=utf8mb4 \
  dbName < dbDump.sql

But it doesn’t work. Maybe it depends on server configuration maybe not. But as result of this operation I’ve lost all the 4 bytes emoji in my database (schema). You can notice “?” symbols instead of 4 (four) bytes symbols.

I suppose that problem is how dbDump.sql is read from the disk and MySQL gets not properly data. But after checking I see that something wrong here.

mysql \
  --user=korobochkin \
  --password="password" \
  --default-character-set=utf8mb4 \
  dbName

# Run in MySQL
SHOW VARIABLES LIKE  'char%';

# Result

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

As you can see parameter default-character-set not affects connection charset.

Import WP CLI. Not Work too

My next idea was doing import with WP CLI since this tool is great and luckily have wp db import command which looked promised. But it doesn’t work too. After import I see that emoji is gone.

Import. The right way

And finnally here the solution which works fine for me.

mysql \
  --user=korobochkin \
  --password="password" \
  < import-db-commands.sql \
;

# The commands bellow in import-db-commands.sql

# Select desired DB
USE dbName;

# Select the right charset
SET NAMES 'utf8mb4';

# Import DB from sql file
SOURCE db-dump.sql;

# Disconnect from SQL server
EXIT

Advertisements