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, Oct 19 '12 at 11:04
Did you test to connect using the `mysql` command line client? — , Oct 19 '12 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, Oct 19 '12 at 11:17

score 13 · Answer 1 · edited Sep 04 '16 at 22:14

13

 GRANT ALL
 ON  *.*
 TO user@192.168.39.17  -- 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 04 '16 at 22:14

Jerad

594
7
15

answered Oct 19 '12 at 11:20

solaimuruganv

27,177
1
18
23

I don't get it. Could you please explain it further? – user706838 Oct 19 '12 at 18:53
What is this excatly? – GLHF Jul 07 '16 at 17:08
is there a way to run this command through python. I tried the cursor object, but it also giving the access denied error. – Ankur Bhatia Oct 31 '16 at 16:39

score 6 · Answer 2 · answered Oct 19 '12 at 11:41

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

score 1 · Answer 3 · answered Oct 19 '12 at 15:50

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 '12 at 15:50

mahdieh Saeed

29
4

The first step was successful, but I lost it from step 2 onwards... could you please explain it in more detail??? – user706838 Oct 19 '12 at 16:39
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. – mahdieh Saeed Oct 28 '12 at 07:39

score 0 · Answer 4 · answered Jun 25 '18 at 11:13

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

Connect on remote MySQL database through Python

4 Answers4

Linked