mysqldump with utf8 can not export the right emojis string

Question

I am using MySQL 5.5.29, utf8mb4 charset, there is a table user containing a field nickname with value hex F09F988EF09F988E that translates to the emojis 😎😎.

Now open MySQL console, and execute:

---

set names utf8mb4;
select nickname, hex(nickname) from user;

---

nickname | hex(nickname)
---------+-----------------
😎😎    | F09F988EF09F988E

---

And then execute:

mysqldump --default-character-set=utf8 -utest -ptest test_dev user > user.sql

Check the user.sql and find the nickname display ?? which hex string is 3f

So, how can mysqldump with UTF8 export the right emojis string?

---

btw, the database charset envionments configured as follow: show variables like 'character_set_%':

'character_set_client', 'utf8mb4'
'character_set_connection', 'utf8mb4'
'character_set_database', 'utf8mb4'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8mb4'
'character_set_server', 'utf8mb4'
'character_set_system', 'utf8'
'character_sets_dir', '/data/mysql/share/charsets/'

Bikini

What happens when 1) You change the default character set to "--default-character-set=utf8mb4" 2) You export directly to a file e.g. execute "mysqldump --default-character-set=utf8mb4 -utest -ptest test_dev user -r user.sql" – Goldfish

Also, have you tried re-importing the data? It may just be that your editor is displaying the question marks as it doesn't have the correct characters available in it's font. – Goldfish

Answer 1

Thanks Danack!
Thru specifying utf8mb4 charset and upgrading mysqldump version to 5.5.3+, mysqldump & mysql work well for 4 bytes emojis.

[tomcat@localhost ~]$ mysqldump --default-character-set=utf8mb4 -utest -ptest test_dev user > user.sql

If it shows an error like:

mysqldump: Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file

check your mysqldump version (mysqldump --version)

[tomcat@localhost ~]$ mysqldump --version
mysqldump  Ver 10.11 Distrib 5.0.95, for redhat-linux-gnu (x86_64)

It works after upgrading mysqldump to 5.5.33.

[tomcat@localhost ~]$ mysqldump --version
mysqldump  Ver 10.13 Distrib 5.5.33, for Linux (x86_64)

Bikini

related: As of this date the export tool in MySql Workbench hardcodes the default-character-set option to utf8 ...*sigh*.. – Styles

@BradKent Fun-Fact: It's thanks to MySQLWorkbench that I got aware of this solution at all haha. Worked like a charm with utf8mb4 :) – Bilek

Answer 2

It's true that you need to use mysqldump --default-character-set=utf8mb4 (notice the --default-character-set option) when exporting.

But then importing is still super tricky. I tried so many different approaches and had no success.

Finally, I discovered that you need to create an importer.sql file like this:

USE my_example_db_name;

# Select the right charset
SET NAMES 'utf8mb4';

# Import from SQL file
SOURCE /somewhere/dump.sql;

# Disconnect from SQL server
EXIT

Then, to import, run this:

mysql -u my_user my_example_db_name < /somewhere/importer.sql

Thank you to https://korobochkin.wordpress.com/2017/02/25/import-and-export-wordpress-database-with-utf8mb4-charset/

Marcellus

--set-charset should have the same effect should it not? – Cricket

Answer 3

Was struggling with this for a while as well. The other solutions in this thread still caused dump.sql to still have multiple wrong characters for emojis. Turns out, using > is not a safe way of exporting (at least not on my machine, Windows 10). Using -r dump.sql instead of > dump.sql did the trick.

This command exports all the tables of the database:

mysqldump --default-character-set=utf8mb4 -h [host] -u [username] -p [database_name] --set-gtid-purged=OFF --port=3306 --protocol=tcp --skip-triggers -r dump.sql

Then for importing, first run this command:

mysql -h [host_name] -u [username] -p [database_name] --binary-mode -o

And then, when in mysql mode, type the following:

USE database_name;
SET NAMES 'utf8mb4';
SOURCE /dump.sql;
EXIT

Hope this helps others that had the same issue!

Dann