MySQL 5.7.28 docker image operation and maintenance，part II

• step 3 Copy the configuration file directory to the mount directory on the host machine

  Copy the configuration file directory /etc/mysql in the mysql docker container instance to the directory /data/cloud/docker/mysql/etc on the host machine. The directory /data/cloud/docker/mysql/etc is the configuration file mount directory we reserve for the mysql container instance. Operation and maintenance personnel modify the configuration file at any time.

[root@vm83 ~]# ll /data/cloud/
total 0
drwxr-xr-x. 4 root root 28 Jan  1 20:09 .
drwxr-xr-x. 3 root root 19 Jan  1 20:09 ..
drwxr-xr-x. 2 root root  6 Jan  1 20:09 app
drwxr-xr-x. 2 root root  6 Jan  1 20:09 web
[root@vm83 ~]# mkdir -p /data/cloud/docker/mysql
[root@vm83 ~]# cd /data/cloud/docker/mysql/
[root@vm83 mysql]# ll
total 0
drwxr-xr-x. 2 root root  6 Jan 14 14:34 .
drwxr-xr-x. 3 root root 19 Jan 14 14:34 ..
[root@vm83 mysql]# mkdir -p data
[root@vm83 mysql]# ll
total 0
drwxr-xr-x. 3 root root 18 Jan 14 14:35 .
drwxr-xr-x. 3 root root 19 Jan 14 14:34 ..
drwxr-xr-x. 2 root root  6 Jan 14 14:35 data
[root@vm83 mysql]# docker cp mysql:/etc/mysql ./etc
[root@vm83 mysql]# ll
total 0
drwxr-xr-x. 4 root root 29 Jan 14 14:36 .
drwxr-xr-x. 3 root root 19 Jan 14 14:34 ..
drwxr-xr-x. 2 root root  6 Jan 14 14:35 data
drwxr-xr-x. 4 root root 94 Jan 14 14:03 etc
[root@vm83 mysql]# ll etc/
total 8
drwxr-xr-x. 4 root root   94 Jan 14 14:03 .
drwxr-xr-x. 4 root root   29 Jan 14 14:36 ..
drwxr-xr-x. 2 root root   62 Dec 29 07:00 conf.d
lrwxrwxrwx. 1 root root    9 Jan 14 14:03 my.cnf -> mysql.cnf
-rw-r--r--. 1 root root  839 Jul 10  2016 my.cnf.fallback
-rw-r--r--. 1 root root 1215 Sep 27 15:17 mysql.cnf
drwxr-xr-x. 2 root root   24 Dec 29 07:00 mysql.conf.d
[root@vm83 mysql]# 

The host directory /data/cloud/docker/mysql/data is the data file mount directory that we reserve for the mysql container instance, and it is also for the convenience of operation and maintenance personnel to back up at any time.

• step 4 Run mysql docker instance

[root@vm83 ~]# docker run  --detach \
> --restart always \
> --publish 3306:3306 \
> --name mysql \
> --volume /data/cloud/docker/mysql/data:/var/lib/mysql \
> --volume /data/cloud/docker/mysql/etc:/etc/mysql \
> -e MYSQL_ROOT_PASSWORD=Gah6kuP7ohfio4 \
> mysql:5.7.28
b63d0c01f1c9b585895e18f96f076593845e9c61df9c40e081a980340e965df0
[root@vm83 ~]# docker ps -a 
CONTAINER ID        IMAGE                                                                   COMMAND                  CREATED             STATUS              PORTS                               NAMES
b63d0c01f1c9        mysql:5.7.28                                                            "docker-entrypoint.s…"   18 seconds ago      Up 16 seconds       0.0.0.0:3306->3306/tcp, 33060/tcp   mysql
a95dcc30eeaf        registry.cn-hangzhou.aliyuncs.com/qwfys/dockfile-sample:1.0.0.release   "java -Djava.securit…"   18 hours ago        Up 18 hours         0.0.0.0:17829->8080/tcp             dockerfile-sample
[root@vm83 ~]#

• step 5 Personalized configuration

  Although the system is up, we will find that there are still many places that need further configuration, such as time zone issues, and others. Next, we deal with these problems one by one.

• about zone

  Let’s look at a case first, this case reflects the actual problem well.

[root@vm83 ~]# date
Tue Jan 14 14:57:07 CST 2020
[root@vm83 ~]# docker exec -it mysql /bin/bash
root@b63d0c01f1c9:/# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
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()               |
+---------------------+
| 2020-01-14 06:57:55 |
+---------------------+
1 row in set (0.00 sec)

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

mysql> exit
Bye
root@b63d0c01f1c9:/# 

  We found that the time zone in the mysql docker container instance is still the international standard time zone, not the Beijing time zone where China is located.

  There are many solutions to deal with time zones. Here, we choose to modify the configuration file in the mysql docker instance to fix the problem. This file is /etc/mysql/mysql.conf.d/mysqld.cnf. Considering that we have mounted the configuration file directory before running the docker instance, so we only need to modify the corresponding file of the mounted directory on the host machine.

root@b63d0c01f1c9:/# exit                            
exit
[root@vm83 ~]# ll /data/cloud/docker/mysql/etc/

total 8
drwxr-xr-x. 4 root root   94 Jan 14 14:03 .
drwxr-xr-x. 4 root root   29 Jan 14 14:36 ..
drwxr-xr-x. 2 root root   62 Dec 29 07:00 conf.d
lrwxrwxrwx. 1 root root    9 Jan 14 14:03 my.cnf -> mysql.cnf
-rw-r--r--. 1 root root  839 Jul 10  2016 my.cnf.fallback
-rw-r--r--. 1 root root 1215 Sep 27 15:17 mysql.cnf
drwxr-xr-x. 2 root root   24 Dec 29 07:00 mysql.conf.d
[root@vm83 ~]# ll /data/cloud/docker/mysql/etc/mysql.conf.d/
total 4
drwxr-xr-x. 2 root root   24 Dec 29 07:00 .
drwxr-xr-x. 4 root root   94 Jan 14 14:03 ..
-rw-r--r--. 1 root root 1610 Dec 29 07:00 mysqld.cnf
[root@vm83 ~]# cat /data/cloud/docker/mysql/etc/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@vm83 ~]#

  Here, we just need to add the following line to the end of the file /data/cloud/docker/mysql/etc/mysql.conf.d/mysqld.cnf, and then restart the mysql container instance.

default-time_zone = '+8:00'

  Now restart the mysql docker container instance for the configuration to take effect.

• Other issues

  Other issues to be continued later

  Document written by the Qiantang River in Hangzhou.

References

• MySQL for Docker Quick reference
• Docker Official Images