Python – Connect MySQL through localhost not working but 127.0.0.1 is working?

I just setup a VPS with a Mysql Database on it. I’m able to log into the database in the SSH terminal with mysql -uroot -p however, the python file shown below is unable to form a connection, and errors with Access denied for root@localhost (using password: YES). I’m confused as to why this is happening. I’m 100% sure my password is correct. I’m using mysql.connector

Python file:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="pwd",
  database="db"
)

mycursor = mydb.cursor()

sql = "INSERT INTO test (tests) VALUES (%s)"
val = ("testing")

mycursor.execute(sql, val)

mydb.commit()

I’m a bit new to this DB and VPS stuff, so please bear with me if I don’t understand on the first go.

Thanks!

Answer

In addition to @don’s answer

I assume it has something to do with the dns not properly resolving localhost. I have experienced that before, but outside of just mapping localhost to 127.0.01 in the host’s file,

This can also be fixed by changing a value in your /etc/mysql/my.cnf file.

It’s likely your bind-address line is

bind-address = localhost 

You can try changing it to

bind-address = 127.0.0.1

You can also set it to 0.0.0.0 but this will expose the connection to the outside world (inc. public IP), so this is highly not recommended but will solve the problem.