MySQLDump Backup of UTF-8 Database Has Character Corruption, But Default Charsets are Correct

Question

When I'm trying to make a full dump of the database as follows:

mysql --default-character-set=utf8 -uUSER -p database -r output.sql

It creates a UTF-8 file but all international data (Chineese,Spanish,Russian) is corrupted as follows(it's Russian part of the dump):

(1,'Ð<9e> наÑ<81>')

However, when I try to dump one table, it works fine: mysql --default-character-set=utf8 -uUSER -p database table_name -r output.sql

I don't quite understand what causes the issue as all possible variables are set to UTF-8:

show variables like "collation_database";
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci | 
+--------------------+-----------------+

show variables like "character_set_database";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  | 
+------------------------+-------+

All tables also have the same charset/collation. There are the following settings in my.cnf

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

And finally, locale is set to utf-8 as well. So what could be the problem of the issue? I'm pretty sure it's a dumb issue but I'm out of the ideas. I would appreciate the assistance greatly.

Answer 1

mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql

Then in shell copy and edit

shell> cp dump.sql dump-fixed.sql
shell> vi or vim dump-fixed.sql
:%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
:%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/

then save the file

Answer 2

I struggled with this tremendously, and tried many online solutions I 've found, such as --default-character-set=utf8 or iconv or vim setfileencoding=utf8, and none of these worked! It was even more frustrating, because I could select properly formatted data in the database itself, and I could select the encoding vars which were all in UTF-8. Anyway!

TLDR Solution:

1. Create the dump as normaly, except with -default-character-set=latin1 --skip-set-charset.
2. Do a manual find/replace of "latin1" with "utf8" (or "utf8mb3", which is just an alias, so, use either).

Full Code Solution:

mysqldump
-u username -pPASSWORD
-h mysql.example.com
--default-character-set=latin1
--skip-set-charset
--no-tablespaces
-N
--routines
--skip-triggers
DATABASENAME > OUTPUTFILE.sql

Or, in one line:

mysqldump -u username -pPASSWORD -h mysql.example.com --default-character-set=latin1 --skip-set-charset --no-tablespaces -N --routines --skip-triggers DATABASENAME > OUTPUTFILE.sql

Essential Code Explanation:

• --default-character-set=latin1 and --skip-set-charset: This stops MySQL from trying to do any actual reconversion of the data, which can break things.
• --no-tablespaces: This resolves an ACCESS-Denied Error caused by a MySQL bug.
• --skip-triggers: Skip doing triggers, but you may need this if you use them.

How Did the Backup Work? Test It:

To restore a dump, simply do in the MySQL prompt:

mysql> create database ExampleDB;
mysql> use ExampleDB;
mysql> source ExampleDB-backup.sql;