-- 修复时间、时区 docker cp /usr/share/zoneinfo/Asia/Shanghai mycat01:/etc/localtime set global time_zone = '+8:00'; -- 启动容器时设置： -e TZ=Asia/Shanghai
背景
时区是使用了世界标准时间(UTC)。因为在中国使用，所以需要把时区改成东八区的
或者启动容器时设置
-e TZ=Asia/Shanghai
永久修改
进入容器
docker exec -it mysql5.7 bash
查看当前时区
date -R
修改时区
cp /usr/share/zoneinfo/PRC /etc/localtime
# 或者
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
# 退出
exit
# 重启容器生效
docker restart mysql5.7
临时修改-重启失效
查看时区select now();
修改时区为北京时间
mysql> set global time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)
mysql> set time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
修改docker环境中mysql时区为北京时间纪实
原创qwfys200 最后发布于2019-11-28 14:38:46 阅读数 120 收藏
展开
[root@cvm00 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d79fd92a656a redis:5.0.6-alpine "docker-entrypoint.s…" 12 days ago Up 12 days 0.0.0.0:6379->6379/tcp redis
5b803ffcfb3d mysql:5.7.28 "docker-entrypoint.s…" 12 days ago Up 12 days 0.0.0.0:3306->3306/tcp, 33060/tcp mysql
1
2
3
4
进入docker虚拟容器
[root@cvm00 ~]# docker exec -it mysql /bin/bash
root@5b803ffcfb3d:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2965
Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2019-11-28 06:02:42 |
+---------------------+
1 row in set (0.00 sec)
mysql> set global time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)
mysql> set time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2019-11-28 14:04:06 |
+---------------------+
1 row in set (0.00 sec)
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | UTC |
| time_zone | +08:00 |
+------------------+--------+
2 rows in set (0.00 sec)
mysql> exit
Bye
root@5b803ffcfb3d:/#
这时修改只是临时的，如果系统重启，可能还会恢复为格林威治时区时间。所以要永久性修改，需要修改mysql配置文件。追加default-time_zone = '+8:00'下内容到mysql配置文件中。
root@5b803ffcfb3d:/# ls -la /etc/mysql/
total 24
drwxr-xr-x 4 root root 4096 Oct 17 04:49 .
drwxr-xr-x 1 root root 4096 Nov 15 12:44 ..
drwxr-xr-x 2 root root 4096 Oct 17 04:49 conf.d
lrwxrwxrwx 1 root root 24 Oct 17 04:49 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r-- 1 root root 839 Jul 9 2016 my.cnf.fallback
-rw-r--r-- 1 root root 1215 Sep 27 07:17 mysql.cnf
drwxr-xr-x 2 root root 4096 Oct 17 04:49 mysql.conf.d
root@5b803ffcfb3d:/# cd /etc/mysql/
root@5b803ffcfb3d:/etc/mysql# vi my.cnf
bash: vi: command not found
root@5b803ffcfb3d:/etc/mysql# cat my.cnf
# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
root@5b803ffcfb3d:/etc/mysql# ls -la
total 24
drwxr-xr-x 4 root root 4096 Oct 17 04:49 .
drwxr-xr-x 1 root root 4096 Nov 15 12:44 ..
drwxr-xr-x 2 root root 4096 Oct 17 04:49 conf.d
lrwxrwxrwx 1 root root 24 Oct 17 04:49 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r-- 1 root root 839 Jul 9 2016 my.cnf.fallback
-rw-r--r-- 1 root root 1215 Sep 27 07:17 mysql.cnf
drwxr-xr-x 2 root root 4096 Oct 17 04:49 mysql.conf.d
root@5b803ffcfb3d:/etc/mysql# ls -la /etc/alternatives/my.cnf
lrwxrwxrwx 1 root root 20 Oct 17 04:49 /etc/alternatives/my.cnf -> /etc/mysql/mysql.cnf
root@5b803ffcfb3d:/etc/mysql# cd conf.d/
root@5b803ffcfb3d:/etc/mysql/conf.d# ls -la
total 20
drwxr-xr-x 2 root root 4096 Oct 17 04:49 .
drwxr-xr-x 4 root root 4096 Oct 17 04:49 ..
-rw-r--r-- 1 root root 43 Oct 17 04:49 docker.cnf
-rw-r--r-- 1 root root 8 Jul 9 2016 mysql.cnf
-rw-r--r-- 1 root root 55 Jul 9 2016 mysqldump.cnf
root@5b803ffcfb3d:/etc/mysql/conf.d# cat docker.cnf
[mysqld]
skip-host-cache
skip-name-resolve
root@5b803ffcfb3d:/etc/mysql/conf.d# cat mysql.cnf
[mysql]
root@5b803ffcfb3d:/etc/mysql/conf.d# cat mysqldump.cnf
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
root@5b803ffcfb3d:/etc/mysql/conf.d# cd ../
root@5b803ffcfb3d:/etc/mysql# ls -la mysql.conf.d/
total 12
drwxr-xr-x 2 root root 4096 Oct 17 04:49 .
drwxr-xr-x 4 root root 4096 Oct 17 04:49 ..
-rw-r--r-- 1 root root 1610 Oct 17 04:49 mysqld.cnf
root@5b803ffcfb3d:/etc/mysql# ls -la mysql.conf.d/mysqld.cnf
-rw-r--r-- 1 root root 1610 Oct 17 04:49 mysql.conf.d/mysqld.cnf
root@5b803ffcfb3d:/etc/mysql# cat mysql.conf.d/mysqld.cnf
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
root@5b803ffcfb3d:/etc/mysql/mysql.conf.d#
这里，我们追加default-time_zone = '+8:00'到文件/etc/mysql/mysql.conf.d/mysqld.cnf中。
root@5b803ffcfb3d:/etc/mysql/mysql.conf.d# echo "default-time_zone = '+8:00'"
default-time_zone = '+8:00'
root@5b803ffcfb3d:/etc/mysql/mysql.conf.d# echo "default-time_zone = '+8:00'" >> mysqld.cnf
root@5b803ffcfb3d:/etc/mysql/mysql.conf.d# cat mysqld.cnf
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-time_zone = '+8:00'
root@5b803ffcfb3d:/etc/mysql/mysql.conf.d# cd
root@5b803ffcfb3d:~#
追加完成，退出docker容器，重启该容器。
root@5b803ffcfb3d:~# exit
exit
[root@cvm00 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d79fd92a656a redis:5.0.6-alpine "docker-entrypoint.s…" 12 days ago Up 12 days 0.0.0.0:6379->6379/tcp redis
5b803ffcfb3d mysql:5.7.28 "docker-entrypoint.s…" 12 days ago Up 12 days 0.0.0.0:3306->3306/tcp, 33060/tcp mysql
[root@cvm00 ~]# docker restart mysql
mysql
[root@cvm00 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d79fd92a656a redis:5.0.6-alpine "docker-entrypoint.s…" 12 days ago Up 12 days 0.0.0.0:6379->6379/tcp redis
5b803ffcfb3d mysql:5.7.28 "docker-entrypoint.s…" 12 days ago Up 10 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql
[root@cvm00 ~]#
重新进入容器，查看有修改成功。
[root@cvm00 ~]# docker exec -it mysql /bin/bash
root@5b803ffcfb3d:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | UTC |
| time_zone | +08:00 |
+------------------+--------+
2 rows in set (0.00 sec)
mysql>
这里我们发现已经修改成功，接来退出容器。
mysql> exit
Bye
root@5b803ffcfb3d:/# exit
exit
[root@cvm00 ~]#
至此完成任务。
参考文献
MySQL查看和修改时区time_zone
修改mysql数据库的时区
About Me
|
........................................................................................................................ ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub、博客园、CSDN和个人微 信公众号（ DB宝）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文博客园地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群号： 230161599 、618766405 ● 微 信群：可加我微 信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友 （ 646634621 ），注明添加缘由 ● 于 2020-04-01 06:00 ~ 2020-04-30 24:00 在西安完成 ● 最新修改时间：2020-04-01 06:00 ~ 2020-04-30 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ........................................................................................................................ ● 小麦苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麦苗腾讯课堂主页： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号（ DB宝）及QQ群（DBA宝典）、添加小麦苗微 信， 学习最实用的数据库技术。
........................................................................................................................ |
 |
|