6

With my current situation I have mysql database with collation and character sets on database and tables set to utf8, but all data is still latin1. This is due to server being configured (my.cnf) with:

character-set-server = latin1

Here is more information on old database server:

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| 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     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Now we have new properly configured database instances with full UTF-8 and I need to dump the data. I have been trying it different ways without any luck. The data now on original database is garbled and I need to export it to full UTF-8 database.

I have tried this:

mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
--skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql

mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
--default-character-set=utf8 DB_NAME < DB_NAME-dump.sql

But it does not help as the data in .sql files and in new database is garbled UTF-8

  • 1
    Btw, MySQL's 'utf8' is not full UTF-8 because it only supports characters up to 3 bytes. Fields are truncated at the first 4 byte character (💩). Use 'utf8mb4' for better UTF-8 support: stackoverflow.com/a/24487309/664132 – basic6 Jul 10 '14 at 10:06
6

After reading various issues and trying different ways, this was the solution for me:

The dump has to be done like this (make sure you pass the filename as -r argument):

mysqldump -h HOST -u USER -p --default-character-set=latin1 DATABASE -r utf8.dump

Then I have opened utf8.dump file and changed one line at the top.

From

/*!40101 SET NAMES latin1 */;

To

/*!40101 SET NAMES utf8 */;

Then I imported it to (make sure to use mysql instead of mysqldump and source the file, do not use "<" to pass it from terminal):

mysql -u USER -p DB -h HOST

mysql> source utf8.dump

After this, all my data was correctly encoded and stored as UTF-8 (e.g. hét -> hét)

  • Almost worked for me, but special chars were still messed up. Then I inserted an additional step before the re-import: recode lat1..utf8 utf8.dump – that fixed the broken chars. Now it looks fine, thanks :) – Izzy Feb 13 '15 at 23:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.