web123456

docker mysql container import, export (using mysqldump) sql file Chinese garbled problem - set the character set

synopsis (of previous events)

docker mysql5.7.26 container, using the way to mount the folder to import the sql file, using mysqldump to export the sql file, due to the mysql container character set, export the character set set set error, the Chinese import and then export will become Latin.

Setting the container character set

View container character set methods:

# Enter the command line of the container with name mysql, the container needs to be in the running state
docker exec -it mysql /bin/bash
#Connect to mysql
mysql -u username -p# Then you need to enter your password, enter
# View mysql character set
show variables like '%char%’; 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

If you don't set it, the database entry in the container's initial character set is Latin, as follows:
在这里插入图片描述
establishmysqlAdd two commands "-character-set-server=utf8mb4 -collation-server=utf8mb4_unicode_ci" to the container, then you can set the container character set:

# Command line creation of mysql containers
docker run -it -d -p 127.0.0.1:3306:3306/tcp --name mysql mysql:5.7.26 --character-set-server=utf8mb4 —collation-server=utf8mb4_unicode_ci

  • 1
  • 2
  • 3
#docker-compose create mysql container
version: '3'
services:
  mysql:
    image: mysql:5.7.26
    container_name: mysql
    ports:
      - 0.0.0.0:3306:3306/tcp
    network_mode: "bridge"
    command: [
      '--character-set-server=utf8mb4',
      '--collation-server=utf8mb4_unicode_ci'
    ]
      
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

At this point, the database entry in the container character set has changed to utf8.
在这里插入图片描述

Setting the character set when exporting with mysqldump

# Store all structure+data in mysql container to directory /var/lib/mysql/
mysqldump -uroot -proot123 --default-character-set=latin1 --set-charset=false --all-databases> /var/lib/mysql/ 

  • 1
  • 2
  • 3

postscript

After doing the above two settings, when importing and exporting sql files, the Chinese in the data is no longer garbled, but the field comments are still in Latin. Because my database is not a lot of fields, the comments do not affect what is messed up, so I have not deliberately solve this problem, if there is a solution welcome to leave a message hee hee hee.