Docker 安装MySql镜像 navicat无法远程连接报错 ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password'】

Docker 安装MySql镜像 navicat无法远程连接报错

---

1.拉取镜像

docker pull mysql   默认下载最先latest版本

---

2.错误启动

docker run --name mysql01 -d mysql 自动停止运行

docker.io/tomcat    8.5.20              84ac1052e153        2 years ago         560 MB
[root@MyCnetos7 ~]# docker run --name mysql01 -d mysql
3d7af6415717e85d72031ca4dccc4e5f8d612a2afdf0434f93d5941c4e272024
[root@MyCnetos7 ~]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                       PORTS               NAMES
3d7af6415717        mysql               "docker-entrypoint..."   7 seconds ago       Exited (1) 6 seconds ago                         mysql01
[root@MyCnetos7 ~]# docker start mysql01
mysql01
[root@MyCnetos7 ~]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                       PORTS               NAMES
3d7af6415717        mysql               "docker-entrypoint..."   34 seconds ago      Exited (1) 1 second ago                          mysql01
[root@MyCnetos7 ~]# 

---

3.查看错误日志

docker logs 容器id/容器名

• 原因:
  没有指定登录密码(三个参数必须指定其中一个)
  You need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD

[root@MyCnetos7 ~]# docker logs mysql01
 You need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD

4.正确登录

删除之前的容器,重新创建:
    docker run --name mysql01 -p 3306:3306
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest

• -e:传入环境变量参数

[root@MyCnetos7 ~]# docker stop mysql01
mysql01
[root@MyCnetos7 ~]# docker rm mysql01
mysql01
[root@MyCnetos7 ~]# docker run --name mysql01 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest
af85c73cf15f1a6fcf62eace4d5b1e515701345edfbbe11b2aaffe9922a79f1a
[root@MyCnetos7 ~]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                        PORTS                               NAMES
af85c73cf15f        mysql:latest        "docker-entrypoint..."   5 seconds ago       Up 4 seconds                  0.0.0.0:3306->3306/tcp, 33060/tcp   mysql01
ce79af47cc30        tomcat:8.5.20       "catalina.sh run"        18 minutes ago      Exited (143) 18 minutes ago    

5.本地连接mysql

docker exec -it 容器名/容器id bash    进入容器内部进入容器内部
**exit **  退出容器

[root@MyCnetos7 ~]# docker exec -it mysql01 bash
root@af85c73cf15f:/# mysql -u root -p
Enter password: 

6.远程连接

1)开放3306端口:

firewall-cmd --zone=public --add-port=3306/tcp --permanent
systemctl restart firewalld    重启防火墙
firewall-cmd --zone=public --list-ports   查看端口开放列表

• 移除指定端口:
  firewall-cmd --zone=public --remove-port=3307/tcp --permanent
  systemctl restart firewalld   重启防火墙

---

2)赋予远程连接权限:

grant all privileges on *.* to ‘root’@’%’   给root 用户赋予远程连接权限
flush privileges   刷新权限

mysql> grant all privileges on *.* to 'root'@'%';
mysql> flush privileges;

---

3)报错:

---

4)原因:

客户端不支持新的加密方式
mysql 以前身份验证机制为: mysql_native_password
mysql 8.0 默认身份验证机制为:caching_sha2_password

---

5)解决方法:修改用户密码加密方式

alter user ‘root’@’%’ identified with mysql_native_password by ‘root的密码’    修改验证机制
flush privileges    刷新权限

mysql> alter user 'root'@'%' identified with mysql_native_password by '123456';
mysql> flush privileges;