MySQL Error Access denied for user root localhost

Question

mysqladmin -u root -p  redacted  Enter password       mysqladmin  connect to server at  localhost  failed error     Access denied for user  root   localhost   using password  YES     How can I fix this

User · Answer

For new linux users this could be a daunting task. Let me update this with mysql 8(the latest version available right now is 8.0.12 as on 12-Sep-2018)

Open "mysqld.cnf" configuration file at "/etc/mysql/mysql.conf.d/".
Add skip-grant-tables to the next line of [mysql] text and save.
Restart mysql service as "sudo service mysql restart". Now your mysql is free of any authentication.
Connect to mysql client(also known as mysql-shell) as mysql -u root -p. There is no password to be keyed in as of now.
run sql command flush privileges;
Reset the password now as ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassword';
Now let's get back to the normal state; remove that line "skip-grant-tables" from "mysqld.cnf" and restart service.

That's it.

User · Answer

All solutions I found were much more complex than necessary and none worked for me  Here is the solution that solved my problem  No need to restart mysqld or start it with special privileges   sudo mysql  ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  root     With a single query we are changing the auth plugin to mysql native password and setting the root password to root  feel free to change it in the query   Now you should be able to login with root  More information can be found in mysql documentation   exit mysql console with Ctrl   D or by typing exit

User · Answer

Although a old one  I was getting the same error while setting up the mysql-8 zip version  Finally  switched to installer version which worked seamlessly  During installation  there is a prompt to setup the root password  Once set  it works for sure

User · Answer

If you are like me and all the above suggestions failed  proceed to uninstall all versions of mysql on your machine  search for all remaining mysql files using this command sudo find   -name  mysql  and rm -rf every file or directory with the mysql name attached to it  you should skip files related to programming language libraries   Now install a fresh version of MySQL and enjoy  NB  You will loose all your data so weigh your options first

User · Answer

It can happen if you don t have enough privileges  Type su  enter root password and try again

User · Answer

I tried many steps to get this issue corrected  There are so many sources for possible solutions to this issue that is is hard to filter out the sense from the nonsense  I finally found a good solution here   Step 1  Identify the Database Version    mysql --version   You ll see some output like this with MySQL     mysql  Ver 14 14 Distrib 5 7 16  for Linux  x86 64  using  EditLine wrapper   Or output like this for MariaDB   mysql  Ver 15 1 Distrib 5 5 52-MariaDB  for Linux  x86 64  using readline 5 1   Make note of which database and which version you re running  as you ll use them later  Next  you need to stop the database so you can access it manually   Step 2  Stopping the Database Server  To change the root password  you have to shut down the database server beforehand   You can do that for MySQL with     sudo systemctl stop mysql   And for MariaDB with     sudo systemctl stop mariadb   Step 3  Restarting the Database Server Without Permission Checking  If you run MySQL and MariaDB without loading information about user privileges  it will allow you to access the database command line with root privileges without providing a password  This will allow you to gain access to the database without knowing it   To do this  you need to stop the database from loading the grant tables  which store user privilege information  Because this is a bit of a security risk  you should also skip networking as well to prevent other clients from connecting   Start the database without loading the grant tables or enabling networking     sudo mysqld safe --skip-grant-tables --skip-networking  amp    The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal   Now you can connect to the database as the root user  which should not ask for a password     mysql -u root   You ll immediately see a database shell prompt instead   MySQL Prompt  Type  help   or   h  for help  Type   c  to clear the current input statement   mysql gt    MariaDB Prompt  Type  help   or   h  for help  Type   c  to clear the current input statement   MariaDB   none   gt    Now that you have root access  you can change the root password   Step 4  Changing the Root Password  mysql gt  FLUSH PRIVILEGES    Now we can actually change the root password   For MySQL 5 7 6 and newer as well as MariaDB 10 1 20 and newer  use the following command   mysql gt  ALTER USER  root   localhost  IDENTIFIED BY  new password     For MySQL 5 7 5 and older as well as MariaDB 10 1 20 and older  use   mysql gt  SET PASSWORD FOR  root   localhost    PASSWORD  new password      Make sure to replace new password with your new password of choice   Note  If the ALTER USER command doesn t work  it s usually indicative of a bigger problem  However  you can try UPDATE     SET to reset the root password instead    IMPORTANT  This is the specific line that fixed my particular issue   mysql gt  UPDATE mysql user SET authentication string   PASSWORD  new password   WHERE User    root  AND Host    localhost     Remember to reload the grant tables after this   In either case  you should see confirmation that the command has been successfully executed   Query OK  0 rows affected  0 00 sec    The password has been changed  so you can now stop the manual instance of the database server and restart it as it was before   Step 5  Restart the Database Server Normally  The tutorial goes into some further steps to restart the database  but the only piece I used was this   For MySQL  use        sudo systemctl start mysql  For MariaDB  use     sudo systemctl start mariadb   Now you can confirm that the new password has been applied correctly by running     mysql -u root -p   The command should now prompt for the newly assigned password  Enter it  and you should gain access to the database prompt as expected   Conclusion  You now have administrative access to the MySQL or MariaDB server restored  Make sure the new root password you choose is strong and secure and keep it in safe place

User · Answer

None of the above answers helped me with this problem  so here s the solution I found   The relevant part      In Ubuntu systems running MySQL 5 7  and later versions   the root MySQL user is set to authenticate using the auth socket plugin by default rather than with a password  This allows for some greater security and usability in many cases  but it can also complicate things when you need to allow an external program  e g   phpMyAdmin  to access the user       In order to use a password to connect to MySQL as root  you will need to switch its authentication method from auth socket to mysql native password  To do this  open up the MySQL prompt from your terminal            sudo mysql         Next  check which authentication method each of your MySQL user accounts use with the following command            SELECT user authentication string plugin host FROM mysql user          Output        ------------------ ------------------------------------------- ----------------------- -----------    user               authentication string                       plugin                  host         ------------------ ------------------------------------------- ----------------------- -----------    root                                                           auth socket             localhost     mysql session       THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE   mysql native password   localhost     mysql sys           THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE   mysql native password   localhost     debian-sys-maint    CC744277A401A7D25BE1CA89AFF17BF607F876FF   mysql native password   localhost    ------------------ ------------------------------------------- ----------------------- -----------  4 rows in set  0 00 sec           In this example  you can see that the root user does in fact authenticate using the auth socket plugin  To configure the root account to authenticate with a password  run the following ALTER USER command  Be sure to change password to a strong password of your choosing  and note that this command will change the root password you set in Step 2            ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  password           Then  run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect            FLUSH PRIVILEGES          Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth socket plugin            SELECT user authentication string plugin host FROM mysql user          Output        ------------------ ------------------------------------------- ----------------------- -----------    user               authentication string                       plugin                  host         ------------------ ------------------------------------------- ----------------------- -----------    root                3636DACC8616D997782ADD0839F92C1571D6D78F   mysql native password   localhost     mysql session       THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE   mysql native password   localhost     mysql sys           THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE   mysql native password   localhost     debian-sys-maint    CC744277A401A7D25BE1CA89AFF17BF607F876FF   mysql native password   localhost    ------------------ ------------------------------------------- ----------------------- -----------  4 rows in set  0 00 sec           You can see in this example output that the root MySQL user now authenticates using a password  Once you confirm this on your own server  you can exit the MySQL shell            exit

User · Answer

In my case under Debian 10  the error  ERROR 1698  28000   Access denied for user  root   localhost    was solved by  GOOD WAY   sudo mysql -u root -p mysql   BAD WAY   mysql -u root -p mysql

User · Answer

Open  amp  Edit  etc my cnf or  etc mysql my cnf  depending on your distro  Add skip-grant-tables under  mysqld  Restart Mysql You should be able to login to mysql now using the below command mysql -u root -p Run mysql gt  flush privileges  Set new password by ALTER USER  root   localhost  IDENTIFIED BY  NewPassword   Go back to  etc my cnf and remove comment skip-grant-tables Restart Mysql Now you will be able to login with the new password mysql -u root -p

User · Answer

Fix for Mac OS  Install MySQL from https   downloads mysql com archives community   8 x is latest as on date but ensure that the version is compatible with the Mac OS version   Give password for root  let  lt root-password gt  be the password  during installation  Don t forget to remember the password    Select Use Legacy Password Encryption option  that is what I had used and did not try for Use Strong Password Encryption option   Search and open MySQL prefPane  use search tool   Select Configuration tab Click Select option of Configuration File  Select  private etc my cnf     From terminal open a new or existing file with name  etc my cnf  vi  etc my cnf  add the following content    mysqld   skip-grant-tables   Restart mysqld as follows   ps aux   grep mysql kill -9  lt pid1 gt   lt pid2 gt       grab pids of all mysql related processes    mysqld gets restarted automatically  Verify that the option is set by running the following from terminal   ps aux   grep mysql    gt  mysql bin mysqld     --defaults-file  private etc my cnf      output    Run the following command to connect  let mysql- lt version gt -macos lt version gt -x86 64 be the folder where mysql is installed  To grab the actual folder  run ls  usr local  and copy the folder name     usr local mysql- lt version gt -macos lt version gt -x86 64 bin mysql -uroot -p lt root-password gt

User · Answer

Solution  Give up   Hear me out  I spent about two whole days trying to make MySQL work to no avail  always stuck with permission errors  none of which were fixed by the answers in this thread  It got to the point that I thought if I continued I d go insane   Out of patience for making it work  I sent the command to install SQLite  only using 450KB  and it worked perfectly right from the word go   If you don t have the patience of a saint  go with SQLite and save yourself a lot of time  effort  pain  and storage space

User · Answer

In my experience  if you run without sudo it will not work  So make sure your command is  sudo mysql -uroot -p

User · Answer

For Ubuntu Debian users  may work on other distros especially debian based ones  Run the following to connect as root  without any password  sudo  usr bin mysql --defaults-file  etc mysql debian cnf  If you don t want to add --defaults-file each time you want to connect as root  you can copy  etc mysql debian cnf into your home directory  sudo cp  etc mysql debian cnf    my cnf  and then  sudo mysql

User · Answer

In your MySQL workbench  you can go to the left sidebar  under Management select  Users and Privileges   click root under User Accounts  the in the right section click tab  Account Limits  to increase the max queries  updates  etc  and then click tab  Administrative Roles  and check the boxes to give the account access  Hope that helps

User · Answer

I did this to set my root password in initial set up of MySQL in OSx  Open a terminal    sudo sh -c  echo  usr local mysql bin  gt   etc paths d mysql    Close the terminal and open a new terminal  And followings are worked in Linux  to set root password    sudo  usr local mysql support-files mysql server stop sudo mysqld safe --skip-grant-tables    sudo mysqld safe --skip-grant-tables   This did not work for me in first time  But second try  out was success    Then login to MySQL  mysql -u root  FLUSH PRIVILEGES    Now change the password   ALTER USER  root   localhost  IDENTIFIED BY  newpassword     Restart MySQL   sudo  usr local mysql support-files mysql server stop sudo  usr local mysql support-files mysql server start

User · Answer

Okay  I know this is an old thread but if you reached this page via Google like I did and none of the above solutions worked  what turned out to be the error was 100  foolishness on my end  I didn t connect to the server  Once connected everything was smooth sailing   In case it helps to know my setup  I m using Sequel Pro and trying to connect to it with Node using the NPM package  mysql  I didn t think I needed to actually connect  other than run Sequel Pro  because I was doing that from my app already

User · Answer

After trying all others answers  this it what finally worked for me   sudo mysql -- It does not ask me for any password  -- Then in MariaDB MySQL console  update mysql user set plugin    mysql native password  where User  root   FLUSH PRIVILEGES  exit    I found the answer in this post   https   medium com  chiragpatel 52497 solved-error-access-denied-for-user-root-localhost-of-mysql-programming-school-6e3611838d06

User · Answer

Ugh- nothing worked for me  I have a CentOS 7 4 machine running mariadb 5 5 64    What I had to do was this  right after installation of mariadb from yum     systemctl restart mariadb   mysql secure installation   The mysql secure installation will take you through a number of steps  including  Set root password   Y n    Just say  y  and give it a password  Answer the other questions as you wish   Then you can get in with your password  using    mysql -u root -p   It will survive    systemctl restart mariadb   The Key  Then  I checked the  bin mysql secure installation source code to find out how it was magically able to change the root password and none of the other answers here could  The import bit is   do query  UPDATE mysql user SET Password PASSWORD   esc pass   WHERE User  root         It says SET Password     and not SET authentication string   PASSWORD     So  the proper procedure for this version  5 5 64  is   login using mysql -u root -p   using the password you already set  Or  stop the database and start it with  mysql safe --skip-grant-tables --skip-networking  amp    From the mysql  prompt   use mysql  select host user password from user where user    root    observe your existing passwords for root   UPDATE mysql user set Password   PASSWORD  your new cleartext password   where user    root  AND host    localhost   select host user password from user where user    root   flush privileges  quit    kill the running mysqld safe  restart mariadb  Login as root  mysql -u -p  Use your new password   If you want  you can set all the root passwords at once  I think this is wise   mysql -u root -p  login  use mysql  select host user password from user where user    root   UPDATE mysql user set Password   PASSWORD  your new cleartext password   where user    root   select host user password from user where user    root   flush privileges  quit    This will perform updates on all the root passwords  ie  for  localhost    127 0 0 1   and    1   In the future  when I go to RHEL8 or what have you  I will try to remember to check the  bin mysql secure installation and see how the guys did it  who were the ones that configured mariadb for this OS

[mysql] MySQL Error: : 'Access denied for user 'root'@'localhost'

Examples related to mysql

Examples related to sql

Examples related to database

Examples related to database-connection