Troubleshooting MySQL Access Denied Error 1698 (28000)

mysql -u edge_user -p -h 127.0.0.1 -P 3306 edge_db and I got error 1698 (28000) access denied for user ... @localhos

The error message you're encountering, "1698 (28000): Access denied for user '...'@'localhost'," typically indicates that the MySQL server is rejecting the connection attempt due to authentication issues. Here are some steps you can take to troubleshoot and resolve this issue:

1. Check Username and Password:

   • Ensure that you are using the correct username and password. Double-check for any typos or case sensitivity issues.

2. User Privileges:

   • Verify that the user has the necessary privileges to access the database. You can check this by logging in as a root user and running:

     SHOW GRANTS FOR 'edge_user'@'localhost';
     

   • If the user does not have the necessary privileges, you can grant them using:

     GRANT ALL PRIVILEGES ON edge_db.* TO 'edge_user'@'localhost' IDENTIFIED BY 'your_password';
     FLUSH PRIVILEGES;
     

3. Host Specification:

   • The error message mentions 'localhost', but your command specifies -h 127.0.0.1. MySQL treats 'localhost' and '127.0.0.1' differently due to the way it handles socket connections versus TCP/IP connections.
   • Ensure that the user is allowed to connect from '127.0.0.1'. You can check this by running:

     SELECT host FROM mysql.user WHERE user = 'edge_user';
     

   • If necessary, add or update the host entry:

     CREATE USER 'edge_user'@'127.0.0.1' IDENTIFIED BY 'your_password';
     GRANT ALL PRIVILEGES ON edge_db.* TO 'edge_user'@'127.0.0.1';
     FLUSH PRIVILEGES;
     

4. MySQL Authentication Plugin:

   • Check if the user is using the correct authentication plugin. Sometimes, the default plugin might be set to auth_socket, which does not use passwords.
   • You can change the authentication plugin to mysql_native_password if needed:

     ALTER USER 'edge_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
     

5. MySQL Service:

   • Ensure that the MySQL service is running and listening on the correct port (3306 in this case).

6. Firewall and Network:

   • Check if there are any firewall rules or network configurations that might be blocking the connection to the MySQL server.

7. Log Files:

   • Check the MySQL error log for any additional information that might help diagnose the issue. The log file is usually located in /var/log/mysql/ or /var/log/mysqld.log.

By following these steps, you should be able to identify and resolve the issue causing the "access denied" error.