MySQL: Migrating database with utf8 collation and charset but latin1 data to new full UTF-8 database

Question

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

Answer 1

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)