MySQL can t access root account

Question

I m running MySQL 5 x on my local Windows box and  using MySQL administrator  I can t connect to the databases I created using the root account  The error I get is      MySQL Error number 1045 Access denied   for user  root   localhost   using   password  YES    I can t recall changing root account credentials  but I know I tried to give other computers on the LAN access to the db by adding their IPs  One thing I did for one of the IPs was to specify access to the account  root  instead of root  i e  I surrounded root with single quotation chars  All using MySQL administrator  Could this be the reason why i can t login using root   Also  is there a way to create a new or reset the root account  As previously mentioned  I have full access to my box   See these questions   How to change MySQL root password to default  How do I retrieve my MySQL username and password  How do I change the password of the root user in MySQL

User · Answer

There is a section in the MySQL manual on how to reset the root password which will solve your problem

User · Answer

This worked for me   https   blog dotkam com 2007 04 10 mysql-reset-lost-root-password   Step 1  Stop MySQL daemon if it is currently running    ps -ef   grep mysql      - checks if mysql mysqld is one of the running processes     pkill mysqld             - kills the daemon  if it is running    Step 2   Run MySQL safe daemon with skipping grant tables          mysqld safe --skip-grant-tables  amp     mysql -u root mysql   Step 3  Login to MySQL as root with no password    mysql -u root mysql   Step 4  Run UPDATE query to reset the root password    UPDATE user SET password PASSWORD  value 42   WHERE user  root     FLUSH PRIVILEGES    In MySQL 5 7  the  password  field was removed  now the field name is  authentication string      UPDATE user SET authentication string PASSWORD  42   WHERE    user  root     FLUSH PRIVILEGES    Step 5  Stop MySQL safe daemon  Step 6  Start MySQL daemon

User · Answer

I got the same problem when accessing mysql with root  The problem I found is that some database files does not have permission by the mysql user  which is the user that started the mysql server daemon    We can check this with ls -l  var lib mysql command  if the mysql user does not have permission of reading or writing on some files or directories  that might cause problem  We can change the owner or mode of those files or directories with chown chmod commands    After these changes  restart the mysqld daemon and login with root with command    mysql -u root   Then change passwords or create other users for logging into mysql    HTH

User · Answer

You can use the init files  Check the MySQL official documentation on How to Reset the Root Password  including comments for alternative solutions    So basically using init files  you can add any SQL queries that you need for fixing your access  such as GRAND  CREATE  FLUSH PRIVILEGES  etc   into init file  any file    Here is my example of recovering root account   echo  CREATE USER  root   localhost  IDENTIFIED BY  root     gt  your init file sql echo  GRANT ALL PRIVILEGES ON     TO  root   localhost  WITH GRANT OPTION    gt  gt  your init file sql  echo  FLUSH PRIVILEGES    gt  gt  your init file sql   and after you ve created your file  you can run   killall mysqld mysqld safe --init-file  PWD your init file sql   then to check if this worked  press Ctrl Z and type  bg to run the process from the foreground into the background  then verify your access by   mysql -u root -proot mysql gt  show grants   -------------------------------------------------------------------------------------------------------------    Grants for root localhost                                                                                      -------------------------------------------------------------------------------------------------------------    GRANT USAGE ON     TO  root   localhost  IDENTIFIED BY PASSWORD   81F5E21E35407D884A6CD4A731AEBFB6AF209E1B      See also    No Password     No Problem at Everything MySQL Bug  28331  Unclear error message when CREATE USER fails due to duplicate key

[mysql] MySQL: can't access root account

Examples related to mysql

Examples related to mysql-error-1045