Python 3.8
Mysql 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
Hi,
I want to connect to a distant mysql server using python's mysqldb connector and paramiko's sshtunnelforwarder.
I can connect to the database remotely without any problems by executing the following:
Connecting to database using mysql password authentication
server = new_ssh_server(config)
with server:
print('Connection', server.local_bind_address)
cnx = MySQLdb.connect(host = '127.0.0.1',
port = server.local_bind_port,
user = config['user'],
passwd = config['password'],
db = config['db'])
Queries work, I can read/write to database, no problem.
I would like to connect to database without supplying mysql password, by using mysql auth_socket authentication method, through ssh.
My attempts at this can be resumed by the following code:
Connecting to database using mysql auth_socket authentication
with server as tunnel:
print('Tunnel:', tunnel.local_bind_address)
cnx = MySQLdb.connect(host = 'localhost', user = 'hillbilly', password = '', db='tutut')#, unix_socket="/tmp/mysql.sock")
res = pd.read_sql('select * from users;', cnx)
print(res)
Which throws the following error:
File "connect_ssh_mysql_auth_socket.py", line 12, in <module>
cnx = MySQLdb.connect(host = 'localhost', user = 'hillbilly', password = '', db='rsotest2')#, unix_socket="/tmp/mysql.sock")
File "...../lib/python3.8/site-packages/MySQLdb/__init__.py", line 84, in Connect
return Connection(*args, **kwargs)
File "...../lib/python3.8/site-packages/MySQLdb/connections.py", line 179, in __init__
super(Connection, self).__init__(*args, **kwargs2)
MySQLdb._exceptions.OperationalError: (2002, "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)")
I have and existing mysqld.sock on the distant server that I symlinked to /tmp/mysql.sock, but the error remains. I have also added the next line to /etc/mysql/mysql.conf.d/mysql.cnf:
socket=/run/mysqld/mysqld.sock
But I still get the same error when trying to connect remotely.
Specifying the unix_socket='/run/mysqld/mysqld.sock' to mysqldb connector (commented in Connecting to database using mysql auth_socket authentication) does not fix the issue.
I seem to misunderstand the use of mysql.sock, mysqld.sock. I was not able to find nor create a mysql.sock socket. Is what I am trying to do possible? I remember reading somewhere that unix sockets only work locally, does this mean it is not achievable?
Any help/explanation would be appreciated.
(EDIT AND CLOSING) So this is not possible. Following this thread, auth_socket needs local access to the socket file (usually /tmp/mysql.sock) to run autentication tests, so not accessible through ssh tunneling.
