Solution one
[[email protected] ~]# 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
Enter the docker virtual container.
[[email protected] ~]# docker exec -it mysql /bin/bash
[email protected]:/# 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
[email protected]:/#
At this time, the modification is only temporary. If the system restarts, it may be restored to Greenwich Mean Time. Therefore, to permanently modify, you need to modify the mysql configuration file. Append the contents of
default-time_zone ='+8:00'
to the mysql configuration file.
[email protected]:/# 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
[email protected]:/# cd /etc/mysql/
[email protected]:/etc/mysql# vi my.cnf
bash: vi: command not found
[email protected]:/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/
[email protected]:/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
[email protected]:/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
[email protected]:/etc/mysql# cd conf.d/
[email protected]:/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
[email protected]:/etc/mysql/conf.d# cat docker.cnf
[mysqld]
skip-host-cache
skip-name-resolve
[email protected]:/etc/mysql/conf.d# cat mysql.cnf
[mysql]
[email protected]:/etc/mysql/conf.d# cat mysqldump.cnf
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[email protected]:/etc/mysql/conf.d# cd ../
[email protected]:/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
[email protected]:/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
[email protected]:/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
[email protected]:/etc/mysql/mysql.conf.d#
Here, we append
default-time_zone ='+8:00'
to the file /etc/mysql/mysql.conf.d/mysqld.cnf.
[email protected]:/etc/mysql/mysql.conf.d# echo "default-time_zone = '+8:00'"
default-time_zone = '+8:00'
[email protected]:/etc/mysql/mysql.conf.d# echo "default-time_zone = '+8:00'" >> mysqld.cnf
[email protected]:/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'
[email protected]:/etc/mysql/mysql.conf.d# cd
[email protected]:~#
After the addition is complete, exit the docker container and restart the container.
[email protected]:~# exit
exit
[[email protected] ~]# 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
[[email protected] ~]# docker restart mysql
mysql
[[email protected] ~]# 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
[[email protected] ~]#
Re-enter the container and check if the modification is successful.
[[email protected] ~]# docker exec -it mysql /bin/bash
[email protected]:/# 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>
Here we find that the modification has been successful, and then exit the container.
mysql> exit
Bye
[email protected]:/# exit
exit
[[email protected] ~]#
So far everything is ready.
Solution Two
docker run --detach \
--restart always \
--name mysql \
--publish 3306:3306 \
--volume /data/docker/mysql/data:/var/lib/mysql \
--volume /data/docker/mysql/etc:/etc/mysql \
--volume /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime \
--env MYSQL_ROOT_PASSWORD=Gah6kuP7ohfio4 \
mysql:5.7.28
We know that the time zone of the mysql system reads the current time zone of the system. Normally, in Centos7, we use the command
timedatectl set-timezone Asia/Shanghai
to modify the system time zone, and its essence is to re-add the software Link, namely
ln -s /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
.
References
- MySQL檢視和修改時區time_zone
- 修改mysql資料庫的時區
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)