CODEWITHSUNDEEP
python-3.xpymysql
Ahmad Raza
Ahmad Raza

Reputation: 1929

(1045, "Access denied for user 'root'@'ip-address' (using password: YES)") in python

I want to access MySql database hosted on domain my_domain_name using pymysql ( python library) . My code is:

connection = pymysql.connect('my_domain_name', user, passd, db)

But I receive this error

(1045, "Access denied for user 'root'@'ip-address' (using password: YES)")

In most answers, people suggest that:

either password is wrong or 'root' user do not have privileges to access database

But, I can access the database using direct link as: http://www.my_domain_name/phpmyadmin/index.php using same user and password. I will appreciate your kind help. Thanks

Note: 'my_domain_name' is being converted to an ip_address. When I place this IP to my browser, it takes me to the website of my Internet provider.

Update: It is clear that domain_name is being replaced with ip_address of Internet provider. How can I resolve this issue? Please do share any link. Thanks.

Upvotes: 0

Views: 8908

Answers (2)

Ahmad Raza
Ahmad Raza

Reputation: 1929

The issue was that by default, MySql server does't assign necessary privileges to the user to access database remotely. So, open your SqlServer console and use this command to assign privileges to the 'root' user as:

GRANT ALL ON root.* TO 'root'@'ip_address_of_server' IDENTIFIED BY 'password_for_root' ;
FLUSH PRIVILEGES;

OR

 GRANT ALL ON root.* TO 'root'@'server_name' IDENTIFIED BY 'password_for_root' ;
    FLUSH PRIVILEGES;

Refer to this answer for more information: pymysql cannot connect to mysql

Upvotes: 0

Willis Blackburn
Willis Blackburn

Reputation: 8204

MySQL access control depends on the IP address of the client not the IP address of the server.

I assume that you're connecting to some cloud instance of MySQL, in other words, it's not running on your local machine. (If it's local just use "localhost" instead of "my_domain_name.")

When you connect to the MySQL server "my_domain_name," it sees an incoming connection from your public IP address. (If you Google for "what's my IP address," the address that Google shows you should match the one in the error message.) If you enter that IP address into your browser, you'll be connecting to your own router. If you got that router from your ISP, it's possible they set it up so that connecting to the router redirects you to the ISP's own home page.

In MySQL, the client address is part of a user's identity. In other words, john@host1 and john@host2 are two different users to MySQL. You specify the host when you create the user:

# Only applies to john connecting from host1
CREATE USER 'john'@'host1' IDENTIFIED BY 'password';

You can also use a wildcard for the client address, which means a user with that name connecting from any client address.

# john can connect from any host
CREATE USER 'john'@'%' IDENTIFIED BY 'password';

Your MySQL instance was probably set up with a user called root@localhost. To connect from somewhere else, you could create another root@my-ip-address user where my-ip-address is your address, however, this user will not be the same as root@localhost. You will have to grant it privileges identical to root@localhost. Accessing MySQL in this way could be inconvenient because if your IP address ever changes, you will lose access. You could create a user root@% instead, which will work from any client, but then you are allowing access to your MySQL server from any client address as long as the user has the password.

The reason you can login from the web administration console is that when you access the console, you're not connecting to the database directly, instead you're interacting with the console, and the console is connecting to the database. The console is running on the same server as MySQL, so when it connects to MySQL, the connection is coming from localhost.

If you want command line access to MySQL, a better strategy would be to SSH to the MySQL server, then use mysql locally to connect as root@localhost.

Upvotes: 1

Related Questions