How do you mysqldump specific table(s) with UTF8 Encoding?

Question

When I am making a backup using utf8 / utf8mb4 encode, my table query returns with unrecognized characters.

Here's the backup command I am using in C#:

"-u{0} -p{1} -c -e --default-character-set=utf8 dbname tablename"

output:

    -- MySQL dump 10.13  Distrib 5.6.24, for Win32 (x86)
--
-- Host: localhost    Database: dbname
-- ------------------------------------------------------
-- Server version   5.6.24

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `tablename`
--

DROP TABLE IF EXISTS `tablename`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tablename` (
  `Sno` int(11) NOT NULL DEFAULT '0',
  `ShopItemcode` varchar(255) DEFAULT NULL,
  `Itemname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tablename`
--

LOCK TABLES `tablename` WRITE;
/*!40000 ALTER TABLE `tablename` DISABLE KEYS */;
INSERT INTO `tablename` VALUES (992,'JPBC20','à®œà¯€à®©à¯à®¸à¯(à®ªà¯‚à®Ÿà¯à®•à®Ÿà¯)à®šà®¿20'),(993,'JPBC22','à®œà¯€à®©à¯à®¸à¯(à®ªà¯‚à®Ÿà¯à®•à®Ÿà¯)à®šà®¿22'),(994,'JPBC24','à®œà¯€à®©à¯à®¸à¯(à®ªà¯‚à®Ÿà¯à®•à®Ÿà¯)à®šà®¿24');

But, when I am take backup in Xampp means the characters returns good.

I have written a C# Native Library, you can give it a try. It is an alternative to MySqlDump: https://github.com/adriancs2/MySqlBackup.Net — mjb, Aug 19 '17 at 05:21

score 1 · Answer 1 · edited May 23 '17 at 12:34

Tamil? Starting with ஜீன...? You have Mojibake. That is, somewhere in the path, something was declared latin1 instead of utf8.

Read about Mojibake here: Trouble with utf8 characters; what I see is not what I stored . See if its suggestions provide the solution.

There is a chance that it is "double-encoded". SELECT HEX(Itemname) ...; ஜீன will be

E0AE9C E0AF80 E0AEA9 in correctly stored utf8, or
C3A0 C2AE C593 C3A0 C2AF E282AC C3A0 C2AE C2A9 if double-encoded.

To fix the data, if you can't easily reload, see http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases

ya right Tamil. thanks for the details about encoding. – mathi May 04 '17 at 05:53 — mathi, May 04 '17 at 05:53

score 0 · Answer 2 · answered May 04 '17 at 06:06

I succeed as follows:

I export my table as latin1 charset, It returns my query well, but only the other Language values are returned as double encoded. When I import the query with utf8 charset, it returns double encoded data as original.

Like this,

mysqldump -uuser -ppassword --default-character-set=latin1

mysql -uuser -ppassword --default-character-set=utf8

How do you mysqldump specific table(s) with UTF8 Encoding?

2 Answers2