You’ve landed here in frustration whilst developing your latest database-driven creation and want an answer to the question – how to fix “Access denied for user ‘root’@’localhost'”. Well, you’ve come to the right place.
As we’re sure you are aware, this error is triggered by your MySQL database and unfortunately, as is the case for any developer, errors are a common way of life. This error can be uber-frustrating due to the fact it’s likely brought down your ENTIRE app – after all, the error literally means that your application or website cannot connect successfully to your database.
Let’s fix it.
Check your creds!
Firstly, before we move on – and we know it’s the standard level one tech support answer (but it’s this because it’s so often right!), check your credentials – i.e. your username and password. Now, you’ve either forgotten your password (you should be using a good password manager by this point!) or you’ve never set one and aren’t sure.
If this is a production environment, check with your dev lead. If that’s you, berate yourself for forgetting.
If it’s a development environment and you’re thinking “but I’ve never set a ***** password” then fortunately, we are likely to know it ;). If you’re using AMPPS or similar software for local development then there’s every chance that your username and password are:
Username – root
Password – password
Go check it out – and hopefully that’s the end of your nightmare with this error!
For a bit of background, all MySQL databases come with a root password. This can be used to access any database or table on your setup. NOTE: this is NOT recommended for use in a production environment – set up a user specifically for the database/table that your application needs to access.
If you are in a local development and you have changed the root users password – that’s the one that you’ll need to use. If you can’t remember it, we’re going to tell you how to reset it further on so keep reading…
MySQL Privileges
If it’s not the password (90% of the time it is!) then next stop is to check your MySQL privileges. Just like a child, a MySQL privilege provides permission to each MySQL user (including root users) to do something. Including logging in.
The error message Access denied for user ‘root’@’localhost’ can appear if your MySQL user account is trying to do something (i.e. have sufficient privileges) to access the database. This is a curious one with the root user – as by default it has privileges to do anything – but if you’ve been tinkering around in your setup it IS possible that you’ve modified the privileges to lock yourself out.
You can easily check the privileges of any user in the MySQL console. Run the following command on your MySQL server to check the root user privileges.
SHOW GRANTS FOR 'root'@'localhost';
See ‘grants’ as like the keys to the city that your user possesses. This will show you all privileges associated with the root user account. Want to check out a different account? Just replace root with the name of your user. For example, if our user was called ‘test’ we would use the following command:
SHOW GRANTS FOR 'test'@'localhost';Granting Privileges
So, you’ve worked out you don’t have the privileges you thought you had? Or you’re on a development environment and your really not sure? Well, we can fix that using a super-special command:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password';In the above code choose a password and place it where it says ‘password’. This should be a secure, auto-generated, long and complex password UNLESS you’re on a development environment – in which case it’s fine to use ‘password’ if security is not a concern and you value your sanity which is slowly ebbing away whilst trying to fix this error!
The other side effect that this command will have is it will grant ALL privileges to the root account – which is really the way it should be and the way it was configured by default. BEWARE: the root account should be locked down if this is a Production environment!
Check your MySQL Config
OK, so if it’s not the password nor the privileges it’s time for ever developers nightmare – diving into the MySQL config.
Open up the MySQL config file in your favourite text editor. Sometimes the pesky “Access denied for user ‘root’@’localhost'” can be caused because your MySQL config is totally messed up.
Did you make changes recently? You took a backup of course so simply replace the current file with your backup when it last worked? Out of luck? Well, your options are to download a default MySQL config file or try to fix the swamp of a mess you MAY have made whilst trying to tinker with it.
Either way, we need to check that the MySQL configuration file has the correct settings.
“Where is the MySQL configuration file?” I hear you ask… In the blind panic since the error appeared you’ve totally forgotten where it is. Fear not and look under the MySQL directions (/etc/my.cnf or /etc/mysql/) and look for the file called my.cnf.
Once open, we’re going to get down to business and search for this line of config:
[mysqld]
skip-grant-tablesNot there? Add it in.
WARNING!!!!! This config option will SKIP the password check – so if it’s in production, it is not recommended. At least lock down your website using protected folders or similar otherwise ANYONE will be able to access your MySQL database without a password.
The line above skips the password check which allows you access and therefore enables you to change the root users password to something more memorable.
Once you’ve done this – don’t forget to actually delete the lines again:
[mysqld]
skip-grant-tablesWrite yourself a reminder on a post it so that you don’t forget (please!)
Reset the MySQL Root Password
If you’ve got to this point, you’re now desperate to fix the error. So, as in all times of desperation, let’s just start hacking away at MySQL.
All has failed, all is hopeless UNLESS we reset the root users password. to do this, you’re going to need to kill (stop) the MySQL server. Then you can either make the config change above OR start the server with the operator –skip-grant-tables option which will have the same effect.
Now from the command line you’ll be able to login to MySQL without a password. Again, please DON’T do this on a production environment! Once you’ve ‘logged in’, run this command and you can update the root users password to something better:
mysql> USE mysql;
mysql> UPDATE user SET authentication_string=PASSWORD('new_password') WHERE User='root';
mysql> FLUSH PRIVILEGES;Replace ‘new_password’ in the PASSWORD() function with something awesome and hard to guess. In development environments that are otherwise locked down you can of course use the favourite ‘password’. The above code essentially manually updates the user table and updates password (with appropriate hashing/salting) to the thing that you specify.
Final Thoughts
If you’re Access denied for user ‘root’@’localhost’ error hasn’t been solved by now, you’ve missed something or not done it correct. Following the above steps is a fool-proof way to fix the access denied error no matter kind of craziness is going on in your setup.
If all else fails, you can always re-install your MySQL server and build it again from the ground up – but going back through and following the instructions carefully is likely to take a lot less time and cause a lot less pain!
Let us know if the above steps have helped (or if you’ve spotted a typo) and feel free to ask us any questions below.