Resolving MySQL "Access denied" Issue

1. Verify the `root` User's Password:

Ensure that the password you are using (`root@123`) is correct for the `root` user.

2. Check MySQL User Privileges:

mysql -u root -p

If you get access, check the privileges for the `root` user:

SELECT user, host, authentication_string FROM mysql.user WHERE user = 'root';

3. Reset the `root` User Password:

sudo systemctl stop mysql

sudo mysqld_safe --skip-grant-tables &

mysql -u root

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root@123';

FLUSH PRIVILEGES;

sudo systemctl stop mysql

sudo systemctl start mysql

4. Verify Connection with Correct Parameters:

mysql -u root -p

5. Update Application Configuration:

Ensure your application configuration correctly uses the provided parameters.

Example of Using Environment Variables in a Python Script:

import mysql.connector
import os

try:
    connection = mysql.connector.connect(
        host='localhost',
        port=3306,
        user=os.getenv('DB_USER', 'root'),
        password=os.getenv('DB_PASS', 'root@123'),
        unix_socket=os.getenv('SOCKET_PATH', '/var/run/mysqld/mysqld.sock')
    )
    
    if connection.is_connected():
        print("Connected to MySQL Server")
        cursor = connection.cursor()
        cursor.execute("SELECT VERSION();")
        record = cursor.fetchone()
        print("Your MySQL version is:", record)
    
except mysql.connector.Error as err:
    print(f"Error: {err}")

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Checking MySQL Logs:

tail -f /var/log/mysql/error.log

If these steps do not resolve the issue, please provide more details about your setup and any additional error messages you are encountering.

Verify the User and Host

mysql -u root -p

Enter the password `root@123` when prompted. Ensure `root` user is allowed to connect from `localhost`.

Resetting the Root Password

sudo systemctl stop mysql

sudo mysqld_safe --skip-grant-tables &

mysql -u root

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root@123';

FLUSH PRIVILEGES;

sudo systemctl stop mysql

sudo systemctl start mysql

Verify Connection Parameters in Your Application

Ensure your application uses the correct parameters:

import mysql.connector

try:
    connection = mysql.connector.connect(
        host='localhost',
        port=3306,
        user='root',
        password='root@123',
        unix_socket='/var/run/mysqld/mysqld.sock'
    )
    
    if connection.is_connected():
        print("Connected to MySQL Server")
        cursor = connection.cursor()
        cursor.execute("SELECT VERSION();")
        record = cursor.fetchone()
        print("Your MySQL version is:", record)
    
except mysql.connector.Error as err:
    print(f"Error: {err}")

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

TLS Configuration

If using SSL, configure your application accordingly:

import mysql.connector

try:
    connection = mysql.connector.connect(
        host='localhost',
        port=3306,
        user='root',
        password='root@123',
        unix_socket='/var/run/mysqld/mysqld.sock',
        ssl_ca='/path/to/ca.pem'
    )
    
    if connection.is_connected():
        print("Connected to MySQL Server")
        cursor = connection.cursor()
        cursor.execute("SELECT VERSION();")
        record = cursor.fetchone()
        print("Your MySQL version is:", record)
    
except mysql.connector.Error as err:
    print(f"Error: {err}")

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Checking MySQL User Privileges

SELECT user, host, authentication_string, plugin FROM mysql.user WHERE user = 'root';

Ensure `root` user has necessary privileges and the plugin is set to `mysql_native_password` if using the old authentication method.

If the issue persists, provide more details about your application and error messages for further assistance.