Docker 如何部署mysql 5.7

环境
docker Version: 20.10.5
mysql 5.7

问题描述：
Docker 如何部署mysql 5.7

解决方案：
1.搜索docker hub上的mysql镜像

docker search mysql

[root@localhost ~]# docker search mysql

2.下载镜像mysql5.7

docker pull mysql:5.7

[root@localhost ~]# docker pull mysql:5.7

3.准备部署

本地系统创建部署目录，进入部署目录
创建conf，data，logs目录分别用来存放mysql配置，mysql数据，mysql日志

mkdir conf
mkdir data
mkdir logs

批量创建

[root@localhost srv]# mkdir -p conf data logs

更改3个文件夹权限

chmod 777 logs
chmod 777 data
chmod 777 conf

进入conf目录，写入mysql配置如下

[root@localhost srv]# cd conf
[root@localhost conf]# touch my.cnf
[root@localhost conf]# vim my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
init_connect='SET NAMES utf8

4.创建并启动镜像

退出conf目录，使用如下命令创建并启动mysql容器。

docker run -itd --name="mysql" -p3307:3306 -v$PWD/conf/:/etc/mysql/conf.d -v$PWD/data:/var/lib/mysql -v$PWD/logs:/var/log -eMYSQL_ROOT_PASSWORD="mys1223"  mysql:5.7

设置容器时间 

docker exec -it mysql57 /bin/bash -c 'cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime'

其中命令各部分的意义分别是：

docker run：创建并启动应用
-i：开启容器的标准输入STDIN
-t：容器分配伪终端，用于再终端登录容器
-d：后台运行容器
--name：容器名称
-p3307:3306：打开容器的3306端口并且映射到本机的3307端口
-v$PWD/conf/:/etc/mysql/conf.d，挂载($PWD)当前目录下的conf目录到容器的/etc/mysql/conf.d目录
v$PWD/data:/var/lib/mysql，挂载($PWD)当前目录下的data目录到容器的/var/lib/mysql目录
 -v$PWD/logs:/var/log，挂载($PWD)当前目录下的logs目录到容器的/var/log/目录
-eMYSQL_ROOT_PASSWORD="mysql"，增加环境变量MYSQL_ROOT_PASSWORD，mysql容器创建并启动时，设置mysql的登录密码
mysql:5.7，要创建并启动容器的mysql镜像

命令执行后

083041526b249f02c79bef77dcdf4ae42110fdfad3fdf1624e0334cfcf4606aa

查看容器信息

]# docker ps 
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
848a2581da80        mysql:5.7           "docker-entrypoint.s…"   7 minutes ago       Up 3 seconds        33060/tcp, 0.0.0.0:3307->3306/tcp   mysql

5.测试

进入容器终端测试mysql，并进行远程登录权限管理

]# docker exec -it 848a2581da80 /bin/bash

执行mysql -uroot -p命令后输入启动容器时设置的mysql密码，我的密码是mysql，连接mysql数据库成功。

mysql远程登录权限管理

:/# mysql -uroot -p           
Enter password: 之前设置的密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

mysql> grant all privileges on *.* to 'root'@'%' identified by 'mys1223' 命令
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;                                  命令
Query OK, 0 rows affected (0.00 sec)

mysql> exit 退出

退出mysql客户端，查看容器的ip地址

:/# cat /etc/hosts
127.0.0.1    localhost
::1    localhost ip6-localhost ip6-loopback
fe00::0    ip6-localnet
ff00::0    ip6-mcastprefix
ff02::1    ip6-allnodes
ff02::2    ip6-allrouters
172.17.0.2    848a2581da80

Ctrl+P+Q退出容器

通过 docker container inspect命令查看容器的详细信息

docker container inspect 848a2 | grep IP

]# docker container inspect 848a2581da80 | grep IP
            "LinkLocalIPv6Address": "",
            "LinkLocalIPv6PrefixLen": 0,
            "SecondaryIPAddresses": null,
            "SecondaryIPv6Addresses": null,
            "GlobalIPv6Address": "",
            "GlobalIPv6PrefixLen": 0,
            "IPAddress": "172.17.0.2",
            "IPPrefixLen": 16,
            "IPv6Gateway": "",
                    "IPAMConfig": null,
                    "IPAddress": "172.17.0.2",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,

查看data文件夹

]# ls data
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  mysql               private_key.pem  server-cert.pem  sys
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  ibtmp1       performance_schema  public_key.pem   server-key.pem

6.Navicat 测试连接

7.完成

1、重启容器

docker restart mysql

2、查看MySQL日志

docker logs mysql

第二种部署

创建MySQL容器
1、建立目录映射
mkdir /opt
mkdir /opt/mysql
mkdir /opt/mysql/conf/
mkdir /opt/mysql/logs/
mkdir /opt/mysql/data/

2、创建my.cnf配置文件
touch /opt/mysql/my.cnf

my.cnf添加以下内容：
 

[mysqld]
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000
 
[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8

3、创建容器，并后台启动

docker run --restart=always --privileged=true  \
-v /opt/mysql/data/:/var/lib/mysql \
-v /opt/mysql/logs/:/var/log/mysql \
-v /opt/mysql/conf/:/etc/mysql \
-v /opt/mysql/my.cnf:/etc/mysql/my.cnf  \
-p 3306:3306 --name my-mysql \
-e MYSQL_ROOT_PASSWORD=123456 -d mysql

 

三、连接MySQL
1、进入mysql容器
docker exec -it my_mysql /bin/bash

2、登陆mysql
mysql -u root -p

3、mysql 8 设置允许远程用户访问
update user set host = '%' where user = 'root' and host='localhost'; # 更新root - localhost 为 root - %

GRANT ALL ON *.* TO 'root'@'%'; # 设置允许远程用户访问

flush privileges;# 刷新权限

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';#更新用户加密方式，mysql8默认的加密方式为caching_sha2_password 与mysql5的加密方式mysql_native_password 不同