使用PyMySQL，我无法连接到RDS [英] Using PyMySQL, I can't connect to RDS

问题描述

使用PyMySQL，我得到pymysql.err.OperationalError: (1045, u"Access denied for user 'my_user'@'<MY_IP_ADDRESS>' (using password: YES)")，但是，我可以使用此计算机上的相同凭据从命令行或MySQL Workbench登录.而且，我可以使用PyMySQL连接到localhost.这是我的示例测试代码:

Using PyMySQL, I get pymysql.err.OperationalError: (1045, u"Access denied for user 'my_user'@'<MY_IP_ADDRESS>' (using password: YES)"), however, I can login from the command line or MySQL Workbench using the same credentials on this machine. And, I can connect to localhost using PyMySQL. Here's my sample test code:

import pymysql
prod_conn = pymysql.connect(
  host='correct-host-name.us-west-2.rds.amazonaws.com',
  user='my_user',
  password='correct_password',
  port=3306,
  database='my_db')

但是，在同一台计算机上的命令行中使用mysql -hcorrect-host-name.us-west-2.rds.amazonaws.com -umy_user -pcorrect_password -P3306 -Dmy_db，一切正常.

However, using mysql -hcorrect-host-name.us-west-2.rds.amazonaws.com -umy_user -pcorrect_password -P3306 -Dmy_db from the command line on the same machine, I get in just fine.

在谷歌搜索之后，我尝试检查用户的授权，并且相信该用户的设置正确. SHOW GRANTS FOR CURRENT_USER()返回

After Googling, I tried checking the grants for the user and I believe that the user is setup correctly. SHOW GRANTS FOR CURRENT_USER() returns

Grants for my_user@%
'GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO \'my_user\'@\'%\' WITH GRANT OPTION'

我尝试检查SELECT USER(), CURRENT_USER();并返回

USER()                      CURRENT_USER()
'my_user@<MY_IP_ADDRESS>'   'my_user@%'

因此，似乎用户设置正确，我怀疑这是因为我可以通过其他选项进行连接.我还尝试了 https://forums.aws.amazon.com/的两个建议thread.jspa?messageID = 707103 (具体来说，使用带有log_bin_trust_function_creators == 1的其他参数组并且使用不带标点的密码)无济于事.

So, it seems that the user is setup properly, which I suspected since I can connect through other options. I also tried both suggestions from https://forums.aws.amazon.com/thread.jspa?messageID=707103 (specifically, using a different parameter group with log_bin_trust_function_creators == 1 and using a password without punctuation) to no avail.

我想在lambda函数中使用它，所以我真的想使用PyMySQL将其保留为仅python的解决方案，而不是尝试使用MySQLDB或其他库.

I want to use this in a lambda function, so I really want to use PyMySQL to keep this a python only solution rather than trying MySQLDB or another library.

查看SELECT user,host FROM mysql.user;

user        host
'my_user'   '%'
'mysql.sys' 'localhost'
'rdsadmin'  'localhost'

赞赏这个社区可能对如何进行其他任何想法...

Appreciate any other thoughts this community might have on how to proceed...

推荐答案

我通过创建一个新用户来解决此问题.我只需要该用户的读取权限，因此，与使用具有如此高权限的默认用户相比，这是一种更好的方法.我仍然不确定为什么原来的那个不起作用，但是下面是最终起作用的地方:

I solved this by creating a new user. I only needed read privileges for this user, so it's a better approach than using the default user with such high permissions. I'm still not sure why the original one didn't work, but here's what ended up working:

# Grants for my_new_user@%
'GRANT SELECT ON *.* TO \'my_new_user\'@\'%\''

这篇关于使用PyMySQL，我无法连接到RDS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！