Change MySQL default character set to UTF-8 in my.

Question

Currently we are using the following commands in PHP to set the character set to UTF-8 in our application.

Since this is a bit of overhead, we'd like to set this as the default setting in MySQL. Can we do this in /etc/my.cnf or in another location?

SET NAMES 'utf8'
SET CHARACTER SET utf8

I've looked for a default charset in /etc/my.cnf, but there's nothing there about charsets.

At this point, I did the following to set the MySQL charset and collation variables to UTF-8:

skip-character-set-client-handshake
character_set_client=utf8
character_set_server=utf8

Is that a correct way to handle this?

Answer 1

Change MySQL character:

Client

default-character-set=utf8

mysqld

character_set_server=utf8

We should not write default-character-set=utf8 in mysqld, because that could result in an error like:

start: Job failed to start

At last:

 +--------------------------+----------------------------+
 | 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/ |
 +--------------------------+----------------------------+

Answer 2

If you are confused by your setting for client and conn is reseted after restart mysql service. Try these steps (which worked for me):

1. vi /etc/my.cnf
2. add the contents blow and :wq [client] character-sets-dir=/usr/local/mysql/share/mysql/charsets
3. restart mysql and login mysql , use database, input command status;, you'll find the character-set for 'client' and 'conn' is set to 'utf8'.

Check the reference for more info.

Answer 3

For the recent version of MySQL,

default-character-set = utf8

causes a problem. It's deprecated I think.

As Justin Ball says in "Upgrade to MySQL 5.5.12 and now MySQL won’t start, you should:

1. Remove that directive and you should be good.

2. Then your configuration file ('/etc/my.cnf' for example) should look like that:

   [mysqld]
   collation-server = utf8_unicode_ci
   init-connect='SET NAMES utf8'
   character-set-server = utf8
   

3. Restart MySQL.

4. For making sure, your MySQL is UTF-8, run the following queries in your MySQL prompt:

   • First query:

      mysql> show variables like 'char%';
     

     The output should look like:

      +--------------------------+---------------------------------+
      | 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/local/mysql/share/charsets/|
      +--------------------------+---------------------------------+
     

   • Second query:

      mysql> show variables like 'collation%';
     

     And the query output is:

      +----------------------+-----------------+
      | Variable_name        | Value           |
      +----------------------+-----------------+
      | collation_connection | utf8_general_ci |
      | collation_database   | utf8_unicode_ci |
      | collation_server     | utf8_unicode_ci |
      +----------------------+-----------------+
     

Answer 4

I also have found out that after setting default-character-set = utf8 under [mysqld] title, MySQL 5.5.x would not start under Ubuntu 12.04 (Precise Pangolin).

Answer 5

Note: my.cnf file is located at /etc/mysql/

After adding these lines:

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
skip-character-set-client-handshake

[client]
default-character-set   = utf8

[mysql]
default-character-set   = utf8

Don't forget to restart server:

sudo service mysql restart

Answer 6

MySQL 5.5, all you need is:

[mysqld]
character_set_client=utf8
character_set_server=utf8
collation_server=utf8_unicode_ci

collation_server is optional.

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)