Troubleshoot Access Denied Error: Connect DBeaver to a local MySQL database

When trying to connect to a local MySQL database created via terminal with DBeaver

you can encounter Access Denied error:

This error typically indicates that the username and password combination you've provided does not have permission to access the MySQL server.

Here are a few steps to troubleshoot and possibly resolve this issue:

1. Login to MySQL via Terminal: Make sure you can login via the terminal first:

    sudo mysql -u root -p
   

2. Check User's Host Permissions: Once you're in the MySQL shell, check the hosts from which the user is allowed to connect:

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

   

   If you don’t see a row with localhost as the host, that's part of the problem. You might see a host like %, which stands for any host but sometimes, localhost is treated separately than other hosts.

3. Create/Update User for Localhost: If you don’t have a user for localhost, you can create one or grant it the necessary permissions:

    CREATE USER 'user'@'localhost' IDENTIFIED BY 'mypass';
    GRANT ALL PRIVILEGES ON studentdemo.* TO 'user'@'localhost';
   

   If the user already exists, you can update its password just to ensure it's set correctly:

    ALTER USER 'user'@'localhost' IDENTIFIED BY 'mypass';
   

4. Flush Privileges: Always remember to flush privileges after making any changes to user permissions:

    FLUSH PRIVILEGES;
   

5. Plugin Authentication Issue: Sometimes, especially in MySQL 5.7+, the issue might be related to the default authentication plugin. Check the plugin being used:

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

   

   If it says auth_socket or something other than mysql_native_password, that might be your issue. You can change the plugin as follows:

    ALTER USER 'user'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'mypass';
   

   Then, flush privileges again.

6. MySQL Bind Address: Check if MySQL is binding to 127.0.0.1 and not ::1 (IPv6 localhost) or some other address. Check the bind address in the MySQL configuration file (/etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf). Look for the line:

    bind-address = 127.0.0.1
   

   Ensure it’s set to 127.0.0.1 and not something else.

7. Firewall and MySQL Configuration: Ensure there's no firewall rule blocking your connection, and the MySQL configuration is set to listen on port 3306. This is typically not an issue if you are connecting locally, but it's something to consider.

8. Try Without Password: Sometimes, the user might not have a password set. Try connecting without a password from DBeaver.

After trying the above steps, attempt to connect through DBeaver again. If the issue persists, double-check your DBeaver settings to ensure you're entering the correct details, and also check the MySQL error log for any additional clues.

If successful, you can now connect to your database: