Как соединиться с mysql, используя SSL файл и флаг verify identity?

Question

[alwaystried]

Добрый вечер. Появилась база данных, которая требует подключения только по SSL и флагу Verify Identity.
Вся проблема в том, что мой код ну никак не хочет подключаться к базе. Использую AIOMYSQL. Возможно ответ подойдет и как для PyMysql.

Код:

conn = await aiomysql.connect(host='', port=3306, user='redst', password='',
                                  db='aiodb', ssl={'ssl' : {'ca': 'root.pem'}},
                                  loop=loop)

А ошибка..

ERROR:asyncio:SSL handshake failed
protocol: <asyncio.sslproto.SSLProtocol object at 0x1062c3f40>
transport: <_SelectorSocketTransport fd=12 read=idle write=<idle, bufsize=0>>


future: <Task finished name='Task-9' coro=<Dispatcher._process_polling_updates() done, defined at /Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiogram/dispatcher/dispatcher.py:407> exception=OperationalError(2003, "Can't connect to MySQL server on 'rc1b-nvx2jdtfj41mw1sl.mdb.yandexcloud.net'")>
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiomysql/connection.py", line 501, in _connect
    await self._request_authentication()
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiomysql/connection.py", line 706, in _request_authentication
    self._reader, self._writer = await asyncio.open_connection(
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/asyncio/streams.py", line 47, in open_connection
    transport, _ = await loop.create_connection(
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/asyncio/base_events.py", line 1080, in create_connection
    transport, protocol = await self._create_connection_transport(
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/asyncio/base_events.py", line 1110, in _create_connection_transport
    await waiter
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/asyncio/sslproto.py", line 631, in _on_handshake_complete
    raise handshake_exc
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/asyncio/sslproto.py", line 676, in _process_write_backlog
    ssldata = self._sslpipe.do_handshake(
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/asyncio/sslproto.py", line 116, in do_handshake
    self._sslobj = self._context.wrap_bio(
AttributeError: 'dict' object has no attribute 'wrap_bio'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiogram/dispatcher/dispatcher.py", line 415, in _process_polling_updates
    for responses in itertools.chain.from_iterable(await self.process_updates(updates, fast)):
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiogram/dispatcher/dispatcher.py", line 235, in process_updates
    return await asyncio.gather(*tasks)
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiogram/dispatcher/handler.py", line 116, in notify
    response = await handler_obj.handler(*args, **partial_data)
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiogram/dispatcher/dispatcher.py", line 283, in process_update
    return await self.callback_query_handlers.notify(update.callback_query)
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiogram/dispatcher/handler.py", line 116, in notify
    response = await handler_obj.handler(*args, **partial_data)
  File "/Users/redst/PycharmProjects/pythonProject2/main.py", line 142, in start
    conn = await aiomysql.connect(host='', port=3306, user='redst', password='',
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiomysql/connection.py", line 75, in _connect
    await conn._connect()
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiomysql/connection.py", line 519, in _connect
    raise OperationalError(2003,
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'rc1t'")

Answer 1

[Даниил Рыжов]

import asyncio
import ssl


ctx = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
# the root CA path is dependent on your system. 
ctx.load_verify_locations(cafile='/etc/ssl/cert.pem')

async def test_example(loop):
    pool = await aiomysql.create_pool(host='', port=3306, user='redst', password='',
                                  db='aiodb', loop=loop, ssl=ctx)