Connect on remote MySQL database through Python

Question

I have tried the following script but unfortunately doesn't work. I am using a free MySQL database provider. Any ideas?

import MySQLdb

myDB = MySQLdb.connect(host="208.11.220.249",port=3306,user="XXXXX",passwd="XXXXX",db="XXXXX")
cHandler = myDB.cursor()
cHandler.execute("SHOW DATABASES")
results = cHandler.fetchall()
for items in results:
    print items[0]

Currently, I am getting the following error:

super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (1044, "Access denied for user 'XXXXX'@'%' to database 'XXXXX'")

what does not work? you get an error or just nothing is output — Alexander Stefanov
– Alexander Stefanov, Commented Oct 19, 2012 at 11:04
Did you test to connect using the mysql command line client? — user647772
– user647772, Commented Oct 19, 2012 at 11:10
Can you connect to the same database at the same host with these credentials in some other way? like from the command line or a GUI tool? — Alexander Stefanov
– Alexander Stefanov, Commented Oct 19, 2012 at 11:17

Jerad · Accepted Answer · 2016-09-04 22:14:47Z

13

 GRANT ALL
 ON  *.*
 TO [email protected]  -- client ip address
 IDENTIFIED BY 'pwd';

Edit

This is SQL that you'd run on the database in order to ensure that the user has access to everything. pwd is the user's password.

Basically, this answer assumes that the connection issue is a credentials issue.

edited Sep 4, 2016 at 22:14

Jerad

6038 silver badges15 bronze badges

answered Oct 19, 2012 at 11:20

solaimuruganv

30.3k1 gold badge22 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user706838 Over a year ago

I don't get it. Could you please explain it further?

GLHF Over a year ago

What is this excatly?

Ankur Bhatia Over a year ago

is there a way to run this command through python. I tried the cursor object, but it also giving the access denied error.

Ranjith Ramachandra · Accepted Answer · 2012-10-19 11:41:01Z

6

This is what I would do

See if the port is actually open on that machine.
On the machine you are connecting from, open console/cmd/terminal and see if you can connect using mysql -u XXXX -h 208.11.220.249 -p. If your mysql client can not connect, then there is no way you can connect using python

answered Oct 19, 2012 at 11:41

Ranjith Ramachandra

10.9k16 gold badges62 silver badges98 bronze badges

Comments

mahdieh Saeed · Accepted Answer · 2012-10-19 15:50:14Z

1

You don't have permission for connecting to database with this user. Follow these steps:

1.try connecting to mysql DB: mysql -h208.11.220.249 -uXXXXX -pXXXXX XXXXX

2.If you don't have permission for connecting to DB ,try creating user that has remote permission

GRANT ALL ON DB.* -- Database name TO user@ip -- client ip address IDENTIFIED BY 'pwd';

3.on the last check my.cnf . "bind-address" must be 0.0.0.0 If you want to connect all remote addresses.

answered Oct 19, 2012 at 15:50

mahdieh Saeed

294 bronze badges

2 Comments

user706838 Over a year ago

The first step was successful, but I lost it from step 2 onwards... could you please explain it in more detail???

mahdieh Saeed Over a year ago

IF you have connection to special DB ,You could access to this DB.Run the other command for example cHandler.execute("SHOW TABLES").IF this command works,you don't have permission for seeing all databases.

DuckPool · Accepted Answer · 2018-06-25 11:13:28Z

0

open your database in mysql and type the following :

mysql> GRANT ALL ON *.* TO root@'x' IDENTIFIED BY 'Your-password'

where x is the ip address of the user that wants to access your db.

use this link for more information: How to Allow MySQL Client to Connect to Remote MySQL server

answered Jun 25, 2018 at 11:13

DuckPool

14 bronze badges

Collectives™ on Stack Overflow

Connect on remote MySQL database through Python

4 Answers 4

3 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related