Cannot connect programmatically from remote client to mysql server

khedger

Active Member
Joined
Jun 7, 2019
Messages
152
Reaction score
85
Credits
1,294
Hi all,
I've been beating my head against the wall, reading, querying the internet, trying the 'solutions' I've found out there, but nothing's
helping me with this problem, so now I'm asking here.
I have a client laptop and a server. The server is running mySQL 8. Both systems are running python3. As I alluded to above, I've done all kinds of fiddling with the mySQL users, /etc/my.cnf, etc.
I have two users defined: root, and grunt. I can log into mysql on the server and do stuff - no problem.
From my laptop, I can do 'mysql -h "serverIP" -u 'root or grunt' -p and log into the server with no problem.
However when I try to execute a python3 script with the following code:
Code:
cnx = mysql.connector.connect(user='grunt',
                          password='xxxx',
                          host='192.168.1.100',
                          unix_socket='/tmp/mysqld.sock',
                          database='itunes')

I've tried using /var/run/mysqld/mysqld.sock and linking this to /tmp/mysqld.sock as you see in the code above. Nothing
affects the outcome which is the following message:
Code:
Exception in Tkinter callback
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/mysql/connector/network.py", line 452, in open_connection
    self.sock.connect(self.unix_socket)
FileNotFoundError: [Errno 2] No such file or directory

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3.6/tkinter/__init__.py", line 1705, in __call__
    return self.func(*args)
  File "./test.py", line 165, in srchit
    artistSearchResults = myDB.doDB(searchterm)
  File "./test.py", line 207, in doDB
    database='itunes')
  File "/usr/lib/python3/dist-packages/mysql/connector/__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 94, in __init__
    self.connect(**kwargs)
  File "/usr/lib/python3/dist-packages/mysql/connector/abstracts.py", line 722, in connect
    self._open_connection()
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 207, in _open_connection
    self._socket.open_connection()
  File "/usr/lib/python3/dist-packages/mysql/connector/network.py", line 455, in open_connection
    errno=2002, values=(self.get_address(), _strioerror(err)))
mysql.connector.errors.InterfaceError: 2002: Can't connect to local MySQL server through socket '/tmp/mysqld.sock' (2 No such file or directory)

the first thing I wonder about is the part that says "Can't connect to local MySQl server...." seems to me that it should "REMOTE"
Second thing is...why can't it connect? What's different about my python script connecting and my sitting at the command line and connecting?

Any help would be GREATLY appreciated!

keith
 


Hmmm.....so people are reading my note, but not replying. Okay, let me try to ask a more specific question:
What is the difference between what happens when I login to MySQL from a remote client's command line (i.e. "mysql -h 'ip' -u 'mysql login' -p") and doing this from a python script:
Code:
cnx = mysql.connector.connect(user='grunt',
                          password='xxxx',
                          host='192.168.1.100',
                          unix_socket='/tmp/mysqld.sock',
                          database='itunes')

- would both use the same socket
- would both access the socket the same way
- would both authenticate (os and mysql) the same way
- is there a list of mysql permissions or parameters I can check to allow programmatic connection

Again, any help would be greatly appreciated...just point me in the right direction....

keith
 
Last edited:
The error seems to indicate that the socket does not exist. I know this is simple but have you checked to ensure the socket, /tmp/mysqld.sock, in fact does exist? On my RHEL based systems, the socket is defined in /etc/my.cnf and is located here: /var/lib/mysql/mysql.sock.
 
The error seems to indicate that the socket does not exist. I know this is simple but have you checked to ensure the socket, /tmp/mysqld.sock, in fact does exist? On my RHEL based systems, the socket is defined in /etc/my.cnf and is located here: /var/lib/mysql/mysql.sock.
Yes, I've created a link from /var/run/mysqld/mysqld.sock /tmp/mysqld.sock. I've also tried using /var/run/mysqld/mysqld.sock directly. In my.cnf I have
Code:
[mysqld]
/var/run/mysqld/mysld.conf

keith
 
On the client host, the socket won't exist and I would suggest using the port parameter instead. So, the connection string would look something like this:

Code:
cnx = mysql.connector.connect(user='grunt',
                          password='xxxx',
                          host='192.168.1.100',
                          port='3306',
                          database='itunes')

Then, make sure on the server side, port 3306 is open in firewalld (or iptables).

Alternatively, looking at the documentation, you can omit the port altogether if you're using the default (3306).
 
On the client host, the socket won't exist and I would suggest using the port parameter instead. So, the connection string would look something like this:

Code:
cnx = mysql.connector.connect(user='grunt',
                          password='xxxx',
                          host='192.168.1.100',
                          port='3306',
                          database='itunes')

Then, make sure on the server side, port 3306 is open in firewalld (or iptables).

Alternatively, looking at the documentation, you can omit the port altogether if you're using the default (3306).
Thanks. I'll give that a try!
Out of curiosity, where did you find this doc? I tried to find this info and couldn't.

keith
 
Getting rid of the 'socket' argument worked. I then had to solve an authentication problem, but now everything works. Thanks for the help!

keith
 

Members online


Top