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/
Groggy Man Reads The Man 