python: use 'MySQLdb' module to connect to remote mysql server

Question

I have two virtual machines. One is used as Agent, One is used as Master. A Mysql Server is installed on Master(Mysql Client is forbidden to be installed on Master which means I can't use mysql command line on Master). The Mysql server has a default user 'root'. Now I want to write a python script which use 'MySQLdb' module on Agent. The test code is easy. just see as bellow:

#!/usr/bin/python

import MySQLdb

def main():
    try:
        conn=MySQLdb.connect(host=master ip,user='root',passwd='xxx',db='xxx',port=3306)
        cur=conn.cursor()
        count = cur.execute('select * from table')
        print count
        cur.close()
        conn.close()
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])

if __name__ == "__main__":
    main()

however, when I execute it on Agent, there is an error:
Mysql Error 1045: Access denied for user 'root@Agent ip'(using password: YES)

So I don't know why the user is 'root@Agent ip', not the default user of Mysql server on Master. Anyone knows how to solve this problem?

You have specified the user name by user='root' and mysql use username@host as the user, so the user is root@Agent_ip. It's not an issue of MySQLdb, you would have the same issue if you log in with a mysql client. — WKPlus
– WKPlus, Commented May 19, 2015 at 10:14

Aswin Murugesh · Accepted Answer · 2015-05-19 11:05:43Z

2

There is a command named GRANT in MySQL. You have to grant permission for root@AgentIP (Where AgentIP is the IP of the system from which you have to access the db.)

The command to be run in the mysql client of the server:

GRANT ALL on mydb.* to 'root'@'YourIP' identified by 'YourPassword'

Only then the MySQL server running in the remote system will grant access to the database.

If not sure of the IP details, you can also specify 'root'@'%' ,

which will allow all requests from user named root from anywhere. This is not a recommended way, but there is such an option.

answered May 19, 2015 at 11:05

Aswin Murugesh

11.1k11 gold badges46 silver badges73 bronze badges

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

2 Comments

liminche Over a year ago

Since mysql client is forbidden to be installed on Master, is there any other method to grant remote user access?

Aswin Murugesh Over a year ago

@liminche : Or you can also grant access with the help of phpmyadmin. But there is no option for remote access without granting permissions

Collectives™ on Stack Overflow

python: use 'MySQLdb' module to connect to remote mysql server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related