convert MySQL db from Latin1 to UTF8

All questions related to servers
Post Reply
mister_v
Posts: 188
Joined: Thu Mar 04, 2010 9:19 pm

convert MySQL db from Latin1 to UTF8

Post by mister_v »

Hi,

My MySQL-database is set to latin1,
but I want to convert it to UTF8.

I already added

Code: Select all

default-character-set=utf8
to /etc/mysql/my.cng

But I still get the following:

Code: Select all

Connection id:          371
Current database:       db
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.63-0ubuntu0.11.10.1 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysqld/mysqld.sock
The server and the databse are still in latin1.
What else should I do?
chris
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: convert MySQL db from Latin1 to UTF8

Post by chris »

You probably set

Code: Select all

default-character-set=utf8
Under [client] in /etc/mysql/my.cnf

You also need to set it under [mysqld];
Actually

Code: Select all

character-set-server=utf8
would be better.

This should take care of the Server characterset.


For your database you have to do some additional steps.
in MySQL give the following command:

Code: Select all

ALTER DATABASE <database_name> CHARACTER SET utf8;
This sets the default of your database to utf8.
New tables will be created in utf8,
HOWEVER already created tables remain in the old character set.
This command converts the table:

Code: Select all

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8;
Always remember to do backup before you do anything.
I don't guaranty that all data is converted correctly.

You can check every steps with the following commands:
STATUS; : Show client,server and database settings.
SHOW TABLE STATUS; : Show all table settings.
SHOW FULL COLUMNS FROM <table_name>; : Shows info about the columns in a table.
Post Reply