Access denied for user ‘zhangsan_read_only‘@‘192.168.1.1‘(using password:YES)数据库连接报错，以下几种方法总能解决你的问题！

Access denied for user ‘zhangsan_read_only’@‘192.168.1.1’(using password:YES)数据库连接报错，以下几种方法总能解决你的问题！

方案一.测试两台机器可不可以正常访问

假如MySQL的地址是192.168.1.2，在张三的机器上输入一下命令看能否正常访问

[root@zhangsan:~]# yum install telnet -y
[root@zhangsan:~]# telnet 192.168.1.2 3306

如果不能正常嗅探，那就需要进入数据库机器看看是不是开了防火墙阻止了张三的IP访问本机电脑
看一下iptables规则，不懂的可以去学习一下iptables五链四表

[root@you:~]# iptables -nL

主要看input链和output链

可以输入以下两条命令

[root@you:~]# iptables  -I INPUT 1 -p tcp -s 192.168.1.1  --dport 3306 -j ACCEPT
[root@you:~]# iptables  -I OUTPUT  -p tcp -s 192.168.1.1 --sport 3306 -j ACCEPT

这两条命令的意思是让192.168.1.1这台机器能够与我（数据库机器）的3306端口正常通信，然后测试一下能不能连接，不能连接看下面的解决方法

方案二.检查数据库进程是否正常运行

[root@you:~]# netstat -ltnup|grep 3306

下面是正常运行的状态

如未运行，启动一下mysql

[root@you:~]# service mysql start
Starting MySQL (Percona Server) SUCCESS! 

然后测试一下能不能连接，不能连接看下面的解决方法

方案三.检查允许远程登录的主机IP是否正确

[root@you:~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 74094
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
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.

root@MySQL 16:22:  [(none)]> 

务必确认MySQL中用户绑定的的IP与你远程登录的电脑IP地址一致
查看电脑IP地址的命令：

• linux系统输入命令ip add查看
• windows进入CMD之后输入ipconfig查看

root@MySQL 16:27:  [(none)]> select  user,host from mysql.user where user like '%zhangsan%';
+--------------------+------+
| user               | host |
+--------------------+------+
| zhangsan_read_only | 192.168.1.1    |
+--------------------+------+
1 row in set (0.00 sec)

root@MySQL 16:27:  [(none)]> 

然后测试一下能不能连接，不能连接看下面的解决方法

方案四.检查所建的用户和密码是否正确

由于MySQL的密码加密处理后存储的，没法直接看，可以生成一个新的密码给账号

root@MySQL 16:29:  [(none)]> alter user zhangsan_read_only@'192.168.1.1' identified by '654321';
Query OK, 0 rows affected (0.00 sec)
root@MySQL 16:29:  [(none)]> flush privileges;

然后测试一下能不能连接，不能连接看下面的解决方法

方案五.多实例MySQL问题

如果还是无法连接的话大概率就是端口这方面出了问题，先要要清楚那个库到底在哪个端口的MySQL上，假如你的数据库服务器是多实例的，例如一台服务器上存在端口为3306、3307、3308的三个MySQL服务器

[root@you:~]# ls /tmp | grep mysql
mysql3307.sock
mysql3307.sock.lock
mysql3308.sock
mysql3308.sock.lock
mysql.sock
mysql.sock.lock
mysqlx.sock
mysqlx.sock.lock
[root@you:~]# 

你可以看到有三个socket文件，如果我们不指定通过哪台socket文件登录mysql，那么将会默认3306的登录mysql，你可以试试以下命令指定端口重新登录一下mysql

[root@you:~]# mysql -P 3307 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 75155
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
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.

root@MySQL 16:48:  [(none)]> 

那么现在你可以直接操作了吗？nonono，你得再确认一下端口

root@MySQL 16:48:  [(none)]> show variables like '%port%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| admin_port               | 33062 |
| large_files_support      | ON    |
| mysqlx_port              | 33060 |
| mysqlx_port_open_timeout | 0     |
| port                     | 3306  |
| report_host              |       |
| report_password          |       |
| report_port              | 3306  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
10 rows in set (0.01 sec)

root@MySQL 16:49:  [(none)]> 

怎么个事，咋还是3306。那是因为我们本地会默认通过3306登录，这种 -P的方法只适用于远程来控制数据库服务器，本地控制需要另外一种方法，直接强指定sock文件

[root@sz_yfw_pdcloud_slave01_13_19:~]# mysql -S/tmp/mysql3308.sock  -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 158977
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
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.

root@MySQL 16:52:  [(none)]> show variables like '%port%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| admin_port               | 33062 |
| large_files_support      | ON    |
| mysqlx_port              | 33060 |
| mysqlx_port_open_timeout | 0     |
| port                     | 3308  |
| report_host              |       |
| report_password          |       |
| report_port              | 3308  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
10 rows in set (0.00 sec)

root@MySQL 16:52:  [(none)]> 

进入指定的数据库之后授权相应的库给张三即可，然后再测试访问

一些相关的MySQL命令

修改MySQL只读模式

set global super_read_only = 0;

创建张三用户并允许他的主机（192.168.1.1）登录，同时设个密码

create user zhangsan_read_only@'192.168.1.1' identified by '654321';

修改张三的密码

alter user zhangsan_read_only@'192.168.1.1' identified by '654321';

查看含义port字段的变量

show variables like '%port%';

查看所有库

show databases;

查看用户名中以zhangsan开头的用户和主机

select user,host from mysql.user where user like "zhangsan%';

给张三授权aa库的所有权限

grant all  privileges on aa.* to 'zhangsan'@'192.168.1.1'

给张三aa库的只读权限

grant select on aa.* to 'zhangsan'@'192.168.1.1'

给张三解除aa库的只读权限

revoke select on aa.* from 'zhangsan_read_only'@'192.168.1.1';

将张三的访问主机IP从10开头的所以IP改成固定IP192.168.1.1

rename user 'zhangsan_read_only'@'10%' to 'zhangsan_read_ony'@'192.168.1.1';

查看张三有哪些权限

show grants for zhangsan_read_only@'%';