docker 文件方式修改mysql配置(时区+字符集)

Default configuration of 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/ |
+--------------------------+--------------------------------+

I want to change the time zone to East 8 and the character set to utf-8 to solve the problem of Chinese garbled characters

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

对mysql docker image 5.7 ~ latest 都有效

进入已运行成功的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 或者mysql.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/ |
+--------------------------+--------------------------------+
关闭