更改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)