How to change the default character set of mysql using docker-compose?

You can either build your own mysql image where you modify my.cnf, or modify the command that starts mysql's daemon with --character-set-server=utf8mb4 and --collation-server=utf8_unicode_ci.

web:
    image: yetongxue/docker_test:1.2
    links:
      - "db"
    ports:
      - "8100:8000"
    volumes:
      - "/Users/yetongxue/docker_v/docker_test/media:/root/media"
    restart: always

db:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: qwerasdf
      MYSQL_DATABASE: docker_db
    restart: always
    volumes:
      - "/Users/yetongxue/docker_v/docker_test/db:/var/lib/mysql"
    command: ['mysqld', '--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci']

I recommend using utf8mb4, as it can store up to "4 bytes per multibyte character" (https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html)

If you wanted to create your own docker image with the settings above, instead of specifying them in docker-compose, you can do so like:

FROM mysql:5.5.60

RUN ln -sf /usr/share/zoneinfo/Europe/London /etc/localtime

VOLUME ["/docker-entrypoint-initdb.d"]

CMD ["--character-set-server=utf8mb4", "--collation-server=utf8mb4_general_ci", "--skip-character-set-client-handshake"]

I read the GitHub issue as @Ziemowit Stolarczyk listed. All of them are not exactly correct. The GitHub answer is -e LANG=C.UTF-8. I use it in my docker-compose.yml, but mysql prints out like this:

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

It doesn't work for database and server. So I add a configuaration in docker-compose.yml as the following.

command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

The whole yaml file is:

version: "3"
services:
  mysql:
    container_name: mysql
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: root
      LANG: C.UTF-8
    volumes:
      - ./mysql:/var/lib/mysql
    ports:
      - "3306:3306"
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

Here is the final output.

mysql>  show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

REFERENCE

• https://github.com/docker-library/mysql/issues/131#issuecomment-248412170
• https://stackoverflow.com/a/66419629/13430829