docker中的MySQL修改時區
-- 修復時間、時區 docker cp /etc/localtime mycat01:/etc/localtime docker cp /usr/share/zoneinfo/Asia/Shanghai mycat01:/etc/localtime -- 啟動容器時設定： -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寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。
........................................................................................................................ |
 |
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2687821/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 修改Docker容器的時區為中國時區Docker
- 【Mysql】修改mysql時區MySql
- 【MySQL】時區修改MySql
- 修改docker中Mysql的密碼DockerMySql密碼
- Docker容器內部修改時區Docker
- 基於Alpine和Ubuntu的Docker映象修改時區UbuntuDocker
- linux系統時區修改（Debian的主機和docker）LinuxDocker
- mac mysql修改預設時區為 utcMacMySql
- MySQL中的時區設定MySql
- docker修改容器時間Docker
- Oracle修改時區Oracle
- weblogic修改時區Web
- Esxi 修改時區
- ubuntu16 修改時區Ubuntu
- 在Linux中修改MySQL的密碼LinuxMySql密碼
- Docker映象時區設定Docker
- Linux 7 修改時區不生效Linux
- [20200313]centos 7修改時區.txtCentOS
- Linux設定和修改時間與時區Linux
- mysql中!=和is not的區別MySql
- mysql中“ ‘ “和 “ ` “的區別MySql
- mysql的時區錯誤問題MySql
- 在Docker中，Docker配置檔案在哪裡以及如何修改？Docker
- 修改主機時區對Oracle的影響分析Oracle
- Docker容器中部署Django的時區問題DockerDjango
- docker下mysql連線數修改後不生效問題的解決DockerMySql
- ubuntu 修改時區或時間 及網路同步時間Ubuntu
- 修改docker映象源的方法Docker
- MySQL 中的臨時表MySql
- ubuntu 22.04版本修改時區的操作方法Ubuntu
- Docker（33）- 如何修改 docker 容器的埠對映Docker
- Ubuntu、CentOS修改時區、設定24小時時間格式教程。UbuntuCentOS
- 【MySQL】 在Docker中快速部署PXCMySqlDocker
- MySQL修改表的列名MySql
- 如何透過Hibernate/JPA在MySQL中儲存UTC時區？MySql
- 測試修改作業系統時間&時區對oracle的影響作業系統Oracle
- docker 修改port 埠Docker
- Mysql 中 MyISAM 和 InnoDB 的區別MySql