How to switch the mysql system time zone in a Docker container to Beijing time zone

Solution one

[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

Enter the docker virtual container.

[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:/#

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.

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#

Here, we append default-time_zone ='+8:00' to the file /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:~# 

After the addition is complete, exit the docker container and restart the container.

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 ~]#

Re-enter the container and check if the modification is successful.

[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> 

Here we find that the modification has been successful, and then exit the container.

mysql> exit
Bye
root@5b803ffcfb3d:/# exit
exit
[root@cvm00 ~]# 

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数据库的时区