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

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?

Note that a better default would be `utf8mb4`, i.e. real UTF-8 with full Unicode support. See [How to support full Unicode in MySQL databases](http://mathiasbynens.be/notes/mysql-utf8mb4). — Mathias Bynens, Jul 31 '12 at 09:22
@Jorre would you object to changing this to be `utf8mb4` this is kind setting a dangerous precedent as it sits? — Evan Carroll, Jun 22 '18 at 20:27

score 479 · Answer 1 · edited Dec 24 '20 at 15:02

479

To set the default to UTF-8, you want to add the following to my.cnf/my.ini

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4


[mysqld]
collation-server = utf8mb4_unicode_520_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

If you want to change the character set for an existing DB, let me know... your question didn't specify it directly so I am not sure if that's what you want to do.

Edit: I replaced utf8 with utf8mb4 in the original answer due to utf8 only being a subset of UTF-8. MySQL and MariaDB both call UTF-8 utf8mb4.

edited Dec 24 '20 at 15:02

PHP Guru

1,301
11
20

answered Aug 18 '10 at 15:34

NinjaCat

9,974
9
44
64

thanks, I solved it this way, can you let me know if that's also a correct way to handle this? skip-character-set-client-handshake character_set_client=utf8 character_set_server=utf8 – Jorre Aug 18 '10 at 16:35
20

The above my.cnf settings worked for me as well. Additionally, I had to make sure the table was set properly, such as ALTER TABLE `Table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; – Chris Livdahl May 26 '11 at 19:26
9

Does not work for mysql 5.5. I used:
[mysqld] # Changes for utf-8 collation-server = utf8mb4_unicode_ci init-connect='SET NAMES utf8mb4' character-set-server = utf8mb4 and utf8mb4 elsewhere as mentioned above. – Champ Oct 04 '12 at 15:07
12

On Ubuntu 12.04, this worked for me - if I removed the first line after `[mysqld]`. – Brandon Bertelsen Dec 23 '12 at 02:53
4

It looks like default-character-set is no longer allowed in the [mysqld] section – marsbard Mar 24 '13 at 10:33
This one worked for me Debian 6.0make sure you have collation-server = utf8_unicode_ci – Marin Jun 15 '13 at 15:00
5

Note that if UTF-8 is what you want, don’t use MySQL’s `utf8` charset. [Use `utf8mb4` instead.](http://mths.be/utf8mb4) – Mathias Bynens Feb 19 '14 at 09:47
3

Beware: from mysql docs: The content of init_connect is not executed for users that have the SUPER privilege. http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_character_set_server – ling Nov 03 '14 at 22:20
It's unclear to me why this answer and many others continue to set `init-connect` and `default-character-set` for `[client]` and `[mysql]` when it seems that adding `skip-character-set-client-handshake` to the `[mysqld]` section accomplishes both. Is there something about that approach that I'm missing? – Justin Watt Sep 23 '15 at 22:15
4

Small input, consider using `utf8mb4` for the charset and `utf8mb4_unicode_ci` for the collation to have all of utf8 covered. See https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci – Mayeu Sep 15 '16 at 12:54
In [mysqld] `collation-server = utf8_unicode_ci` and `init-connect='SET NAMES utf8'` were bugging my my.ini – FtheBuilder May 12 '17 at 14:38
I dont see [mysql] in `my.ini` file. I am using Mariadb 10.2 on windows 10. Should I create it and add that line? – Murtaza Haji Jun 03 '20 at 02:37

score 263 · Answer 2 · edited Aug 01 '14 at 00:14

263

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:

Remove that directive and you should be good.

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

Restart MySQL.

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

edited Aug 01 '14 at 00:14

Peter Mortensen

30,738
21
105
131

answered Jun 02 '12 at 23:55

Mustafah

4,447
2
24
24

Is your first line max_allowed_packet=64M related to this UTF8 issue in any way? – malhal Nov 29 '12 at 03:40
I have `character_set_filesystem | utf8`. I'm not sure, is it ok? – uncle Lem Feb 20 '13 at 14:33
1

Tried on MariaDB v5.5 and it works, thanks! `mysql Ver 15.1 Distrib 5.5.35-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1` – cenk Feb 16 '14 at 16:17
I think you don't need to remove `default-character-set`, instead you can change to `loose-default-character-set = utf8` — that is, prefix with 'loose-'. This makes `mysqlbinlog` happy, if you need to use it — google for: *"loose-default-character-set" mysqlbinlog*. – KajMagnus Mar 12 '14 at 10:16
Is `init-connect='SET NAMES utf8'` really necessary? If not we can definitely do without it for performance. – datasn.io Jul 14 '14 at 01:07
@Mustafah, Why is the `character_set_filesystem` still showing `binary` and not `utf8`? – Pacerier Oct 18 '14 at 21:29
I need skip-character-set-client-handshake, otherwise some variables are still unchanged. – cn123h Nov 10 '15 at 19:23
Because of a lack of support for numerous widely-used characters, and the high stakes (data truncation, query failure) of an unsupported character, most people are going to want to use `utf8mb4` and not `utf8`, and the corresponding collation `utf8mb4_unicode_ci` or a similar one. – cazort Aug 27 '21 at 17:30

score 64 · Answer 3 · edited May 23 '17 at 12:26

This question already has a lot of answers, but Mathias Bynens mentioned that 'utf8mb4' should be used instead of 'utf8' in order to have better UTF-8 support ('utf8' does not support 4 byte characters, fields are truncated on insert). I consider this to be an important difference. So here is yet another answer on how to set the default character set and collation. One that'll allow you to insert a pile of poo ().

This works on MySQL 5.5.35.

Note, that some of the settings may be optional. As I'm not entirely sure that I haven't forgotten anything, I'll make this answer a community wiki.

Old Settings

mysql> SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Config

#  
# UTF-8 should be used instead of Latin1. Obviously.
# NOTE "utf8" in MySQL is NOT full UTF-8: http://mathiasbynens.be/notes/mysql-utf8mb4

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

New Settings

mysql> SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

character_set_system is always utf8.

This won't affect existing tables, it's just the default setting (used for new tables). The following ALTER code can be used to convert an existing table (without the dump-restore workaround):

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Edit:

On a MySQL 5.0 server: character_set_client, character_set_connection, character_set_results, collation_connection remain at latin1. Issuing SET NAMES utf8 (utf8mb4 not available in that version) sets those to utf8 as well.

Caveat: If you had a utf8 table with an index column of type VARCHAR(255), it can't be converted in some cases, because the maximum key length is exceeded (Specified key was too long; max key length is 767 bytes.). If possible, reduce the column size from 255 to 191 (because 191 * 4 = 764 < 767 < 192 * 4 = 768). After that, the table can be converted.

I followed your settings, but I find `character_set_database` is still `utf8` and `collation_database` is utf8_bin`. Did I miss something? — Stewart, Jan 30 '16 at 15:04
Found what I missed. Those 2 settings are set when the database itself is created. See this question; http://stackoverflow.com/questions/22572558/how-to-set-character-set-database-and-collation-database-to-utf8-in-my-ini — Stewart, Jan 30 '16 at 15:15
add `character-set-client-handshake = FALSE` to [mysqld] section, so it will always use default encoding even if you will make mistake on application layer — Lukas Liesis, Nov 10 '16 at 12:43
hi I have tried this solution but still I can't show character_set_client | utf8mb4. it's utf8. just character_set_database utf8mb4 and character_set_server utf8mb4 are utf8mb4 other not changed. Please help me — Bhavin Chauhan, Dec 16 '16 at 09:52
@baic6 I suggest adding a note clarifying why only indexed columns can cause the issue. 767 bytes limitation is only for indexed columns, coz the limitation for "key"s. For non-indexed columns in general, this doesn't apply. If a column had value with max byte length, say 255 characters and the max 3 bytes of utf8 were used, it would still need only 255*3 bytes, utf8mb4 in these cases will use only 3-bytes, as these characters in utf8mb4 also need only 3 bytes. That is, unless a character that doesn't fit in utf8 was stored into the column in which case the info is already lost. — sam, Apr 09 '19 at 19:06

score 55 · Answer 4 · answered Feb 19 '12 at 15:24

55

On MySQL 5.5 I have in my.cnf

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

Result is

mysql> show variables like "%character%";show variables like "%collation%";

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

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

answered Feb 19 '12 at 15:24

tomazzlender

1,123
12
22

2

What does `skip-character-set-client-handshake` do exactly and under what circumstances is it required? – Simon East May 09 '12 at 23:44
3

@Simon, when using `show variables like "%collation%";` I was seeing 'collation_connection' as `utf8_general_ci` and collation_database and collation_server as `utf8_unicode_ci`. Adding the line `skip-character-set-client-handshake` changed the general entry to unicode, so all three were consistent. – Vaughany May 29 '12 at 22:11
2

On MySQL 5.6, the skip-character-set-client-handshake does not do what @Vaughany said. Any suggestion? – Ababneh A Sep 27 '12 at 12:43
4

The `skip-character-set-client-handshake` did it for me (along with the "character-set-server" and "collation-server", of course -- on Debian 7). The `init_connect`s did not make a difference with or without the `skip` and seem redundant. +1 and thank you. – Jeff Jul 27 '13 at 14:43
3

[skip-character-set-client-handshake](http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_character-set-client-handshake) ignores character set information sent by the client. It's nice for forcing a specific character set during communication, but if the client is expecting something else, it could lead to issues. I'd say you're better off specifying this on the client (your application). – 0b10011 Jan 28 '14 at 20:17
1

This three lines added to the mysqld section of the configuraton solved the issue for me in Ubuntu-14.04+mysql-5.5.38 **skip-character-set-client-handshake** is mandatory, it will not work without this line. – jhcaiced Aug 13 '14 at 17:31
@jhcaiced, That depends on your client. Which client are you using? – Pacerier Feb 06 '15 at 10:07

М.Б. · Answer 5 · 2017-11-10T07:20:35.240

35

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

edited Nov 10 '17 at 07:20

answered Aug 06 '16 at 23:06

М.Б.

1,308
17
20

2

Note: This changes defaults; it does _not_ change the encoding for any existing columns. – Rick James Apr 19 '17 at 16:10

score 28 · Answer 6 · edited Sep 23 '15 at 23:00

28

MySQL v5.5.3 and greater:

Just add three lines only in the [mysqld] section:

[mysqld]
character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake

Note: Including skip-character-set-client-handshake here obviates the need to include both init-connect in [mysqld] and default-character-set in the [client] and [mysql] sections.

edited Sep 23 '15 at 23:00

Justin Watt

740
1
10
17

answered Jan 23 '14 at 06:01

mug896

1,777
1
19
17

2

Wow, so many duplicative answers to this question, and yet this seems to be the simplest one. Also works for me on v5.5.44. – Justin Watt Sep 23 '15 at 22:21

score 24 · Answer 7 · answered Dec 31 '11 at 20:33

24

NijaCat was close, but specified overkill:

To set the default to UTF-8, you want to add the following to my.cnf

[client]
default-character-set=utf8

[mysqld]
default-character-set = utf8

Then, to verify:

mysql> show variables like "%character%";show variables like "%collation%";

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

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

answered Dec 31 '11 at 20:33

Derek

1,466
15
24

5

Nice answer. Maybe a brief explanation about which parts are overkill would help people decide exactly what they want in their config. – Mike Samuel Dec 31 '11 at 21:22
@Derek, What does `[mysql] default-character-set=utf8` do then? – Pacerier Oct 18 '14 at 21:34
2

The [client] option group already sets options for all MySQL clients that read the my.cnf file. The [mysql] group sets options for just the "mysql" client binary, specifically. If you want to set a mysql option that does not apply to other clients, using this group is appropriate, but setting the same option value in both locations is redundant. http://dev.mysql.com/doc/refman/5.6/en/option-files.html – Derek Jan 13 '15 at 14:39
1

@Mike Samuel, setting the default character set to utf8 already implies the default collation mode of utf8_general_ci. On the other hand, "init-connect='SET NAMES utf8'" is a somewhat interesting idea, as it attempts to force all clients connecting to the server to use utf8, but this is applied inconsistently depending on the connection privileges of the connecting user and I suspect that some connecting third party clients might be confused when the server overrides the charset they requested. http://dev.mysql.com/doc/refman/5.6/en/charset-applications.html – Derek Jan 13 '15 at 14:53
1

For `[mysqld]` use `character-set-server` instead of `default-character-set` – Rick James Aug 02 '15 at 22:07
If you get: `unknown variable 'default-character-set=utf8'` check this: https://stackoverflow.com/a/12409934/2263395 – Tom Raganowicz Nov 12 '21 at 21:52

score 23 · Answer 8 · edited Aug 01 '14 at 00:13

23

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).

edited Aug 01 '14 at 00:13

Peter Mortensen

30,738
21
105
131

answered Feb 22 '12 at 11:34

amiroff

229
2
4

16

That’s [MySQL bug #52047](http://bugs.mysql.com/bug.php?id=52047). Under `[mysqld]`, you should use `character-set-server` instead of `default-character-set`. (Extremely confusing, I agree!) – Mathias Bynens Jun 25 '12 at 13:26
Yes. Deprecated in 5.0; removed in 5.5. – Rick James Aug 02 '15 at 22:06

score 11 · Answer 9 · answered Mar 04 '13 at 10:13

All settings listed here are correct, but here are the most optimal and sufficient solution:

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
character-set-server = utf8
collation-server = utf8_unicode_ci

[client]
default-character-set = utf8

Add these to /etc/mysql/my.cnf.

Please note, I choose utf8_unicode_ci type of collation due to the performance issue.

The result is:

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

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

And this is when you connect as non-SUPER user!

For example, the difference between connection as SUPER and non-SUPER user (of course in case of utf8_unicode_ci collation):

user with SUPER priv.:

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci | <---
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

user with non-SUPER priv.:

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

I wrote a comprehensive article (rus) explaining in details why you should use one or the other option. All types of Character Sets and Collations are considered: for server, for database, for connection, for table and even for column.

I hope this and the article will help to clarify unclear moments.

I wish I could read your linked article, but can't read Russian and Google translate is not that helpful for technical texts. From what I can tell though it would be very interesting. Would you please consider posting an English version? — Martijn Heemels, Mar 14 '13 at 14:54
Could you explain which setting fails to set the SUPER user connection collation? (and is there a solution) — KCD, Aug 18 '13 at 23:15
@gahcep, You stated you use `utf8_unicode_ci` due to performance issues, then, why not use `utf8_bin` instead? — Pacerier, Oct 18 '14 at 21:37
Hi, Paceriar. Good point. Now I'm not sure if my choice was correct about using `utf8_unicode_ci` at all. Never spent time testing the performance. — Sergei Danielian, Oct 20 '14 at 03:25

score 9 · Answer 10 · answered Apr 22 '12 at 12:37

Under Xubuntu 12.04 I simply added

[mysqld]
character_set_server = utf8

to /etc/mysql/my.cnf

And the result is

mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| 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)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Also take a look at http://dev.mysql.com/doc/refman/5.6/en/charset-server.html

You also need [client]default-character-set=utf8 otherwise when you use mysql at the command line it won't use utf8 potentially destroying any backups you pipe to file. — malhal, Nov 29 '12 at 04:03

score 6 · Answer 11 · edited Feb 07 '16 at 02:27

6

On Fedora 21

$ vi /etc/my.cnf

Add follow:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

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

Save and exit.

Final remember restart service mysqld with service mysqld restart.

edited Feb 07 '16 at 02:27

Tieme

62,602
20
102
156

answered May 05 '15 at 05:11

Nguyễn Văn Tiến

76
2
4

score 4 · Answer 12 · edited Aug 01 '14 at 00:18

4

MySQL versions and Linux distributions may matter when making configurations.

However, the changes under [mysqld] section is encouraged.

I want to give a short explanation of tomazzlender's answer:

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

[mysqld]

This will change collation_connection to utf8_unicode_ci

init_connect='SET collation_connection = utf8_unicode_ci'

Using SET NAMES:

init_connect='SET NAMES utf8'

The SET NAMES will influence three characters, that is:

character_set_client
character_set_results
character_set_connection

This will set character_set_database & character_set_server

character-set-server=utf8

This will only affect collation_database & collation_server

collation-server=utf8_unicode_ci

Sorry, I'm not so sure what is this for. I don't use it however:

skip-character-set-client-handshake

edited Aug 01 '14 at 00:18

Peter Mortensen

30,738
21
105
131

answered Nov 06 '13 at 02:57

Lihang Li

361
2
5

The [documentation](http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_character_set_database) notes that `character_set_server` should not be set manually. – Brian Nov 27 '13 at 00:45
So what is the state-of-art approach? We can explicitly pass the `character set` when establishing database connection, however looks a little annoying. – Lihang Li Dec 10 '13 at 14:35
Actually I made a mistake. The documentations says character_set_database should not be set dynamically. Doesn't mention character_set_server. However, I'm not sure you need to worry about character_set_server, since I think it only affects the default value for a newly created database? – Brian Dec 10 '13 at 16:01
As I mentioned above and I did the experiment again just now, `character_set_server` in `[mysqld]` section will affect `character_set_database` and `character_set_server`. Now I think a good practice is to explicitly point out the `character set` that you want to use when creating a database, a table and database connection. Honestly speaking, the documentation of `mysql` is not so easily understood. Do you know if there is a generic way to get all the `character set` and `collation` settings done in mysql? – Lihang Li Dec 16 '13 at 13:08

score 4 · Answer 13 · answered Sep 19 '11 at 02:12

4

The directive has changed to character-set-system=utf8

http://dev.mysql.com/doc/refman/5.6/en/charset-configuration.html

answered Sep 19 '11 at 02:12

Loren

13,903
8
48
79

This leads to an error on MySQL 8.0: [ERROR] [MY-000067] [Server] unknown variable 'character-set-system=utf8mb4' – ihoru Jul 06 '22 at 03:55

score 1 · Answer 14 · answered Oct 12 '13 at 19:37

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)

score 1 · Answer 15 · answered May 15 '14 at 20:30

If you're having trouble confirming the client's character-set support using MySQL Workbench, then keep the following note in mind:

Important All connections opened by MySQL Workbench automatically set the client character set to utf8. Manually changing the client character set, such as using SET NAMES ..., may cause MySQL Workbench to not correctly display the characters. For additional information about client character sets, see Connection Character Sets and Collations.

Thus I was unable to override MySQL Workbench's character sets with my.cnf changes. e.g. 'set names utf8mb4'

score 1 · Answer 16 · edited May 26 '15 at 14:02

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

vi /etc/my.cnf
add the contents blow and :wq [client] character-sets-dir=/usr/local/mysql/share/mysql/charsets
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.

score 1 · Answer 17 · answered Sep 26 '21 at 03:29

For utf8mb4_general_ci

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4
    
[mysqld]
collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

As per symfony framework Documentation at https://symfony.com/doc/2.6/book/doctrine.html#configuring-the-database

We recommend against MySQL’s utf8 character set, since it does not support 4-byte unicode characters, and strings containing them will be truncated. This is fixed by the newer utf8mb4 character set.

score 0 · Answer 18 · answered Aug 06 '17 at 09:16

0

You can do it the way it does, and if it doesn't work, you need to restart mysql.

answered Aug 06 '17 at 09:16

郭浩伟

121
1
3

score -1 · Answer 19 · edited Aug 01 '14 at 00:17

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

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

19 Answers19

Old Settings

Config

New Settings

Client

mysqld

Linked

Related