为什么我可以通过shell连接MySQL，而不能通过Python连接? [英] Why can I connect to MySQL through shell, but can't do it through Python?

问题描述

我正在通过服务器上的 SSH 连接到 MySQL 数据库.我设法通过 MySql 工作台和系统 shell 做到了这一点，这意味着密码、用户名和 ip 是正确且允许的.

I'm connecting to MySQL db through SSH on server. I managed to do it through MySql workbench and through system shell, which means passwords, username and ip are correct and allowed.

现在我正在尝试通过 Python 进行连接.我正在使用与工作台中设置相同的 sshtunnel 包(在 shell 中都是默认设置) - mysql 端口 3306、ssh 端口 22、mysql 主机名 127.0.0.1，它连接我.当我尝试通过连接器连接到数据库时，我得到:

Now I'm trying to connect through Python. I'm using sshtunnel package with the same settings as in workbench (in shell all was default) - mysql port 3306, ssh port 22, mysql hostname 127.0.0.1 and it connects me. When I'm trying to connect through connector to db I'm getting:

mysql.connector.errors.ProgrammingError: 1045 (28000): 拒绝访问对于用户 'myusername'@'localhost'(使用密码:YES).

mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'myusername'@'localhost' (using password: YES).

我还尝试使用以下命令通过 shell 与 python 直接连接:os.system("mysql -u myusername -p")我得到了:

I also tried to connect directly through shell with python using: os.system("mysql -u myusername -p") and I got:

错误 1045 (28000):用户myusername"@localhost"的访问被拒绝(使用密码:YES)

ERROR 1045 (28000): Access denied for user 'myusername'@'localhost' (using password: YES)

我在这里检查过:

不能通过 MySQL Workbench 或 HeidiSQL 访问 MySQL，但可以通过 shell 访问

从 Python 3 访问 MySQL:访问被拒绝用户

还有更多，但我发现没有任何帮助.我的 Python 版本是 3.6.8，MySql 版本是 5.7.31.python访问的shell和普通的Unix shell之间还有什么不同?或者通过工作台获得访问权限?

And many more, but I found nothing helpful. My Python version is 3.6.8, MySql version is 5.7.31. What other thing may differ between shell accessed by python and the normal Unix one? Or access got by workbench?

请求的代码.所有三个连接都给出了完全相同的输出.

Requested code. All of the three connections gave exactly the same output.

with sshtunnel.SSHTunnelForwarder(
        (ssh_host, ssh_port),
        ssh_username=ssh_username,
        ssh_password=ssh_password,
        remote_bind_address=('localhost', mysql_port)) as tunnel:
    

    # conn = mysql.connector.connect(**mysql_config)
    conn = MySQLdb.connect(host="localhost", 
    user="myusername", passwd="password", db="mydb")
    print(conn)
    # print(os.system("mysql -u myusername -p"))

推荐答案

我设法联系了:

tunnel = sshtunnel.SSHTunnelForwarder((ssh_host, 22), ssh_password=ssh_password, ssh_username=ssh_username,
     remote_bind_address=('localhost', 3306))
tunnel.start()

显然区别在于保持隧道打开而不是使用 'with' 语句，尽管我不明白实际发生了什么，我希望得到解释.

Apparently the difference was to keep the tunnel open instead of using 'with' statement, though I don't understand what actually happened and I'd appreciate explanation.

这篇关于为什么我可以通过shell连接MySQL，而不能通过Python连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！