I am trying to use my SSL certs to connect to the my sql from python MySQLdb module. I am doing like
Python 3.8.6 (default, Feb 4 2021, 21:45:23)
Type 'copyright', 'credits' or 'license' for more information
IPython 8.12.2 -- An enhanced Interactive Python. Type '?' for help.
In [1]: import MySQLdb
...: # Connect
...: db = MySQLdb.connect(host="mysql.host.name",
...: user="username",
...: db="database",
...: ssl={"cert": "/var/lib/certs/mysql_prod.cert.pem",
...: "key": "/var/lib/keys/ui.key.pem"})
...:
...: cursor = db.cursor()
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
Cell In[1], line 3
1 import MySQLdb
2 # Connect
----> 3 db = MySQLdb.connect(host="mysql.host.name",
4 user="username",
5 db="database",
6 ssl={"cert": "/var/lib/certs/mysql_prod.cert.pem",
7 "key": "/var/lib/keys/ui.key.pem"})
9 cursor = db.cursor()
File /var/virtualenvs/venv/lib/python3.8/site-packages/MySQLdb/__init__.py:123, in Connect(*args, **kwargs)
120 """Factory function for connections.Connection."""
121 from MySQLdb.connections import Connection
--> 123 return Connection(*args, **kwargs)
File /var/virtualenvs/venv/lib/python3.8/site-packages/MySQLdb/connections.py:185, in Connection.__init__(self, *args, **kwargs)
182 # PEP-249 requires autocommit to be initially off
183 autocommit = kwargs2.pop("autocommit", False)
--> 185 super().__init__(*args, **kwargs2)
186 self.cursorclass = cursorclass
187 self.encoders = {k: v for k, v in conv.items() if type(k) is not int}
OperationalError: (2059, "Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory")
If I use same certs, with my mysql command, it works.
-bash-4.2$ sudo mysql --ssl-mode=REQUIRED --ssl-cert=/var/lib/certs/mysql_prod.cert.pem --ssl-key=/var/lib/keys/ui.key.pem -u username -h mysql.host.name database
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 118485292
Server version: 8.0.32-24 Source distribution
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
I tried passing ssl_mode in MySQLdb.connect but it gives error. NotSupportedError: MySQL client library does not support ssl_mode specification.
I also check the version of mysqlclient, its the latest one.
$ pip freeze | grep mysqlclient
mysqlclient==2.1.1
I am not sure, what is missing? the ssl_mode is compulsory to pass in python or I have to install which will add /usr/lib64/mysql/plugin/caching_sha2_password.so.
I also check Authentication plugin 'caching_sha2_password' cannot be loaded, but it suggest to change password, but we are able to login without password from mysql command, so I pass that option, second option its mention to update my.cnf, where I have to change that, in my client side or from my server side?
