MySQL / MariaDB: Set Character Set and Collation to UTF8

By | 2017/03/04

Defaults of various MySQL or MariaDB installs in Linux distros often do not use utf8 for default character sets. Here is how to get things straight and set the mysql server to use utf8!


1. To first check character set and collation, try something like this:

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)

mysql> 

latin1_swedish_ci – eek!


2. To set the mysql server to utf8, add the following to /etc/mysql/my.cnf (debian/ubuntu) or /etc/my.cnf (fedora/centos/rhel) under the [mysqld] section and restart mysql/mariadb:

character-set-server = utf8
collation-server = utf8_general_ci
skip-character-set-client-handshake

3. Check to make sure the change went through ok:

mysql> show variables like 'char%'; 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)

mysql> 

Much better. Note that this only applies to newly created databases / tables.