在文件模式下修改docker镜像mysql的时区和字符集

Question

mysql的默认配置

mysql> show variables like'%time_zone';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | latin1                         |
| character_set_connection | latin1                         |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | latin1                         |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+

我想将时区更改为East 8，字符集更改为utf-8，以解决中文乱码问题

Answer 1

永久修改Docker容器中mysql的时区

适用于mysql docker 5.7 ~最新版本

输入已成功运行的docker镜像

docker exec -it mysql bash

查看数据库时区配置:

show variables like'%time_zone';

查看时区结果:

mysql> show variables like "%time_zone";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+

查看数据库字符集配置:

SHOW VARIABLES LIKE 'character%';

查看数据库字符集结果:

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-8.0/charsets/ |
+--------------------------+--------------------------------+

升级apt包管理工具(原因:不升级就无法下载vim)

apt-get update

apt升级完成后下载vim

apt-get install vim

如果vim修改了docker.cnf或mysqld.cnf镜像文件，则会生效。您可以修改其中任何一个。

vim /etc/mysql/conf.d/docker.cnf

或:

vim /etc/mysql/conf.d/mysql.cnf

文件默认显示(以下均以docker.cnf为例):

[mysqld]
skip-host-cache
skip-name-resolve

添加时区配置:东巴区(中国)

[mysqld]
default-time_zone = '+8:00'

增加数据库服务器utf8字符集配置

[mysqld]
character_set_server=utf8

增加数据库客户端utf8字符集配置

[client]
default-character-set=utf8

添加完成后显示为:

[mysqld]
skip-host-cache
skip-name-resolve
default-time_zone = '+8:00'
character_set_server=utf8
[client]
default-character-set=utf8

保存配置后，重新启动数据库(适用于5.7版本)

service mysql restart

**建议退出容器，直接重启容器(因为重启mysql后还需要启动镜像，最好退出容器，直接重启镜像)

docker restart mysql

再次输入docker image mysql查看修改是否成功

时区视图:

show variables like "%time_zone";

时区查看结果:

mysql> show variables like "%time_zone";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | +08:00 |
+------------------+--------+

字符集视图:

SHOW VARIABLES LIKE 'character%';

字符集查看结果:

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb3                        |
| character_set_connection | utf8mb3                        |
| character_set_database   | utf8mb3                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb3                        |
| character_set_server     | utf8mb3                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+