Mysql: Set default character set

If you got problems with any software trying to store/save properly non English characters into MySQL and can't figure what is going on then try out the following changes into my.cnf. Under Redhat Enterprise Server or CentOS the my.cnf usually is located under /etc/mysql. Other Linux distributions might have this under /etc. Anyway just edit the file and add the following.

First change is under [client] section, just add:

default-character-set=utf8

Then move on under [mysqld] and add:

init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
default-character-set=utf8
character-set-server = utf8
collation-server = utf8_general_ci

and finally under [mysql] add:

default-character-set=utf8

Check that everything works as expected:

backup:/tmp# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 708
Server version: 5.0.51a-24+lenny4-log (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:        708
Current database:   
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.0.51a-24+lenny4-log (Debian)
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:            8 min 15 sec

Threads: 7  Questions: 5435  Slow queries: 182  Opens: 3479  Flush tables: 1  Open tables: 64  Queries per second avg: 10.980
--------------

mysql>

Posted on: 24/02/2011








0 Comments
If you want to leave a comment please Login or Register