Docker专题系列之二：安装mysql8.0

查找镜像

[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                                      

拉取镜像

docker pull 名称

[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

[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
mysql               5.7                 cd3ed0dfff7e        5 days ago          437MB
mysql               latest              c8ee894bd2bd        5 days ago          456MB

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

连接数据库

再启动mysql 最新版本的，由于我们启动5.7 占用了3306 端口，所以我们要更改映射端口号为3307

[root@centos-nacos /]# docker run -p 3307:3306 --name mysql8.0 -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/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 之后密码加密方式进行了更改

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fpkpbgiX-1638932168087)(D:\d-hs-logs\images\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;

重新登录

问题：

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

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