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.

One thought on “MySQL / MariaDB: Set Character Set and Collation to UTF8

  1. kaoutar

    bonjour tout le monde j’ai besoin de vous pour m’aider à propos d’un ocs inventory j’ai installer tous ces prérequis et quand je veux redémarrer apache il me donne ce message (Job for apache2.service failed because the control process exited with error code. See “systemctl status apache2.service” and “journalctl -xe” for details.)
    et quand je veux accéder à localhost pour vérifier la connectivité de la base de donnée il me donne aussi un message d’erreur 404 ou est le problème j’attens vos réponses et merci d’avance

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Notify me of followup comments via e-mail. You can also subscribe without commenting.