MySQL might return this error when you try to connect to a MySQL Server:
Error 1045 - Access denied for user 'user_name'@'host'...
This is one of the most common errors in MySQL. It typically means you are trying to connect using a user account that doesn't have enough privileges to connect.
By default, after installation, MySQL only allows localhost connection and blocks any remote connection attempts until the user privileges get re-configured properly. That explains why you can't connect from a remote client while other server-side programs seem to connect without any issues.
To establish a remote connection, you have to re-configure it and give it proper privileges:
CREATE USER 'user_name'@'host' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'host';
Or:
CREATE USER 'user_name'@'%' IDENTIFIED BY 'Password';
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'%';
For testing purpose, you can start the server with the --skip-grant-tables option:
$ mysqld --skip-grant-tables
Then you can access the MySQL grant tables using the SHOW GRANTS statement to check the privileges and change if you want:
SHOW GRANTS FOR 'user'@'host';
When finished, flush privileges:
FLUSH PRIVILEGES;
If the issue arises with the root account only after upgrading MySQL, it's likely the authentication plugin has changed to auth_socket by default. So what you can do is to change the authentication method from auth_socket to mysql_native_password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Note that mysql_native_password is deprecated in MySQL 8.0 and removed in MySQL 9.0. On newer versions, use caching_sha2_password instead.
Then flush privileges:
FLUSH PRIVILEGES;
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS
