1.查找镜像
[root@centos-nacos /]# docker search mysql
NAME DESCRIPTION STARS OFFICIAL AUTOMATED
mysql MySQL is a widely used, open-source relation… 8713 [OK]
mariadb MariaDB is a community-developed fork of MyS… 3048 [OK]
mysql/mysql-server Optimized MySQL Server Docker images. Create… 645 [OK]
centos/mysql-57-centos7 MySQL 5.7 SQL database server 63
2.拉取镜像
拉取指定版本镜像在名称后面加 ：版本号 例如：mysql：5.7 。若不加则拉取最新版本latest，以mysql 为例
最新版本
[root@centos-nacos /]# docker pull mysql # 默认拉取最新版本即8.0 版本的
Using default tag: latest
latest: Pulling from library/mysql
80369df48736: Pull complete
e8f52315cb10: Pull complete
cf2189b391fc: Pull complete
cc98f645c682: Pull complete
27a27ac83f74: Pull complete
fa1f04453414: Pull complete
d45bf7d22d33: Pull complete
3dbac26e409c: Pull complete
9017140fb8c1: Pull complete
b76dda2673ae: Pull complete
bea9eb46d12a: Pull complete
e1f050a38d0f: Pull complete
Digest: sha256:7345ce4ce6f0c1771d01fa333b8edb2c606ca59d385f69575f8e3e2ec6695eee
Status: Downloaded newer image for mysql:latest
docker.io/library/mysql:latest
5.7 版本
[root@centos-nacos /]# docker pull mysql:5.7 #拉取指定版本即5.7 版本的数据库
5.7: Pulling from library/mysql
80369df48736: Already exists
e8f52315cb10: Already exists
cf2189b391fc: Already exists
cc98f645c682: Already exists
27a27ac83f74: Already exists
fa1f04453414: Already exists
d45bf7d22d33: Already exists
c7d49ffebc56: Pull complete
511a8052b204: Pull complete
5d5df4c12444: Pull complete
d482603a2922: Pull complete
Digest: sha256:44b33224e3c406bf50b5a2ee4286ed0d7f2c5aec1f7fdb70291f7f7c570284dd
Status: Downloaded newer image for mysql:5.7
docker.io/library/mysql:5.7
查看拉取的镜像
[root@centos-nacos /]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
#5.7版本的镜像
mysql 5.7 cd3ed0dfff7e 5 days ago 437MB
#最新版本的镜像
mysql latest c8ee894bd2bd 5 days ago 456
----------------------------------------------------------------------------------------------------
REPOSITORY ：镜像名称
TAG :镜像版本，latest 指最新版本
IMAGE ID ：镜像ID
CREATED :创建时长
SIZE：镜像大小
运行镜像
[root@centos-nacos /]# docker run -p 3306:3306 --name mysql5.7 -v $PWD/usr/local/docker/mysql/8.0-01/conf:/etc/mysql/conf.d -v $PWD/usr/local/docker/mysql/8.0-01/logs:/logs -v $PWD/usr/local/docker/mysql/8.0-01/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
#运行成功出现以下结果
84ef62005aad2cc2dd0aea1ab9170495e488eaded0e3c21f4861586f5147337b
-----------------------------------------------------------------------------------------------------------
-p 3306:3306：将容器的 3306 端口映射到主机的 3306 端口。
-v $PWD/conf:/etc/mysql/conf.d：将主机当前目录下的 conf/my.cnf 挂载到容器的 /etc/mysql/my.cnf。
-v $PWD/logs:/logs：将主机当前目录下的 logs 目录挂载到容器的 /logs。
-v $PWD/data:/var/lib/mysql ：将主机当前目录下的data目录挂载到容器的 /var/lib/mysql
-e MYSQL_ROOT_PASSWORD=123456：初始化 root 用户的密码。
查看已运行镜像
[root@centos-nacos /]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
658c3bb9cd2b mysql:5.7 "docker-entrypoint.s…" 3 seconds ago Up 2 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql5.7
连接数据库
20191023105231.png
再启动mysql 最新版本的，由于我们启动5.7 占用了3306 端口，所以我们要更改映射端口号为3307,数据卷映射路径必须更改，可以自己创建数据卷文件夹，在根目录下创建文件夹
mkdir usr/local/docker/mysql8.0
再在mysql8.0下创建data,logs,conf
[root@centos-nacos /]# docker run -p 3307:3306 --name mysql8.0 -v $PWD/usr/local/docker/mysql8.0/conf:/etc/mysql/conf.d -v $PWD/usr/local/docker/mysql8.0/logs:/logs -v $PWD/usr/local/docker/mysql8.0/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest
2cff2731d3042fe3d13b8ee1466ff6ba9b71881e545b188007e40ea307e8a266
查看正在运行的镜像,分别运行了两个不同版本的数据库
[root@centos-nacos /]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
2cff2731d304 mysql:latest "docker-entrypoint.s…" 3 seconds ago Up 2 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp mysql8.0
658c3bb9cd2b mysql:5.7 "docker-entrypoint.s…" 12 minutes ago Up 26 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql5.7
连接数据库，报错，由于mysql8.0 之后密码加密方式进行了更改
20191023111326.png
则需进入容器中修改密码
[root@centos-nacos /]# docker exec -it 2cff2731d304 /bin/bash #进入容器
登录mysql
root@4d3fbee0d162:/# mysql -u root -p #登录
Enter password: #输入密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.18 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>
按照步骤输入以下内容,进行修改
mysql> grant all PRIVILEGES on *.* to root@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER user 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;#使修改生效
Query OK, 0 rows affected (0.02 sec)
mysql> exit; #退出容器
重新登录
20191023111731.png
问题：mysql 5.7 + 及以上 出现
mysql sql_mode=only_full_group_by
ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在 SELECT 中的列，没有在 GROUP BY 中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。
解决方案修改 etc/my.cnf ,及在我们映射的数据卷 conf 下进行修改
[root@centos-nacos conf]# vi my.cnf
[mysqld]
character-set-server=utf8
#加入以下内如即可 即sql_mode 中去除了 only_full_group_by
#5.7 版本加这句
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#8.0 版本加这句
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
注5.7 版本和8.0 版本的sql_mode不一样
可以试用工具查询
#全局查询，然后复制里面的值出来，删除掉only_full_group_by 即可
SELECT @@GLOBAL.sql_mode;
网友评论