MySQL SSH Connection Error from Python: Access Denied Solution

“Access denied for user ‘root’@‘localhost’” Error When Connecting to MySQL Through SSH from Python

The error Access denied for user 'root'@'localhost' when connecting to MySQL through SSH from Python, while the connection works in DBeaver, occurs due to differences in authentication method support between different clients and database settings.

Contents

• Main causes of connection mismatch
• Authentication differences: MySQL 8.0 and authentication methods
• SSH tunnel features and local connection differences
• Differences between DBeaver and Python connector
• Solutions and step-by-step instructions
• Diagnosis and settings verification

Main causes of connection mismatch

The main reason for the mismatch between DBeaver and Python connector when connecting to MySQL through SSH lies in the differences in supported authentication methods.

DBeaver generally handles MySQL 8.0 modern authentication methods better, while the standard Python connector mysql-connector-python may not support the new caching_sha2_password method by default.

Important: When you connect through an SSH tunnel, you’re actually connecting to localhost on the MySQL server, not to a remote host. This means authentication occurs using local MySQL credentials, not SSH credentials.

Authentication differences: MySQL 8.0 and authentication methods

The caching_sha2_password problem

In MySQL 8.0, the default authentication method is caching_sha2_password, which is not supported by some older clients, including certain versions of Python connectors.

-- Check current authentication method for user
SELECT Host, User, plugin FROM mysql.user WHERE User = 'root';

Results might be:

• root@localhost - caching_sha2_password (doesn’t work with Python)
• root@% - mysql_native_password (works with Python)

Why DBeaver works but Python doesn’t?

According to research, DBeaver has broader support for modern MySQL authentication methods. It correctly handles:

• caching_sha2_password with encrypted password exchange
• mysql_native_password (legacy but widely supported)
• Various authentication combinations through SSH

Meanwhile, the Python mysql-connector-python connector in versions before 8.0.19 had limited support for caching_sha2_password.

SSH tunnel features and local connection differences

How SSH tunnel works in this context

When setting up an SSH tunnel in DBeaver or Python, the following happens:

1. SSH connection is established with the remote server
2. A local port (e.g., localhost:3307) is created that redirects traffic to the remote MySQL port
3. Key point: When connecting to localhost:3307, the client connects to the MySQL server on the remote machine, but authentication still occurs using local MySQL users

The problem with root@localhost

The error Access denied for user 'root'@'localhost' occurs because:

• MySQL on the remote server doesn’t allow authentication for root from the localhost host through the SSH tunnel
• Or an unsupported authentication method is being used
• The root user may be configured to only connect from specific hosts

Differences between DBeaver and Python connector

Authentication support

Why DBeaver “ignored” the problem?

DBeaver can successfully connect because:

1. It automatically detects the supported authentication method
2. It allows manually specifying the authentication method in settings
3. It has built-in handling for various connection scenarios
4. It supports secure password exchange through SSH

The Python connector doesn’t have these capabilities by default.

Solutions and step-by-step instructions

Solution 1: Change authentication method to mysql_native_password

-- Change authentication method for root@localhost
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

-- Apply changes
FLUSH PRIVILEGES;

Important: This method makes authentication less secure but ensures compatibility with older clients.

Solution 2: Create a separate user for Python applications

-- Create a new user with native password support
CREATE USER 'python_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secure_password';

-- Grant necessary privileges
GRANT ALL PRIVILEGES ON *.* TO 'python_user'@'localhost' WITH GRANT OPTION;

-- Apply changes
FLUSH PRIVILEGES;

Solution 3: Update Python connector

Make sure you’re using the latest version of the connector:

pip install --upgrade mysql-connector-python

Solution 4: Manual authentication setup in Python

import mysql.connector

connection = mysql.connector.connect(
    host='localhost',
    port=3307,  # SSH tunnel port
    user='root',
    password='your_password',
    auth_plugin='mysql_native_password'  # Explicitly specify method
)

Diagnosis and settings verification

Checking current authentication settings

-- Check all users and their authentication methods
SELECT Host, User, plugin, authentication_string FROM mysql.user;

-- Check root user privileges
SHOW GRANTS FOR 'root'@'localhost';

Checking SSH tunnel

# Manually check tunnel
ssh -L 3307:localhost:3306 user@remote_server -N

# Connect to MySQL through tunnel using mysql-client
mysql -h localhost -P 3307 -u root -p

Checking Python connector version

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

Recommended version: 8.0.19 and above for better caching_sha2_password support.

Sources

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

Conclusion

1. The main reason for the mismatch between DBeaver and Python connector lies in the differences in supported authentication methods, especially in MySQL 8.0 with caching_sha2_password.

2. DBeaver has broader support for modern authentication methods and automatically handles various connection scenarios through SSH.

3. Solutions to the problem include either changing the authentication method to mysql_native_password, creating a separate user for Python applications, or updating the connector to the latest version.

4. For diagnosis, use checking current MySQL authentication settings and manually testing the SSH tunnel before setting it up in Python.

5. Preventive measures include using the latest connector versions and regularly updating MySQL to maintain security and compatibility.