pymysql cannot connect to mysql

Question

I am trying to use pymsql to connect to MySQL db, the host is '115.28.236.225', and using the default port: 3306. The code is as bellow (db_connect.py):

import pymysql

def connDB():
    conn=pymysql.connect(host='115.28.236.225',user='root',passwd='xxx',db='yyy',charset='utf8', port=3306)
    cur=conn.cursor();
    return (conn,cur);

conn,cur=connDB()

I use python db_connect.py to run, however, I got the error message pymysql.err.OperationalError: (1045, u"Access denied for user 'root'@'58.196.159.221' (using password: YES)") , I don't know where the host '58.196.159.221' comes from, which does not correspond to the one in the code.

(I have tried to use MySql Workbench to connect to MySQL, and that worked, so, I am sure it must be something wrong with the python code).

How can I solve this? Thanks in advance!

Answer 1

pymysql is awesome because it is a implemented purely in python - no external dependencies.

I'm willing to bet that you don't have the proper permissions set for the root account from external sources. The reason why you are seeing pymysql.err.OperationalError: (1045,u"Access denied for user 'root'@'58.196.159.221' is because you probably only have access to mysql from 'root'@'localhost', 58.196.159.221 is the IP address of the system your python program is running from - don't believe me? run ifconfig and check your IP address.

fix: Connect to the mysql console and run the following:

GRANT ALL ON root.* TO 'root'@'58.196.159.221' IDENTIFIED BY 'ENTER_PASSWORD_HERE' ;
FLUSH PRIVILEGES;

This will allow for remote access to mysql.

If you want to only access mysql from the local host you'd run this:

GRANT ALL ON root.* TO 'root'@'localhost' IDENTIFIED BY 'ENTER_PASSWORD_HERE' ;
FLUSH PRIVILEGES;

If you want to grant access from any IP address:

GRANT ALL ON root.* TO 'root'@'%' IDENTIFIED BY 'ENTER_PASSWORD_HERE' ;
FLUSH PRIVILEGES;

FLUSH PRIVILEGES reloads the privileges from the mysql database, which is necessary after you make a change to user permissions.

Answer 2

58.196.159.221 is most likely the address of the client that is running the script. It does not have permission to access your remote database; you will have to configure that in MySQL itself.