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

Question

When I save the string of Chinese, mysql rise the error "Exception Value:
(1366, "Incorrect string value: '\xE5\xB0\x8F\xE6\x98\x8E' for column 'name' at row 1")",I check the character of mysql,it show this:

mysql> show variables like 'character%'; +--------------------------+----------------------------+ | 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/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) 

And my docker-compose.yml is as fellow:

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" 

I know how to set the character of mysql with my.cnf,but how can I do this in the docker-compose.yml? Anybody know this? Thanks!

Answer 1

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)

Answer 2

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