24 June 2011

Switch to UTF-8 charset in Mysql on Ubuntu

When installing Mysql on Ubuntu the default character set is probably latin-1. Since Ubuntu uses UTF-8 for most other things this may be little strange. But it is easy to change.

The Mysql configuration file /etc/mysql/my.cnf has a magic line:
!includedir /etc/mysql/conf.d/

This will make it include settings on the subdirectory conf.d. It's not recommended to change the my.cnf file directly since it will cause problems when upgrading Ubuntu/Mysql to a new version.

Create a new file: /etc/mysql/conf.d/utf8_charset.cnf with the following contents:
[mysqld]
default-character-set=utf8

[client]
default-character-set=utf8

Restart mysql and you will have UTF-8 as character set:
$ mysql -u root -p -e "show variables like '%character%'"
+--------------------------+----------------------------+
| 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     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+ 

Update 2012-11-12: This is valid for Mysql 5.1. For Mysql 5.5 see comment below by Martijn.

6 comments:

  1. This is exactly what I've been looking for. Thank you.

    ReplyDelete
  2. Wauw, the options really change across mysql versions, very confusing! This works perfectly on Ubuntu Server 10.04, Mysql 5.1.61. You might also want to run:

    mysql -u root -p -e "show variables like '%collation%'"

    Thanks!

    ReplyDelete
  3. The above option is deprecated on MySQL 5.5. Use the following instead:

    [mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci

    ReplyDelete
    Replies
    1. Thank you. This works for me. When i use "default-character-set=utf8" it couses this error: "start: Job failed to start".

      Delete
  4. Oh man, you save me a day, Thanks

    ReplyDelete