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:
- OS (and version).
- MySQL (and version).
- Default MySQL config.
- Your schema.
- 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
Hey Kolya, thanks for sharing your workflow. Worked for me, too! 😊
Thank you! Glad to hear.
hi,
where do i type all this to export? sorry not an expert
hi,
where do I type all this to export? sorry not an expert (post this as i enabled email notification)
Hi. All of this commands you should type and run in terminal. You can connect to server with MySQL via SSH and run commands.
Hi,
Just a quick note to say thank you! I just spent several hours trying to migrate a utf8mb4 database and this is the method that actually worked 🙂
Ah, just found this comment in moderation stack. Thank you for your reply, glad to hear that you have solved your issue.
For some reasons, some emojis show and some are gone, see this:

Any ideas why?
Hm, it looks strange. Can you post anywhere emojis which was transformed to “????”?
Emojis are gone after database migration – WordPress Solutions Group
Emojis are gone after database migration – WordPress Solutions Group
Thank you vary much!!!
Only solution that work for me!!!