MySQL: Solving Access Denied for User Root

How to correctly solve a problem with MySQL Server that prevents logging in as root.

759 views
d

By. Jacob

Edited: 2019-11-05 08:53

When attempting to log in to a MySQL Server you might encounter the error: Access denied for user 'root'@'localhost'. This error happens for security reasons.

If your server is just hosting a single web service, then it might be fine to use the root user. But, you might as well create a second user to familiarize yourself more with MySQL and get used to enforcing stricter security.

In an environment where a server is hosting multiple web services that use the MySQL database, it is often better to create a dedicated user for each service. The individual users can then be granted access to specific databases only, preventing them from deleting databases of other services by accident or if compromised.

You can rarely be 100% sure that the code you run is secure, and as such, limiting what individual services or Virtual Hosts is able to do, can be a useful way to mitigate the damage from a compromised VHOST user.

The quick solution is to login to MySQL as root, and create a new user:

sudo mysql -uroot -p

Create a new MySQL user:

CREATE USER 'UserName'@'localhost' IDENTIFIED BY 'Password';
GRANT ALL PRIVILEGES ON *.* TO 'UserName'@'localhost';
FLUSH PRIVILEGES;

Note. The above will give the new user access to everything, which, depending on your circumstances, might not be recommended. See also: Create New Users and Control Privileges

Tell us what you think:

  1. How to configure phpMyAdmin with automatic login by setting auth_type to config.
  2. How to create new users in MySQL and control their permissions for better security.
  3. How to generate sitemaps dynamically using PHP.
  4. How to perform simple SELECT statements in SQL to communicate with SQL databases.
  5. The error happens when importing database backups using the SOURCE command, either because you got the path wrong, or because you used the command incorrectly.

More in: MySQL