更改docker里mysql的字符编码

一、问题复现

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)

由上图可见database和server的字符集使用了latin1编码方式，不支持中文，即存储中文时会出现乱码。

二、解决办法

1.查找mysqld.cnf文件(可跳过)

去mysql容器中寻找mysqld.cnf文件，一般是在 /etc/mysql/mysql.conf.d/ 下

# docker exec -it 容器id/容器名称 /bin/bash
docker exec -it mysql-test /bin/bash

进入文件目录

cd /etc/mysql/mysql.conf.d/

输入ls就能看到mysqld.cnf文件

exit 退出容器

2.复制mysqld.cnf文件到主机

将刚才的 mysqld.cnf 文件 cp 到主机任意目录

docker cp mysql-test:/etc/mysql/mysql.conf.d/mysqld.cnf  /home/

3.配置mysqld.cnf文件

对该配置文件进行编辑 vi /home/mysqld.cnf

使用i打开编辑模式，在文本的最后输入以下内容

注意：下面的内容一定不能输错，否则会造成配置文件失效

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8

4.覆盖原mysqld.cnf文件

编辑完成后，再将其复制回原目录，覆盖源文件

docker cp /home/mysqld.cnf mysql-test:/etc/mysql/mysql.conf.d/

5.重启mysql

docker restart mysql

6.确认编码是否成功更改

进入容器 docker exec -it mysql bash

登录mysql mysql -u root -p

执行命令mysql> show variables like'character_%';

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