连接docker中的mysql数据库 Access denied for user ‘bob‘@‘192.168.135.100‘ (using password: YES)

一、首先确认用户名密码是否有误，配置文件格式是否正确

二、MySQL数据库服务器，没有开放MySQL远程权限！

1.查看MySQL容器的ID docker ps

2.进入docker 容器  docker exec -it bf6c(容器id) /bin/bash

3.登录mysql mysql -u root -p

操作流程展示：

[root@localhost ~]# docker ps
CONTAINER ID   IMAGE                       COMMAND                  CREATED             STATUS             PORTS                                                  NAMES
428f28b39387   openjdk:8-jdk               "bash"                   40 minutes ago      Up 40 minutes                                                             jdk1.8
bf6c8c0f463f   mysql:8.0-oracle            "docker-entrypoint.s…"   About an hour ago   Up About an hour   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql
2494bf61b7db   nacos/nacos-server:v2.0.3   "bin/docker-startup.…"   About an hour ago   Up About an hour   0.0.0.0:8848->8848/tcp, :::8848->8848/tcp              nacos
[root@localhost ~]# docker exec -it bf6c /bin/bash
bash-4.4# mysql -u root -p
Enter password: 

4.切换到mysql数据库 use mysql

5. 查看user表中的用户和host信息  select user, host from user;

mysql> use mysql
Database changed
mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.08 sec)

 6.我这里根据报错提示创建了一个名为bob的可以通过192.168.135.100登陆的账户 并设置密码

报错信息：Access denied for user 'bob'@'192.168.135.100' (using password: YES) 

create user 'bob'@'192.168.135.100' identified with mysql_native_password by 'root';

create user 'bob'@'192.168.135.100' identified with mysql_native_password by 'root';

7.查看创建的用户

 select user, host from user;

8.给这个用户添加可以操作数据库的权限，再刷新重启：

grant all privileges on *.* to 'bob'@'192.168.135.100' with grant option;

刷新重启：flush privileges;

# 这里为刚才创建的'bob'@'192.168.135.100' 用户授予所有数据库的所有表的所有操作访问权限
grant all privileges on *.* to 'bob'@'192.168.135.100' with grant option;

#刷新重启
flush privileges;

6-8操作流程如下：

mysql> create user 'bob'@'192.168.135.1' identified with mysql_native_password by 'root';
Query OK, 0 rows affected (0.04 sec)

mysql> alter user 'bob'@'192.168.135.100' identified by 'root';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select user, host from user;
+------------------+---------------+
| user             | host          |
+------------------+---------------+
| root             | %             |
| bob           | 192.168.135.100 |
| mysql.infoschema | localhost     |
| mysql.session    | localhost     |
| mysql.sys        | localhost     |
| root             | localhost     |
+------------------+---------------+
7 rows in set (0.01 sec)

mysql> grant all privileges on *.* to 'bob'@'192.168.135.100' with grant option;
Query OK, 0 rows affected (0.03 sec)