Connection to remote msql database fails

Python Help
1

the following code runs well

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
     user="root",
    password="xxxxx"
)

print(mydb)  # <mysql.connector.connection_cext.CMySQLConnection object>

but this code gives me an error

import mysql.connector

mydb = mysql.connector.connect(
    host="172.16.1.233",
    user="root",
    password="xxxxx"
)

print(mydb)  # <mysql.connector.connection_cext.CMySQLConnection object>

mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user ‘root’@‘imac-2.my_domain.de’ (using password: YES)
The credentials are valid, I can connect from the mysql command line without any problem. the server at 172.16.1.233 is my production SQL server used for many things. I assume the problem is that ‘root’@‘imac-2.my_domain.de’ is passed instead of simply ‘root’, but have no idea how to fix. I am on a Mac Ventura 13.6.9.

Google research did not help
Apprecieate any help or hint

Kind Regards
Rainer

2

Hi Rainer,

I just got your second error when trying to log in from localhost. Perhaps this is insecure or is a bad idea, but the error was fixed and I could log in with mysql-connector-python from a Python venv, after I connected from Bash using mysql -u root -p and ran:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xxxxx';
FLUSH PRIVILEGES;

so maybe you need to do that for ‘root’@‘imac-2.my_domain.de’ ?

3

Yeah, as above, this allows connections from anywhere which is a bad idea (try it first replacing @'%' with @'imac-2.my_domain.de'), but I could get a connection from a remote machine by doing:

CREATE USER 'root'@'%' IDENTIFIED BY 'xxxxx';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

and editing /etc/mysql/mysql.conf.d/mysqld.cnf to

bind-address            = 0.0.0.0

then from bash: sudo systemctl restart mysql. Check your firewall (e.g. ufw) too.

4

Can you show the exact mysql command line you’re using for the remote connection, please? (Other than, of course, censoring the password.) There may be a difference between how that’s connecting and how Python is connecting.

5

I think I will give the permissions, the command line that worked
mysql -u root -p -h

I normally did that from a Linux machine. May be Mac OS does that a litte bit different. I installed I an my Mac for a Python Project and install mysql dp, so I can test with local DB, and later when finished with teh remote DB

Thank you for Help

6

That’s not specifying the IP address though. MySQL, unlike a lot of databases, will treat your credentials differently based on the IP you’re connecting from, and does not distinguish between “you got your password wrong” and “you’re not allowed to connect from there”. PostgreSQL, on the other hand, has a single password (if any) for a given user, and then has separate rules to say which addresses and methods can be used for connecting.

So you’ll need to try a MySQL command line invocation from the same IP, to the same IP, using the same method, for it to be equivalent.

7

I specified the IP address , But I wrote it in pointed bracket in the post. Do not know why it was not visible.
In any case I do know what to do
Thank you for help
Regards
Rainer

8

Ah, that’s possible. You can avoid that problem by putting the command in a code block, although I would personally prefer that you keep the command exactly in the same format - so, for example, if the actual IP address has to be obscured for safety, replace it with 172.16.17.18 or something.

In any case, what you want to do is first figure out a command line invocation that absolutely definitely works, and then go into Python and imitate it precisely. That means the exact same IP address (and running from the same computer, so the source IP will be the same), the same user, the same password. Any other options that might also be important.

Beyond that, I have no idea why it would fail.