Python MySQLdb-错误1045:拒绝用户访问 [英] Python MySQLdb - Error 1045: Access denied for user

问题描述

我有一个类似的python代码

import MySQLdb
import sys

try:
    con = MySQLdb.connect(host = 'localhost',user = 'crawler',passwd = 'crawler', db = 'real_estate_analytics')

    #... rest of code ...

except MySQLdb.Error, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

问题是我遇到以下错误:

Error 1045: Access denied for user 'crawler'@'localhost' (using password: YES)

如果我在终端mysql -u crawler -pcrawler上使用mysql，则可以毫无问题地访问数据库.

mysql> show databases;
+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| AL                    |
| cloversoup            |
| codebar               |
| mysql                 |
| performance_schema    |
| real_estate_analytics |
| teste                 |
+-----------------------+
8 rows in set (0.00 sec)

我还删除了匿名用户以避免冲突.我的用户是

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | %         |
| crawler | localhost |
| root    | localhost |
+---------+-----------+
3 rows in set (0.00 sec)

我已将所有授予授予爬网程序(和刷新的特权)，所以这不应该成为问题:

GRANT ALL PRIVILEGES ON *.* TO crawler@localhost IDENTIFIED BY 'crawler' WITH GRANT OPTION;

FLUSH PRIVILEGES;

事实上:

mysql> show grants;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for crawler@localhost                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'crawler'@'localhost' IDENTIFIED BY PASSWORD '*A594DECC46D378FDA13D7843740CBF4985E6969B' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我还尝试通过使用root用户进行连接

con = MySQLdb.connect(host = 'localhost',user = 'root',passwd = 'my_root_password', db = 'real_estate_analytics')

但它也不起作用(相同的错误).

这件事使我发疯.有没有人知道可能是什么问题?

OBS:我知道在SO上也有类似的问题，但是我已经阅读了全部，但并不能解决我的问题.这就是为什么我在这里发布

解决方案

'localhost'是并且具有 MySQL总是很特别.在您的情况下，您授予crawler @ localhost某些特权，这意味着通过UNIX套接字连接的用户搜寻器".而且，我很确定MySQL服务器已配置为 --skip -网络.

这可以通过明确来解决.使用数据库驱动程序的 unix_socket连接参数，它将强制执行使用UNIX套接字. (无耻地链接到MySQL Connector/Python文档，因为我是该驱动程序的维护者.)

I have a python code that goes like

import MySQLdb
import sys

try:
    con = MySQLdb.connect(host = 'localhost',user = 'crawler',passwd = 'crawler', db = 'real_estate_analytics')

    #... rest of code ...

except MySQLdb.Error, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

The problem is that I'm getting the following error:

Error 1045: Access denied for user 'crawler'@'localhost' (using password: YES)

If I mysql on the terminal mysql -u crawler -pcrawler I can access the databases with no problem.

mysql> show databases;
+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| AL                    |
| cloversoup            |
| codebar               |
| mysql                 |
| performance_schema    |
| real_estate_analytics |
| teste                 |
+-----------------------+
8 rows in set (0.00 sec)

I have also deleted the anonymous user to avoid collisions. My users are

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | %         |
| crawler | localhost |
| root    | localhost |
+---------+-----------+
3 rows in set (0.00 sec)

I have given all grants to crawler (and flushed privileges), so that should not be the problem:

GRANT ALL PRIVILEGES ON *.* TO crawler@localhost IDENTIFIED BY 'crawler' WITH GRANT OPTION;

FLUSH PRIVILEGES;

as a matter of fact:

mysql> show grants;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for crawler@localhost                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'crawler'@'localhost' IDENTIFIED BY PASSWORD '*A594DECC46D378FDA13D7843740CBF4985E6969B' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I have also tried to connect using the root user, by doing

con = MySQLdb.connect(host = 'localhost',user = 'root',passwd = 'my_root_password', db = 'real_estate_analytics')

but it doesn't work either (same error).

This thing is driving me crazy. Does anyone have an insight on what the problem could be?

OBS: I know there are similar questions on SO, but I've read them all and it didn't solve my problem. That's why I'm posting it here

解决方案

'localhost' is and has always been special with MySQL. In your case, you grant crawler@localhost some privileges and this would mean 'the user crawler connecting through UNIX socket'. And, I'm pretty sure the MySQL server is configured with --skip-networking.

This can be fixed by being explicit. Using the unix_socket connection argument of your database driver, it would force the use of the UNIX socket. (Shamelessly linking to MySQL Connector/Python docs, as I'm the maintainer of that driver).

这篇关于Python MySQLdb-错误1045:拒绝用户访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！