How to create a MariaDB/MySQL image using utf8 instead of the default latin1 charset?

Question

I noticed when running a stock MariaDB Docker image in a container, the default character set is latin1:

CID=$(sudo docker run -d --rm -e MYSQL_ROOT_PASSWORD=p mariadb:10.3)
echo "show variables like 'char%';" | sudo docker exec -i "$CID" mysql -t -u root -pp
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

I suspect(correct me if I'm wrong) this may be related to the container's system locale :

echo "locale" | sudo docker exec -i "$CID" bash
[sudo] password for sylvain:        
LANG=
LANGUAGE=
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=

Is there a way to create a MariaDB image using utf8 everywhere without having to write and mount a custom my.cnf configuration file? Ideally, I'm looking for a Dockerfile-based solution.

Answer 1

In the official MariaDB image, my.cnf is configured to read additional configuration files from the /etc/mysql/conf.d/ and /etc/mysql/maria.conf.d/ directories:

root@bbd24e22be98:/# tail /etc/mysql/my.cnf 

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!include /etc/mysql/mariadb.cnf
!includedir /etc/mysql/conf.d/

root@bbd24e22be98:/# tail /etc/mysql/mariadb.cnf 
# * Character sets
# 
# Default is Latin1, if you need UTF-8 set all this (also in client section)
#
#character_set_server   = utf8 
#collation_server       = utf8_general_ci 
# Import all .cnf files from configuration directory
!includedir /etc/mysql/mariadb.conf.d/

Based on that, it is quite easy to setup utf8 everywhere by writing a configuration file to override the relevant settings, copying it at the right place from your Dockerfile:

$ cat db/utf8.cnf 
[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

$ cat db/Dockerfile 
# Base image
FROM mariadb:10.4

# utf8 everywhere
COPY utf8.cnf /etc/mysql/mariadb.conf.d/
ENV MYSQL_RANDOM_ROOT_PASSWORD=yes

I let you compare the output of show variables after those changes with the one provided in the question:

MariaDB [(none)]> 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/ |
+--------------------------+----------------------------+