Docker MySQL修改默认编码格式

我的MySQL是5.7版本，装在docker里面，MySQL的默认编码大部分是latin1，写入中文数据就会报错，需要修改默认的编码格式。

首先查询MySQL的container id：docker container ls -a，

进入docker：docker container exec -it 131942a66b1c bash

[root@pa_cicd ~]# docker container ls -a

CONTAINER ID        IMAGE                   COMMAND                  CREATED             STATUS              PORTS                               NAMES
03d88945bd42        redis:6.0               "docker-entrypoint.s…"   3 months ago        Created                                                 redis
b70568b3d49c        phpmyadmin/phpmyadmin   "/docker-entrypoint.…"   9 months ago        Up 3 months         0.0.0.0:3307->80/tcp                phpadmin
131942a66b1c        mysql:5.7               "docker-entrypoint.s…"   9 months ago        Up 3 months         0.0.0.0:3306->3306/tcp, 33060/tcp   ci-mysql
7c0875dee821        mongo                   "docker-entrypoint.s…"   9 months ago        Up 3 months         0.0.0.0:27017->27017/tcp            ci-mongodb

[root@pa_cicd ~]# docker container exec -it 131942a66b1c bash

root@131942a66b1c:/#

然后输入命令进入MySQL：mysql -u root -p

root@131942a66b1c:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41598
Server version: 5.7.29 MySQL Community Server (GPL)

查询编码格式：mysql> show variables like '%char%';

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

接下来改成utf-8的编码，可以识别中文，我们需要找到MySQL的配置文件，一般是/etc/mysql/conf.d/mysql.cnf

如果docker内置的没有vi或者vim文本编辑工具，可以退出docker，通过find命令查找这个配置文件，然后修改。我的是centos7的系统，docker的存储是在devicemapper，不同的发行版本存在不同的存储驱动，比如overlay2，aufs等。

[root@pa_cicd ~]# find / -name mysql.cnf
find: ‘/run/user/42/gvfs’: Permission denied
/var/lib/docker/devicemapper/mnt/4f7c290296fbea64c70ff1db72b8c834e15c54ed277d7b4e24fff63aa2f1c7e1/rootfs/etc/mysql/conf.d/mysql.cnf
/var/lib/docker/devicemapper/mnt/4f7c290296fbea64c70ff1db72b8c834e15c54ed277d7b4e24fff63aa2f1c7e1/rootfs/etc/mysql/mysql.cnf

[root@pa_cicd ~]# vim /var/lib/docker/devicemapper/mnt/4f7c290296fbea64c70ff1db72b8c834e15c54ed277d7b4e24fff63aa2f1c7e1/rootfs/etc/mysql/conf.d/mysql.cnf

[mysql]
~
~
~

我们看到，现在配置文件是空的，我们添加一下内容：

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

[client]
default-character-set=utf8

重启MySQL容器，让配置文件生效：docker container restart ci-mysql

[root@pa_cicd ~]# docker container restart ci-mysql

然后进入MySQL查看编码格式是否修改OK

mysql> 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/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)