Setting up MySql server with utf-8 charset(s)

I can’t remember how many times I installed mysql server or mysql client on some Linux machines (occasionaly on Windows also) and forgot to change the charset of both client, connection, server etc.
For me being from non-latin culture and also working in such an enviroment it is always a step plus and torment while configuring the mysql servers to make them workable with utf8.
With that tought in mind, the purpose of this post is to give you fast solution on setting up utf8 or any other charset in your servers or development machines.
I also suggest making a skeleton my.cnf file and copy-pasting it on new instances of MySql.

Log in on mysql console and execute the query: mysql> show variables like ‘%char%’;

This will give you probably an output like this:

mysql> show variables like ‘%char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)

(sorry for bad formatting)

Well it’s latin1 for the client, connection, current selected database, results, and whole server. Filesystem and system are fine . Let’s do the changes that are needed and set up utf-8.

Open up /etc/my.cnf or /etc/mysql/my.cnf (depending od distribution) with some editor (Vim, nano …). As we know MySql configuration is set in blocks that are categorized by function or logical part of the full system where we put different MySql parameters that are appropriate for that part of mysql. The beginning of this blocks is marked with square brackets, like [mysqld].

That are some basic stuff, the changes that are needed are following.

For the character_set_client in the block [client] we should set default-character-set=utf8 .

[client]
default-character-set=utf8

The encoding  and collation type of whole server and all new created databases will be set by the following parameters:

[mysqld]
character_set_server=utf8
collation_server=utf8_general_ci
init-connect=’SET NAMES utf8′      # A string to be executed by the server for each client that connects

(be aware of the group [mysqld])

A change is also needed in [mysql] for the mysql console client.

[mysql]

default-character-set=utf8

Remember you can always change this parameters by database scope, this is just the default behaviour of the server. You can also do system variables changes on runtime but not everything evaluates immediately. Of course the system variables have scopes of their own, some are global some or not.

See the following documentation for diving in : http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

That are the changes we do in the default option file. Remember, the existance of this file is recommended not needed for mysql to work.

Next we should restart the server and print the char variables.

This is the output:

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     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)

On Windows I use the Mysql Workbench program for managing , its operable on Linux too.

Have a nice day ! o/

1 thought on “Setting up MySql server with utf-8 charset(s)”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s