(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

Solution 1:

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
CREATEUSER'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 connectfromany host
CREATEUSER'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.

Solution 2:

---

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:

GRANTALLON root.*TO'root'@'ip_address_of_server' IDENTIFIED BY'password_for_root' ;
FLUSH PRIVILEGES;

OR

GRANTALLON root.*TO'root'@'server_name' IDENTIFIED BY'password_for_root' ;
    FLUSH PRIVILEGES;

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