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
Leave a reply to Kolya Korobochkin Cancel reply