Почему возникает ошибка подключения к MySQL через SSH из Python

Ошибка Access denied for user 'root'@'localhost' при подключении к MySQL через SSH из Python при работающем соединении в DBeaver возникает из-за различий в поддержке методов аутентификации между разными клиентами и настройками базы данных.

Содержание

• Основные причины несоответствия подключения
• Различия в аутентификации: MySQL 8.0 и методы аутентификации
• Особенности SSH-туннелей и локального подключения
• Различия между DBeaver и Python-коннектором
• Решения и пошаговые инструкции
• Диагностика и проверка настроек

Основные причины несоответствия подключения

Основная причина несоответствия работы DBeaver и Python-коннектора при подключении к MySQL через SSH заключается в различиях поддерживаемых методов аутентификации.

DBeaver, как правило, лучше справляется с современными методами аутентификации MySQL 8.0, в то время как стандартный Python-коннектор mysql-connector-python по умолчанию может не поддерживать новый метод caching_sha2_password.

Важно: Когда вы подключаетесь через SSH-туннель, вы фактически подключаетесь к localhost на сервере MySQL, а не к удаленному хосту. Это означает, что аутентификация происходит с использованием локальных учетных данных MySQL, а не SSH-кредитов.

Различия в аутентификации: MySQL 8.0 и методы аутентификации

Проблема с caching_sha2_password

В MySQL 8.0 по умолчанию используется метод аутентификации caching_sha2_password, который не поддерживается некоторыми старыми клиентами, включая определенные версии Python-коннекторов.

-- Проверка текущего метода аутентификации для пользователя
SELECT Host, User, plugin FROM mysql.user WHERE User = 'root';

Результаты могут быть такими:

• root@localhost - caching_sha2_password (не работает с Python)
• root@% - mysql_native_password (работает с Python)

Почему DBeaver работает, а Python нет?

Согласно исследованиям, DBeaver имеет более широкую поддержку современных методов аутентификации MySQL. Он корректно обрабатывает:

• caching_sha2_password с зашифрованным обменом паролями
• mysql_native_password (устаревший, но широко поддерживаемый)
• Различные комбинации аутентификации через SSH

В то же время, Python-коннектор mysql-connector-python в версиях до 8.0.19 имел ограниченную поддержку caching_sha2_password.

Особенности SSH-туннелей и локального подключения

Как работает SSH-туннель в этом контексте

При настройке SSH-туннеля в DBeaver или Python происходит следующее:

1. SSH-соединение устанавливается с удаленным сервером
2. Создается локальный порт (например, localhost:3307), который перенаправляет трафик на удаленный MySQL-порт
3. Ключевой момент: При подключении к localhost:3307 клиент подключается к MySQL-серверу на удаленной машине, но аутентификация все равно происходит с использованием локальных пользователей MySQL

Проблема с root@localhost

Ошибка Access denied for user 'root'@'localhost' возникает потому, что:

• MySQL на удаленном сервере не разрешает аутентификацию root с хоста localhost через SSH-туннель
• Или используется неподдерживаемый метод аутентификации
• Пользователь root может быть настроен только для подключения с определенных хостов

Различия между DBeaver и Python-коннектором

Поддержка аутентификации

Почему DBeaver “проигнорировал” проблему?

DBeaver может успешно подключиться, потому что:

1. Автоматически определяет поддерживаемый метод аутентификации
2. Позволяет вручную указать метод аутентификации в настройках
3. Имеет встроенную обработку различных сценариев подключения
4. Поддерживает безопасный обмен паролями через SSH

Python-коннектор не имеет таких возможностей по умолчанию.

Решения и пошаговые инструкции

Решение 1: Изменение метода аутентификации на mysql_native_password

-- Изменение метода аутентификации для root@localhost
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

-- Применение изменений
FLUSH PRIVILEGES;

Важно: Этот метод делает аутентификацию менее безопасной, но обеспечивает совместимость со старыми клиентами.

Решение 2: Создание отдельного пользователя для Python-приложений

-- Создание нового пользователя с поддержкой native password
CREATE USER 'python_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secure_password';

-- Предоставление необходимых привилегий
GRANT ALL PRIVILEGES ON *.* TO 'python_user'@'localhost' WITH GRANT OPTION;

-- Применение изменений
FLUSH PRIVILEGES;

Решение 3: Обновление Python-коннектора

Убедитесь, что вы используете последнюю версию коннектора:

pip install --upgrade mysql-connector-python

Решение 4: Ручная настройка аутентификации в Python

import mysql.connector

connection = mysql.connector.connect(
    host='localhost',
    port=3307,  # Порт SSH-туннеля
    user='root',
    password='your_password',
    auth_plugin='mysql_native_password'  # Явное указание метода
)

Диагностика и проверка настроек

Проверка текущих настроек аутентификации

-- Проверка всех пользователей и их методов аутентификации
SELECT Host, User, plugin, authentication_string FROM mysql.user;

-- Проверка привилегий root пользователя
SHOW GRANTS FOR 'root'@'localhost';

Проверка SSH-туннеля

# Проверка туннеля вручную
ssh -L 3307:localhost:3306 user@remote_server -N

# Подключение к MySQL через туннель с использованием mysql-client
mysql -h localhost -P 3307 -u root -p

Проверка версии Python-коннектора

import mysql.connector
print(mysql.connector.__version__)

Рекомендуемая версия: 8.0.19 и выше для лучшей поддержки caching_sha2_password.

Источники

1. MySQL 8.4 Reference Manual - Caching SHA-2 Pluggable Authentication
2. How to Run MySQL 8.0 with Native Password Authentication
3. Python - Authentication plugin ‘caching_sha2_password’ is not supported - Stack Overflow
4. MySQL access denied error when connecting via SSH tunnel - Server Fault
5. DBeaver SSH Configuration Documentation
6. ALTER USER syntax for authentication plugin

Заключение

1. Основная причина несоответствия работы DBeaver и Python-коннектора заключается в различиях поддерживаемых методов аутентификации, особенно в MySQL 8.0 с caching_sha2_password.

2. DBeaver имеет более широкую поддержку современных методов аутентификации и автоматически обрабатывает различные сценарии подключения через SSH.

3. Решение проблемы включает либо изменение метода аутентификации на mysql_native_password, либо создание отдельного пользователя для Python-приложений, либо обновление коннектора до последней версии.

4. Для диагностики используйте проверку текущих настроек аутентификации MySQL и тестирование SSH-туннеля вручную перед настройкой в Python.

5. Профилактические меры включают использование последних версий коннекторов и регулярное обновление MySQL для поддержания безопасности и совместимости.