Python: error connecting to mysql server using socket

Question

I'm tring to connect to my local mysqlserver.

The following code works on another machine:

root@host> mysql --protocol=SOCKET
MariaDB [(none)]>
root@host> ls -la /var/run/mysqld/mysqld.sock 
srwxrwxrwx 1 mysql mysql 0 Jun 9 20:27 /var/run/mysqld/mysqld.sock
root@host> python3
>>> import mysql.connector
>>> mysql.connector.connect( unix_socket='/var/run/mysqld/mysqld.sock' )
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/lib/python3/dist-packages/mysql/connector/__init__.py", line 173, in connect
 return MySQLConnection(*args, **kwargs)
File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 102, in __init__
 self.connect(**kwargs)
File "/usr/lib/python3/dist-packages/mysql/connector/abstracts.py", line 735, in connect
 self._open_connection()
File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 250, in _open_connection
 self._do_auth(self._user, self._password,
File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 172, in _do_auth
 self._auth_switch_request(username, password)
File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 216, in _auth_switch_request
 raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user ''@'localhost'

What may be the root of the problem?

Note that

• I want to use a Socket and not TCP for reasons.
• I don't want to use username/password.
• This works on my other host but I can't figure out why it wont work on this one.

• furas: error shows Access denied for user ''@'localhost' so you can't access it with empty user.
• Scheintod: Thanks for your comment. I tried adding user="root" but this resulted in 1698 (28000): Access denied for user 'root'@'localhost'
• furas: did you try with password= ..? I had problem to connect using mysql-connector but not when I removed it and install mysql-connector-python (created by MySQL)

Answer 1

If you want to use mysql with socket you can use this method :

import MySQLdb
db = MySQLdb.connect("localhost", "root", "l542212h", "TESTDB", charset='utf8' ,unix_socket="/tmp/mysql.sock")

I hope that can help you to resolve your issue.

• Scheintod: Thanks for your anser again and for your effort ;) I'd like to not use username and password like I don't need them in mysql --protocol=SOCKET command. As I wrote this works on another host quite well.

Answer 2

what I suggest first of all is to use a GUI tools like MYSQL workbench or Dbeaver to connect to your database, if it's established you can use this script to connect to your database:

from getpass import getpass
from mysql.connector import connect, Error
try:
 with connect(
 host="localhost",
 user=input("Enter username: "),
 password=getpass("Enter password: "),
 ) as connection:
 print(connection)
except Error as e:
 print(e)

I hope that these can help you to resolve your issue.

• Scheintod: Thanks for your answer. I know how to connect to mysql using TCP. My question is about connecting using a socket. root@host> mysql --protocol=SOCKET tests if this works (which it does) but not with python on this particular host.

Answer 3

You need to specify the user, and clear the password; e.g.

mysql.connector.connect(unix_socket='/run/mysqld/mysqld.sock', user='root', password=None)