MySQL – Migrating Database with UTF8 Collation and Charset to Full UTF-8

MySQL

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

Best Answer

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 */;

/*!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)

Related Solutions

Mysql – When are we supposed to set default-character-set for the client

You have the option of setting character sets/collations on the fly:

While trial-and-error may be necessary, don't go willy-nilly on these variables in /etc/my.cnf. You are better off setting them dynamically during any trial-and-error testing.

To make sure of any corner cases, look at the initial variables of any mysqldump and see if character sets or collations are set in the beginning and reset at the bottom.

In fact, here is a sample for a mysqldump:

/*!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 */;

See the variables

@OLD_CHARACTER_SET_CLIENT
@OLD_CHARACTER_SET_RESULTS
@OLD_COLLATION_CONNECTION
SET NAMES is hardwired to utf8

You could perhaps set these options

when calling the mysqldump
place these options in /etc/my.cnf under [mysqldump] group section
edit these values for existing mysqldumps using perl, awk, etc.

MySQL the.cnf won’t take any effect

The option character-set-database should not be configured in my.cnf.

Please note what the MySQL Documentation says on character-set-database:

The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.

Footnote : This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.

Even the Documentation says it is dynamic, it not supposed to be dynamically by any manual intervention against my.cnf. If you look inside the database subfolder, you will find a file called db.opt. EXAMPLE : When you run use dbname in the mysql client, the file /var/lib/mysql/dbname/db.opt is read in order to set character-set-specific database options contained in that file. For this reason, the variable has to be dynamic.

If you cannot access the database from the OS to see db.opt, simply run this command:

SHOW CREATE DATABASE dbname;

on any database and you will see what db.opt contains (or defaults if db.opt is not there)

mysql> show create database mysql;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

In light of this, you should try setting character-set-server in my.cnf only (or at least remove character-set-database from my.cnf). Then, run service mysql restart.

Give it a Try !!!

UPDATE #1

I sort of dealt with a question like this before : Why default character_set_server is latin1?

Looking back at my old link, I had an idea: I ran this:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.10    |
+-----------+
1 row in set (0.00 sec)

mysql> select * from information_schema.collations where COLLATION_NAME like '%kor%';
+-----------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME  | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-----------------+--------------------+----+------------+-------------+---------+
| euckr_korean_ci | euckr              | 19 | Yes        | Yes         |       1 |
+-----------------+--------------------+----+------------+-------------+---------+
1 row in set (0.00 sec)

mysql>

You could set a Korean character set if need to.

UPDATE #2

You should leave wait_timeout and interactive_timeout out of the [client] and [mysql] groups.